By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,314 Members | 2,155 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,314 IT Pros & Developers. It's quick & easy.

Query mutiple tables with date input.

P: 7
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.

Expand|Select|Wrap|Line Numbers
  1. SELECT [Customer List].[Last Name], [Customer List].[First Name], First([Service Records].Date) AS [First Of Date], Sum([Service Records].Price) AS Price
  2. FROM [Customer List] INNER JOIN [Service Records] ON [Customer List].ID = [Service Records].ID
  3. GROUP BY [Customer List].[Last Name], [Customer List].[First Name], [Service Records].[Trans #]
  4. HAVING (((First([Service Records].Date))>=["Start Date" Use format MM/DD/YY] And (First([Service Records].Date))<["End Date" Use format: MM/DD/YY]))
  5. 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.

Expand|Select|Wrap|Line Numbers
  1. 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
  2. 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
  3. GROUP BY [Customer List].[Last Name], [Customer List].[First Name], [Service Records].[Trans #]
  4. HAVING (((First([Service Records].Date))>=["Start Date" Use format MM/DD/YY] And (First([Service Records].Date))<["End Date" Use format: MM/DD/YY]))
  5. 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.
Feb 22 '08 #1
Share this Question
Share on Google+
2 Replies


P: 7
Silly me. I just created a Union query and all my problems went away. lol
Feb 23 '08 #2

NeoPa
Expert Mod 15k+
P: 31,494
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.
Feb 26 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.