sql - Join cross tab and select queries -

i have table query. query should include 2 sub queries, therefore think join operation needed not sure. table looks this:

region      product status     date region 1          inventory   7/20/2016 region 2          development 7/1/2016 region 2    b       inventory   7/4/2016 region 2    b       inventory   7/9/2016 region 3    b       development 7/8/2016 region 3    c       inventory   7/15/2016 region 1    c       development 7/10/2016 

i perform 1 query, first cross tabulate based on product, query (on 2 columns, status = inventory , date > 7/9/2016), , join results results of first query. in end following result:

region        b   c   special count region 1    1   0   1   1 region 2    1   2   0   0 region 3    0   1   1   1 

what code purpose? thank you

note: using access , not sure how standard access sql compared other sql based software.

you can conditional aggregation:

select region,        sum(iif(product = "a", 1, 0)) a,        sum(iif(product = "b", 1, 0)) b,        sum(iif(product = "c", 1, 0)) c,        sum(iif(status = "inventory" , date > #7/9/2016#, 1, 0)) special t group region;