Subtotal Query / Parameter Query

[yleeSF]

Create a Query where the parameter applies to two differnt tables with the same Field Name. For example,

Table 1
Store
Sales
Date

Table 2
Store
Sales Plan
Date

I would like to create a subtotal query based on the two tables above. I would like the Parameter Between [Start date] And [End Date] under the WHERE to apply to both tables. Is this possible?

Any help you can provide would be much appreciated.
YL

 

[SonOfEmidec1100]

Not 100% sure on what to want to achieve but this might be close enough to give you the idea.

SELECT Table1.store, SUM(Table1.sales) AS SalesTotal
FROM Table1 INNER JOIN
Table2 ON Table1.store = Table2.store
WHERE (Table1.[date] BETWEEN startdate AND enddate) AND (Table2.[date] BETWEEN startdate AND enddate)
GROUP BY Table1.store

Hope it helps.

 

[yleeSF]

Thanks for your response! It doesn't return the figures correctly - Triples/ Quadruples the sales data. Do you have any other suggestions?

Basically, I need to create a Report based on this Query. The user enters the Date Range and a report generates based on this Date Range. The same Date Range needs to apply to all Tables with a Date Field. Same Date Range for Sales & Sales Plan & etc...

 

[BHScripter]

Try putting "distinct" in front of the Table1.store in your select statement