Access - SELECT JOIN many to many 2 tables with 3 dictionaries

Question

please help me with ACCESS SQL SELECT query.

Database scheme

I need to link Sales data into my Promo table. See the scheme above. I try somethink like this, but it doesn't work.

       SELECT P.Ext_item_id, P.Ext_shop_id, P.Ext_date_id, S.SALES

       FROM (((PromoTable AS P INNER JOIN SHOP AS SH ON P.Ext_shop_id = SH.Ext_shop_id)
                               INNER JOIN ITEM AS I ON P.Ext_item_id = I.Ext_item_id)
                               INNER JOIN DATE AS D ON P.Ext_date_id = D.Ext_date_id)

                          ?????INNER JOIN SALES AS S ON SH.shop_id=S.shop_id
                                                    AND I.item_id = S.item_id
                                                    AND D.date_id = D.date_id

The question is: how to link 2 tables using 3 dictionary tables beetween them (M:M relationship) Great thanks in advance!

Examples of data in tables

Tables view

Answer 1

Your initial approach seems correct..

  SELECT P.Ext_item_id, P.Ext_shop_id, P.Ext_date_id, S.SALES
  FROM PROMO P
  INNER JOIN SHOP SH ON P.Ext_shop_id = SH.Ext_shop_id
  INNER JOIN ITEM I ON P.Ext_item_id = I.Ext_item_id
  INNER JOIN [DATE] D ON P.Ext_date_id = D.Ext_date_id
  INNER JOIN SALES S 
    ON SH.shop_id=S.shop_id  AND I.item_id = S.item_id  AND D.date_id = S.date_id