A problem with a query with related tables

[Evdokimos]

Hi all
I have a difficult problem and I use a simplified example to explain you. I have 3 tables. TABLE1 contains the name of the sales person, the date and the Hours worked (of the sales person at the particular date). So a combination of name and date is unique eg George and 15/6/2012 cannot be found again in another record of the table. In TABLE2 contains the sales of SALESPRODUCTA from the particular person at the particular date. However SALESPRODUCTA can be found in more than once (for a particular name at a particular date). Similarly TABLE3 contains the sales of SALESPRODUCTB (from the particular person at the particular date) and also can be found in more than once. I want to create a query to show for each Name at a particular date the hours worked, total sales of product A and total sales of product B. I used the following query but it does not produce correct result. If for example I have two records of sales of product A (in a particular date for a particular name) and one record of sales of product B, the sales of product B is double the correct amount.
SELECT TABLE1.NAME, TABLE1.DATE, TABLE1.HOURS, Sum(TABLE2.SALESPRODUCTA) AS SumOfSALESPRODUCTA, Sum(TABLE3.SALESPRODUCTB) AS SumOfSALESPRODUCTB
FROM (TABLE1 LEFT JOIN TABLE2 ON (TABLE1.NAME = TABLE2.NAME) AND (TABLE1.DATE = TABLE2.DATE)) LEFT JOIN TABLE3 ON (TABLE1.DATE = TABLE3.DATE) AND (TABLE1.NAME = TABLE3.NAME)
GROUP BY TABLE1.NAME, TABLE1.DATE, TABLE1.HOURS
HAVING (((TABLE1.NAME)="GEORGE"));
Please note that I want to avoid a solution of using subqueries or changing the relationship of the tables (eg one to many) since I want the solution to be applied to the specific query. This is because I want to derive a query statement to be used in an Excel macro.
I thank you in advanced for your kind help

Evdokimos

 

[plog]

I have a difficult problem and I use a simplified example to explain you....Please note that I want to avoid a solution of using subqueries or changing the relationship of the tables

I hope you've oversimplified the example, because based on your description of your issue I can only recommend restructuring your tables properly. Based on what you've stated, you need to consolodate the data in Table2 and Table3 into one table.


First, 'Date' and 'Name' are bad object names because they are reserved words in Access. Second, and foremost, the name of a table should never contain data itself. Instead of putting Product information in the table's name, you should put that data in a field and only use one table. This is what that table's structure should be:


SalesDate, SalesName, Product, Quantity



You do that and this problem becomes trivial to solve.

 

[Evdokimos]

Thank you for your answer. However, the example above was simplified. My actual data has diffent field names and for some reason I cannot change the tables structure. The actual fields are in table 1: period date, ID number and tax return value, table 2 is period date, ID number and value of refunds claimed, and table 3 is period date, ID number and unallocated amount of tax payers.

Thanks anyway

Evdokimos