A problem with a query with related tables

[Evdokimos]

Hi all
I have a difficult problem and I will use a simplified example to explain you. I have 3 tables. TABLE1 contains the name of the sales person (SALESMANNAME), the date (WORKINGDATE) and the Hours worked (HOURSWORKED). 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 the particular salesman at at particular date (SALESVALUE) and has also the two key fields of TABLE1 ie SALESMANNAME and WORKINGDATE. However SALESVALUE can be found in more than once (for a particular name at a particular date). Similarly TABLE3 contains the receipts of each sales man (RECEIPSVALUE) which also can be found in more than once and two fields of table 1 (WORKINGDATE and WORKINGDATE). I want to create a query to show for a particular salesman at a particular date the hours worked, total sales and total receipts. I used the query found in the file attached but it does not produce correct result. If for example I have two records of SALESVALUE (in a particular date for a particular name) and one record RECEIPSVALUE, the RECEIPSVALUE is double the correct amount.
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. Also the structure of tables cannot be changed.
I thank you in advanced for your kind help

Hi all append.doc

Evdokimos

[recyan]

Till someone comes along,
Am not familiar with excel macros, all the same :
Is it not possible to design a query with sub-queries in your Access db.
Then, in the excel, Under the "Data" menu, selecting "From Access", etc.
Does this not work ?

Thanks

[Evdokimos]

Thanks a lot for your answer.

However this cannot work for my case.

[recyan]

However this cannot work for my case.

Though you have not mentioned, why it does not work, I am assuming, it is perhaps because of the parameter in the Query.
If that is the case, just check out if below gives some guidelines (Somewhere near the middle - Using Parameters In External ODBC Data Queries) :

http://www.nickhodge.co.uk/pre2007/g...taexamples.htm

Thanks