count 1-many and group by the many side

[imx]

Hi I have a many-many relationship between a “promotions” table and a “trading periods” table. This relationship is resolved with a 3rd table called “tblPromo_TP”

The promotions table has 2 date fields that store the start date and end date of a promotion

The Trading periods table has fields for the Year (integer), Year half (integer from 1 or 2), TP (integer from 1-12), and dStart/dEnd dates (both date fields)

When I import records into the promotions table i run a function that creates records in “tblPromo_TP” if the promotion start and end dates overlap the TP start and end dates.

I need to run a query that counts promotions and groups the result by Year or Half year or trading period

When the promotions table is the only table in the query I get the correct count result but as soon as I include the “tblPromo_TP” and “trading periods” the count returned is higher than I expect (due to one promotion having one or more related records in “tblPromo_TP” depending on the length of the promotion)
I do have the freedom to change the table’s structure and fields and relationships If that is required

 

[jdraw]

I think you should show us the query SQL.

 

[imx]

Heres a small version of the db.

There are 2 records in the promotion table. The query called "qry_Count_Promos_incorrect" returns the number of days in total of both records instead of just counting the number of promotions.

I would like to use the tblPromotion_day table for grouping the results by Year,Year half, or tarading period