I need to query two tables with different types of data. The common thread is the serial numbers. I need to make a new table which lists in chronilogical order a listing of package history. One table lists the maintence, and the other shipment schedule. The only way I've found that returns accurate data is to use 3 separate queries (listed below).
My problem is in the WHERE clause.
1/ If I use the WHERE clause in the union query it either is too slow, or it doesn't return all the history...or history of the wrong serial numbers....or it requires two parameter boxes.
2/ If I put the Where clause in the underlying queries 1&2, Two parameter boxes come up requiring the same information.
I want only one parameter box ...[Serial#]
Please help me find a solution to this. Thank You in advance. I appoligize if I'm providing either too much or too little information. I don't have a good feel for that yet. This is my first post.
QRY1
SELECT DISTINCT Status_Changes.Serial_No, Status_Changes.Date, Status_Changes.Container_Condition AS Status, "" AS Condition
FROM Status_Changes
WHERE (((Status_Changes.Serial_No)=[Serial#]));
QRY2
SELECT DISTINCT Current_Shipments.TNProject, Current_Shipments.[Shipment #] AS Shipment_NO, Current_Shipments.Identifier, Current_Shipments.Return, Current_Shipments.[Departure Date] AS [Date], Current_Shipments.[Arrival Date], Current_Shipments.[One-way] AS Way, Current_Shipments.Serial_NO_1, Current_Shipments.Serial_NO_2, Current_Shipments.Serial_NO_3, Current_Shipments.Serial_NO_4, Current_Shipments.Serial_NO_5, Current_Shipments.Serial_NO_6, "In-Use" AS Status, "" AS Date_Changed, "" AS Condition
FROM Current_Shipments
WHERE (((Current_Shipments.Serial_NO_1)=[Serial#])) OR (((Current_Shipments.Serial_NO_2)=[Serial#])) OR (((Current_Shipments.Serial_NO_3)=[Serial#])) OR (((Current_Shipments.Serial_NO_4)=[Serial#])) OR (((Current_Shipments.Serial_NO_5)=[Serial#])) OR (((Current_Shipments.Serial_NO_6)=[Serial#]));
Union Query based on Qry1 and Qry2
SELECT
Serial_No,
Date,
Status,
"-"AS "Location",
"-"AS "Project_NO",
"-"AS "Shipment_NO",
"-"AS "Return",
"-"AS "Way"
FROM [statusQRY1];
UNION SELECT
[Serial#] AS Serial_No,
Date,
Status,
Identifier,
TNProject,
Shipment_NO,
Return,
Way
FROM [statusQRY2];
My problem is in the WHERE clause.
1/ If I use the WHERE clause in the union query it either is too slow, or it doesn't return all the history...or history of the wrong serial numbers....or it requires two parameter boxes.
2/ If I put the Where clause in the underlying queries 1&2, Two parameter boxes come up requiring the same information.
I want only one parameter box ...[Serial#]
Please help me find a solution to this. Thank You in advance. I appoligize if I'm providing either too much or too little information. I don't have a good feel for that yet. This is my first post.
QRY1
SELECT DISTINCT Status_Changes.Serial_No, Status_Changes.Date, Status_Changes.Container_Condition AS Status, "" AS Condition
FROM Status_Changes
WHERE (((Status_Changes.Serial_No)=[Serial#]));
QRY2
SELECT DISTINCT Current_Shipments.TNProject, Current_Shipments.[Shipment #] AS Shipment_NO, Current_Shipments.Identifier, Current_Shipments.Return, Current_Shipments.[Departure Date] AS [Date], Current_Shipments.[Arrival Date], Current_Shipments.[One-way] AS Way, Current_Shipments.Serial_NO_1, Current_Shipments.Serial_NO_2, Current_Shipments.Serial_NO_3, Current_Shipments.Serial_NO_4, Current_Shipments.Serial_NO_5, Current_Shipments.Serial_NO_6, "In-Use" AS Status, "" AS Date_Changed, "" AS Condition
FROM Current_Shipments
WHERE (((Current_Shipments.Serial_NO_1)=[Serial#])) OR (((Current_Shipments.Serial_NO_2)=[Serial#])) OR (((Current_Shipments.Serial_NO_3)=[Serial#])) OR (((Current_Shipments.Serial_NO_4)=[Serial#])) OR (((Current_Shipments.Serial_NO_5)=[Serial#])) OR (((Current_Shipments.Serial_NO_6)=[Serial#]));
Union Query based on Qry1 and Qry2
SELECT
Serial_No,
Date,
Status,
"-"AS "Location",
"-"AS "Project_NO",
"-"AS "Shipment_NO",
"-"AS "Return",
"-"AS "Way"
FROM [statusQRY1];
UNION SELECT
[Serial#] AS Serial_No,
Date,
Status,
Identifier,
TNProject,
Shipment_NO,
Return,
Way
FROM [statusQRY2];
