473,326 Members | 2,134 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Query mutiple tables with date input.

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
2 1807
wevans
7
Silly me. I just created a Union query and all my problems went away. lol
Feb 23 '08 #2
NeoPa
32,556 Expert Mod 16PB
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

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

Similar topics

2
by: MSM | last post by:
Hello, I am using php to create a form. I would like that forms information to go into a database. When creating tables where the information that i entered into the form will be stored, where...
4
by: Raj | last post by:
Hi all, I have couple of questions some one plzz help 1.I have a query which run for 2 hours on my production machine, it returns 1.5 millon rows, i looked at the explain plan it is picking up...
6
by: PsyClone | last post by:
Im fairly new to access and would prefer not to use any scripting as such, but Im trying to create aselect query based on two tables: tblProduction, from which the query uses DeptID, ProductionQty, ...
0
by: djflow | last post by:
Hi! II was wondering if you can help me with SQL query.. Below 7 separated select query works fine(only when they are retrieved separately) But I want to combined them together and so that i...
4
by: Doris | last post by:
It does not look like my message is posting....if this is a 2nd or 3rd message, please forgive me as I really don't know how this site works. I want to apologize ahead of time for being a novice...
4
by: pokerboy801 | last post by:
OK, I will try to explain this as clearly and as concise as possible. I am using Access, which has three MS Excel Linked tables, to store call center metrics for reps. My Excel workbook has three...
4
by: zion4ever | last post by:
Hello good people, Please bear with me as this is my first post and I am relative new to ASP. I do have VB6 experience. I have a form which enables users within our company to do an intranet...
1
by: Arli | last post by:
I have the following linked tables: tblMainPL is my main table that I need to pull the information in from. It has the following fields: Autonumber1 -PK set as autonumber Date - short date...
2
by: wizardry | last post by:
hello - i'm trying to insert a blob into my table, it will insert but the string that i insert when i query the inserted data returns null with 0 bytes in the column. I have other tables set...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.