Query to pull relevant data from 2 tables

[murphy123]

Hello,
I am trying to build a query to pull a total of completed "truck roll saved" coming from 2 different tables on a daily basis. However, I keep getting the incorrect data. I am not a pro with SQL. The common fields in the tables are the User, Date, and a check box for Saved Truck Roll. I want to count the amount of truck rolls that were saved in the 2 tables then multiply it by $49.95. I make both queries work fine separately, but when I try to combine them, they don't work. I tried to match the date, user, and saved truck roll field in both queries and it didn't work. All I am trying to do is to get both table totals combined in one report. I can't get it to work. Can anyone help me out?


This is what I have so far:

SELECT [QRY SAVED TRUCK ROLL MISM].[Date Completed], [QRY SAVED TRUCK ROLL MISM].Lead, [QRY SAVED TRUCK ROLL MISM].[CountOfSaved Truck Roll], [QRY SAVED TRUCK ROLL DUP OUT].[CountOfSaved Truck Roll]
FROM [QRY SAVED TRUCK ROLL MISM], [QRY SAVED TRUCK ROLL DUP OUT]
WHERE ((([QRY SAVED TRUCK ROLL MISM].[Date Completed]) Between [ENTER START DATE] And [ENTER END DATE]))
ORDER BY [QRY SAVED TRUCK ROLL MISM].[Date Completed];

 

[AlexCuse]

I think you are looking for a union query? NOt exactly sure though. Can you post sample data and desired result?

Ignorance of certain subjects is a great part of wisdom

 

[murphy123]

This is a result sample I have when asking to get data from 11/01-11/10/06.

This is what I am currently getting:

Date Completed User QRY SAVED TRUCK ROLL MISM.CountOfSaved Truck Roll QRY SAVED TRUCK ROLL DUP OUT.CountOfSaved Truck Roll
11/1/2006 Gleco, Kimberly 24 2
11/1/2006 Gleco, Kimberly 24 1
11/2/2006 Gleco, Kimberly 15 1
11/2/2006 Gleco, Kimberly 15 2
11/6/2006 Gleco, Kimberly 33 1
11/6/2006 Gleco, Kimberly 33 2
11/7/2006 Gleco, Kimberly 6 2
11/7/2006 Gleco, Kimberly 6 1
11/8/2006 Gleco, Kimberly 9 2
11/8/2006 Gleco, Kimberly 9 1
11/9/2006 Gleco, Kimberly 27 1
11/9/2006 Gleco, Kimberly 27 2
11/10/2006 Gleco, Kimberly 18 1
11/10/2006 Gleco, Kimberly 18 2

In fact the final result should be:

11/1/06 Gleco, Kimberly 24 0
11/2/06 Gleco, Kimberly 15 0

 

[lespaul]

can you give some raw data from the two tables and the expected results of the table data?


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[murphy123]

These tables have data that need to be corrected and a couple of users will get credit for correcting the data. I am not adding all the fields in my table to this sample. Just the fields that I need in my queries.

The main source of both tables is different, but the user piece of it has the same purpose. In this case, save truck rolls. I am looking for the "True". The fields that I am trying to add to the queries are:


(Table 1)TBL SVC CODE NO EQUIP TO MATCH OUTL
Lead Date Completed Saved Truck Roll
Gleco, Kimberly 09-Aug-06 FALSE
Gleco, Kimberly 09-Aug-06 FALSE
Gleco, Kimberly 06-Nov-06 FALSE
Gleco, Kimberly 25-Oct-06 FALSE
Gleco, Kimberly 17-Nov-06 FALSE
Gleco, Kimberly 06-Nov-06 TRUE
Gleco, Kimberly 24-Nov-06 TRUE
Gleco, Kimberly 24-Oct-06 TRUE
Gleco, Kimberly 15-Nov-06 TRUE
Gleco, Kimberly 13-Nov-06 TRUE
Gleco, Kimberly 29-Nov-06 TRUE
Gleco, Kimberly 06-Nov-06 TRUE
Gleco, Kimberly 23-Oct-06 TRUE
Gleco, Kimberly 30-Oct-06 TRUE
Gleco, Kimberly 26-Oct-06 TRUE
Gleco, Kimberly 26-Oct-06 TRUE
Gleco, Kimberly 30-Nov-06 TRUE
Gleco, Kimberly 06-Nov-06 TRUE
Gleco, Kimberly 13-Nov-06 TRUE
Gleco, Kimberly 26-Oct-06 TRUE
Gleco, Kimberly 23-Oct-06 TRUE
Gleco, Kimberly 30-Oct-06 TRUE
Gleco, Kimberly 24-Oct-06 TRUE
Gleco, Kimberly 23-Oct-06 TRUE

(Table 2)TBL EQUIP CODES ON SAME OUTLET

Lead Date Completed Saved Truck Roll
Hilstolsky 02-Oct-06 FALSE
Gleco, Kimberly 23-Oct-06 FALSE
Hilstolsky 29-Nov-06 FALSE
Gleco, Kimberly 15-Aug-06 FALSE
Gleco, Kimberly 09-Aug-06 FALSE
Gleco, Kimberly 24-Oct-06 FALSE
Gleco, Kimberly 25-Jul-06 FALSE
Gleco, Kimberly 31-Jul-06 FALSE
Gleco, Kimberly 27-Jul-06 FALSE
Gleco, Kimberly 31-Jul-06 FALSE
Gleco, Kimberly 26-Oct-06 FALSE
Hilstolsky 03-Nov-06 FALSE
Gleco, Kimberly 22-Aug-06 FALSE
Gleco, Kimberly 23-Oct-06 TRUE
Gleco, Kimberly 24-Oct-06 TRUE
Gleco, Kimberly 24-Oct-06 TRUE


When running the query results I should have: