union query problem 1

[mustangcoupe]

I have this union query that I display on my form... I just added the fourth query to it but it only displays data from the first three on the form (even though in the query window it shows data from all the queries!)
I am using SELECT TOP 25 qry_recent_logs.* FROM qry_recent_logs ORDER BY qry_recent_logs.rptDate DESC , qry_recent_logs.rptTime DESC; as the row source on my form.

qry_recent_logs

SELECT tbl_report.ReportID, tbl_report.rptDate, tbl_report.rptTime, lu_rpt_activity.RPTactivity, [firstname] & " " & [lastname] AS [By], tbl_report.Revision, tbl_report.Notes
FROM (tbl_report LEFT JOIN lu_rpt_activity ON tbl_report.Activity = lu_rpt_activity.RPTactivityID) LEFT JOIN lu_staff ON tbl_report.By = lu_staff.staffID
WHERE (((tbl_report.JobID)=forms!job_form!jobID))

UNION

SELECT tbl_doc.docID, tbl_doc.docDate, tbl_doc.docTime, lu_doc_activity.RPTactivity, [firstname] & " " & [lastname] AS [By], tbl_doc.Revision, tbl_doc.Notes
FROM (tbl_doc LEFT JOIN lu_doc_activity ON tbl_doc.Activity = lu_doc_activity.RPTactivityID) LEFT JOIN lu_staff ON tbl_doc.By = lu_staff.staffID
WHERE (((tbl_doc.JobID)=forms!job_form!jobID))

UNION 

SELECT tbl_test_log.testlogID, tbl_test_log.logDate, tbl_test_log.logTime, lu_test_activity.Testactivity, [firstname] & " " & [lastname] AS [By], tbl_test_log.Revision, tbl_test_log.Notes
FROM (tbl_test_log LEFT JOIN lu_staff ON tbl_test_log.By = lu_staff.staffID) INNER JOIN lu_test_activity ON tbl_test_log.Activity = lu_test_activity.TestactivityID
WHERE (((tbl_test_log.JobID)=[forms]![job_form]![jobID]));

UNION 

SELECT tbl_shipping.ShippingID, tbl_shipping.DateShipped, "" AS TimeShipped, lu_shipping_activity.Shippingactivity, [firstname] & " " & [lastname] AS [By], "" AS Revision, tbl_shipping.Notes
FROM lu_staff RIGHT JOIN (lu_Shippingmethod INNER JOIN (lu_shipping_activity INNER JOIN tbl_shipping ON lu_shipping_activity.shippingactivityID = tbl_shipping.Shippingactivity) ON lu_Shippingmethod.ShippingmethodID = tbl_shipping.Shippedvia) ON lu_staff.staffID = tbl_shipping.[Shipped By]
WHERE (((tbl_shipping.JobID)=[forms]![job_form]![jobID]));

--Todd


TechnicalUser pretending to be a programmer(shhh… the boss doesn’t know yet)

 

[GingerR]

just grabbing at straws here...are there dates from the fourth part of the big query that are in the top 25 when they are sorted in descending order? What I mean is, when you just run the union query and sort desc by date and time, are there any "Shipping" records in the first 25?

try moving the fourth section to the top and see what happens.

you could also add a portion to the beginning of each of the four chunks to identify which chunk of the query they come from, i.e

 SELECT "Report" as Type,tbl_report.ReportID, tbl_report.rptDate, tbl_report.rptTime...UNION

SELECT "DOC", tbl_doc.docID, tbl_doc.docDate, tbl_doc.docTime...

etc..the last two being maybe "TestLog" and "Shipping"...i'm just using the names of the tables. Then you can confirm the existance of "Shipping" records and if there are any in the top 25 when in desc order.

Let me know how it goes...

g

 

[mustangcoupe]

when I run the query in the query builder... there is are only 2 records that pass the query. (One from the first query one from the last query.) BUT on the report only the first query showes.

could it be I am passing --- into the two time fields on the last query? (I want these to be blank as there is no time field for this in the tables...)

--Todd


TechnicalUser pretending to be a programmer(shhh… the boss doesn’t know yet)

 

[PHV]

Replace this:
SELECT tbl_shipping.ShippingID, tbl_shipping.DateShipped, "" AS TimeShipped, lu_shipping_activity.Shippingactivity, [firstname] & " " & [lastname] AS [By], "" AS Revision, tbl_shipping.Notes
By this:
SELECT tbl_shipping.ShippingID, tbl_shipping.DateShipped, Null, lu_shipping_activity.Shippingactivity, [firstname] & " " & [lastname], Null, tbl_shipping.Notes

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[mustangcoupe]

Thank You Sooo much PHV

*

--Todd


TechnicalUser pretending to be a programmer(shhh… the boss doesn’t know yet)