Multiple Queries into one

[jtm013]

Hello All, thank you in advance for any and all assistance!

Okay I have 7 queries each one pulls from the same table (Tbl_Data_Entry) and they display late jobs.

Each queries looks like the below the only difference is where one is Inspection, the next may be Painting or Planning, or whatever.

Field	Record Number	Initiation Date	Customer Name	Inspection Due	Inspection Completed	Inspector	Days Late: Date() - [Tbl_Data_Entry.Inspection Due]	Initiation Date
Table	Tbl_Data_Entry	Tbl_Data_Entry	Tbl_Data_Entry	Tbl_Data_Entry	Tbl_Data_Entry	Tbl_Data_Entry	Tbl_Data_Entry	Tbl_Data_Entry
Total:	Group By	Group By	Group By	Group By	Group By	Group By	Expression	Where
Sort
Show	Checked	Checked	Checked	Checked	Checked	Checked	Checked
Criteria				< Date()	Is Null			>= #08/11/2014#
Or

Individually each query works correctly and outputs something similar to the following:

382---08/12/2014---Mr. Green---08/14/14--- ---Smith,John---1

The problem I am having is the boss wants a report which shows all late jobs.

So I am trying to make a query that references all 7 of my existing queries and would look similar but would have a "What is late" field at the end.
The ideal output would look something like this:

382---08/12/2014---Mr. Green---08/14/14--- ---Smith,John---1---Inspection Late

Here's the problem- I don't know how to query for that "what is due" field.

Any and all help is appreciated, thanks!!!

[ranman256]

Make a union query: type in Sql mode , not Query grid.

select * from Q1 where late
union
select * from Q2 where late
union
...
select * from Q7 where late

[jtm013]

Okay, I tried your method but it didn't work.
So SQL for the query is now:

select * from Qry_Incomplete_Remedial_Action where late
union
select * from Qry_Incomplete_Root_Cause_Analysis where late
union
select * from Qry_Incomplete_CA_Plan where late
union
select * from Qry_Incomplete_Response where late
union
select * from Qry_Incomplete_Effectivity_Date where late
UNION select * from Qry_Incomplete_Verification where late;

When I run the query I get a text box labeled "late" regardless of what I type in it (or if I don't type anything) the output ends up being all late records but there still is no "what is late field"
For instance output should end up something like this:

382---08/12/2014---Mr. Green---08/14/14--- ---Smith,John---1---Inspection Late
383---08/12/2014---Mr. Red--- 08/14/14--- ---Jones,Jack---1---Remedial Action Late
etc.

Thanks for your assistance with this!!!

[June7]

Is this what you want:

SELECT *, "Remedial" AS WhatIsLate FROM Qry_Incomplete_Remedial_Action WHERE late

UNION

SELECT *, "RootCause" FROM Qry_Incomplete_Root_Cause_Analysis WHERE late

....

[jtm013]

June7-

Yes!!! Thank you!
Out of curiosity- when I run the Query it still gives me a dialogue box labeled "late" and regardless of what I put in there it has the same output. Is there a way to get rid of that?

[June7]

Sorry, I was just pulling that from ranman's example. I guess the 'WHERE late' criteria is not needed if each of the 7 queries already filtered to desired records.

[jtm013]

Awesome that did it! Final solution was:

select *, "Remedial" AS WhatIsLate from Qry_Incomplete_Remedial_Action
union
select *, "Root Cause" AS WhatIsLate from Qry_Incomplete_Root_Cause_Analysis
union
select *, "CA Plan" AS WhatIsLate from Qry_Incomplete_CA_Plan
union
select *, "Response" AS WhatIsLate from Qry_Incomplete_Response
union
select *, "Effectivity" AS WhatIsLate from Qry_Incomplete_Effectivity_Date
UNION select *, "Verification" AS WhatIsLate from Qry_Incomplete_Verification;

Thanks!!!