gagnonconsulting
New member
- Local time
- Today, 03:45
- Joined
- Oct 24, 2008
- Messages
- 7
Hi,
I have a query that summarizes data across 3 tables (Store, Sales and Payroll), Payroll and Sales are related by date; Store and Sales have store_id in common. My query groups records by a given date range and attempts to calculate the sum of the TotalSales from the Sales table and the sum of the ManagerPayroll from the Payroll table. The Store table is included for the OpenDate column so the results can be further grouped by new stores and established stores (stores older than 2 years).
The query works except that the sum of the ManagerPayroll does not work (it comes up $0). I understand that this is due to the fact that I have a Where clause that checks Store.ID = 0. However, if I do not have the Store.ID = 0, I get way too many record combinations in the sum. I know both are wrong, and any advise would be greatly appreciated. I have attached a sample access mdb file. Test2 is the query in question.
I have a query that summarizes data across 3 tables (Store, Sales and Payroll), Payroll and Sales are related by date; Store and Sales have store_id in common. My query groups records by a given date range and attempts to calculate the sum of the TotalSales from the Sales table and the sum of the ManagerPayroll from the Payroll table. The Store table is included for the OpenDate column so the results can be further grouped by new stores and established stores (stores older than 2 years).
The query works except that the sum of the ManagerPayroll does not work (it comes up $0). I understand that this is due to the fact that I have a Where clause that checks Store.ID = 0. However, if I do not have the Store.ID = 0, I get way too many record combinations in the sum. I know both are wrong, and any advise would be greatly appreciated. I have attached a sample access mdb file. Test2 is the query in question.
Last edited:
