Joining two queries

[Tia]

Is there a way to join two queries together?
In query 1, i have Invoice ID, Description, and Total
In query 2, I have the same fields.

I would like to see all the records from both queries. Please advise.

 

[Dirk Goldgar]

Is there a way to join two queries together?
In query 1, i have Invoice ID, Description, and Total
In query 2, I have the same fields.

I would like to see all the records from both queries. Please advise.

I believe what you're talking about is a "union query". You don't join the
queries, but you merge the two resulting sets of records. You can only
build a union query in SQL View. Here's a model for the syntax:

SELECT * FROM Query1
UNION ALL
SELECT * FROM Query2

That only works if both queries have exactly the same fields in the same
order. If they have different fields, or the fields aren't in the same
order, you have to specify the list of fields for each:

SELECT FieldA, FieldB, FieldC FROM Query1
UNION ALL
SELECT FieldD, FieldE, FieldF FROM Query2

In the above, the names of the fields in the result set will be the first
set of fields included, those from Query1.

Also, the "ALL" keyword in the above statements says to include all records
from both queries, even if some are duplicates. If you want to eliminate
duplicate records, just say "UNION", not "UNION ALL":

SELECT * FROM Query1
UNION
SELECT * FROM Query2

 

[Krzysztof Pozorek [MVP]]

(...)

Is there a way to join two queries together?
In query 1, i have Invoice ID, Description, and Total
In query 2, I have the same fields.

I would like to see all the records from both queries. Please advise.

SELECT * FROM Query1
UNION ALL SELECT * FROM Query2

....or if You need unique records:
SELECT * FROM Query1
UNION SELECT * FROM Query2

K.P.

 

[George Nicholson]

You want a UNION query:

SELECT "Qry1" as DataSource, [Invoice ID], Description, Total FROM [Query 1]
UNION SELECT "Qry2", [Invoice ID], Description, Total FROM [Query 2]


- UNION queries have to be written in SQL view of the QueryDesigner (you
can usually construct the first SELECT clause in design view and then switch
to SQL view and fairly easily cut-and paste the remainder.)
- All SELECT clauses of a UNION query must have the same number of
fields, even if they are empty "place holders"
- The field names in the Final result will derive from the FieldNames or
Aliases used in the first SELECT Clause
- I find it a good idea to add a Datasource classification to each
record so that I can easily backtrack "bad" data, or distinguish between
"Budget" and "Actual" figures, etc.


SELECT "Qry1Actual" as DataSource, [Invoice ID], Description, Total FROM
[Query 1]
UNION SELECT "Qry2Budget", [Invoice ID], Description, Total FROM [Query 2]