Unanswered: A problem with a query with related tables
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. 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 collections of each sales man (COLLECTIONSVALUE) which also can be found in more than once and the two key fields of table 1 (SALESMANNAME 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 collections. 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 COLLECTIONSVALUE, the COLLECTIONSVALUE 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