Joining two disctinct count queries in MS ACCESS

Question

I am struggling with merging the two following queries in MS Access. The first query basically counts the number of stores a product (pcode) was available in each week. The second query counts the number of stores each week.

What I want to accomplish is to have one table in the end showing WEEK_ID, PCODE, store_number_per_pcode_per_week, store_number_per_week. Could you please give me some hints?

1. QUERY

SELECT cd.WEEK_ID, cd.PCODE, count(cd.STORE_ID) as store_number_per_pcode_per_week
FROM (SELECT DISTINCT STORE_ID, PCODE, WEEK_ID
      FROM SALES
     ) as cd
GROUP BY cd.WEEK_ID, cd.PCODE;

2. QUERY

SELECT df.WEEK_ID, count(df.STORE_ID) as store_number_per_week
FROM (SELECT DISTINCT STORE_ID, WEEK_ID
      FROM SALES
     ) as df
GROUP BY df.WEEK_ID;

Answer 1

You can use a JOIN:

SELECT wp.WEEK_ID, wp.PCODE, wp.store_number_per_pcode_per_week,
       w.store_number_per_week
FROM (SELECT cd.WEEK_ID, cd.PCODE,
             COUNT(cd.STORE_ID) as store_number_per_pcode_per_week,
      FROM (SELECT DISTINCT STORE_ID, PCODE, WEEK_ID
            FROM SALES
           ) as cd
      GROUP BY cd.WEEK_ID, cd.PCODE
     ) wp INNER JOIN
     (SELECT cd.WEEK_ID, 
             COUNT(cd.STORE_ID) as store_number_per_week,
      FROM (SELECT DISTINCT STORE_ID, WEEK_ID
            FROM SALES
           ) as cd
      GROUP BY cd.WEEK_ID
     ) w
     ON wp.WEEK_ID = w.WEEK_ID;