Query mutiple tables with date input.

[wevans]

I have 3 tables, Customer, Offsite Service and service. I need to create a sales report/query based on the results of the user input, which is a date range. I've got it working great with two of the tables, but not the third. Here's the code for the working one I have now.

SELECT [Customer List].[Last Name], [Customer List].[First Name], First([Service Records].Date) AS [First Of Date], Sum([Service Records].Price) AS Price
FROM [Customer List] INNER JOIN [Service Records] ON [Customer List].ID = [Service Records].ID
GROUP BY [Customer List].[Last Name], [Customer List].[First Name], [Service Records].[Trans #]
HAVING (((First([Service Records].Date))>=["Start Date" Use format MM/DD/YY] And (First([Service Records].Date))<["End Date" Use format: MM/DD/YY]))
ORDER BY [Customer List].[Last Name];

Now if I try to add the third table, offsite service, it only returns one result. Here's that code.

SELECT [Customer List].[Last Name], [Customer List].[First Name], First([Service Records].Date) AS [First Of Date], Sum([Service Records].Price) AS Price, Sum([Offsite Service Records].Price) AS SumOfPrice
FROM ([Customer List] INNER JOIN [Service Records] ON [Customer List].ID = [Service Records].ID) INNER JOIN [Offsite Service Records] ON [Customer List].ID = [Offsite Service Records].ID
GROUP BY [Customer List].[Last Name], [Customer List].[First Name], [Service Records].[Trans #]
HAVING (((First([Service Records].Date))>=["Start Date" Use format MM/DD/YY] And (First([Service Records].Date))<["End Date" Use format: MM/DD/YY]))
ORDER BY [Customer List].[Last Name];

I want to be able to pull records from a date range that returns customer name, the date and price from service AND the price from offsite service. This way I have the price from both tables that I can add up and get a total sales report.

Any help would be greatly appreciated.

[wevans]

Silly me. I just created a Union query and all my problems went away. lol

[NeoPa]

Thanks for posting the solution :)
I would warn to be careful of UNION queries though, particularly for performance and efficiency reasons. If it can be done by Normalisation (Normalisation and Table structures) then that's generally a better approach.