473,700 Members | 2,466 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Query mutiple tables with date input.

7 New Member
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 1831
wevans
7 New Member
Silly me. I just created a Union query and all my problems went away. lol
Feb 23 '08 #2
NeoPa
32,569 Recognized Expert Moderator MVP
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
2130
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 exactly do i place the code for creating the tables. Do I have to create a new php file? and how do i link the information from the form to the tables. Please go easy on me I am brand new to PHP and SQL. here is the code that i have so far for...
4
2207
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 the indexes on big table. The query joins 5 tables ( 1 has 30 million records 4 other tables have less than 100000) i see the cost at each step is less than 1000 so i guess it shouldn't take that long to return the result set??? it does a sort...
6
1903
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, and ProductionDate fields; - and tblDepartment table, from which ive used the Department field to simply link a department name to the corresponding DeptID. the criteria are made up of user prompts, which basically allow the user to input a dept...
0
7495
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 can make report from my database on the one sheet rather than querying each select statement ( I will use MS Query from the Excel) ========================================================= 1) select convert(varchar, create_date_time, 112) as Date,...
4
2041
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 with MS Access and VBA. I desperately need help with 2 queries that I am trying to put together. I want to thank anyone that can help me out with this situation. I want to put a select query(Query1) that uses one table and the criteria would...
4
1942
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 separate worksheets, all linked to Access. I have a query that performs some minor calculations, not to hard, and will be ued to drive a report. Here is my problem, I only have about 14 rows of data in each table as we only have three reps. ...
4
4586
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 reservation of available resources (laptops, beamers, etc). The MySql database queries are already in place, as is the ASP administration panel. The frontend that users will see however, still needs some work. I'm really close, but since I'm no...
1
4265
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 format-example: (10/22/08) Load Number -Number-Single (should not be higher than 35-40 per day) WorkOrder -Text- 50 (string of letters and numbers of varying length)-example-KR53103 2625000-48 Badge -text- (employee badge number)-example-c12345
2
5045
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 up this way and i'm able to insert text data into it with no problems. I've checked the database design its basiclly a duplicate. I did have to remove my not null on the long blob element. but other then that, i don't understand why this is...
0
8645
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9214
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9075
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
8973
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8925
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
4404
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4657
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3089
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
2028
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.