Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / General 2 / January 2008

Tip: Looking for answers? Try searching our database.

Joining two queries

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Tia - 18 Jan 2008 20:20 GMT
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 - 18 Jan 2008 20:39 GMT
> 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

Signature

Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Krzysztof Pozorek [MVP] - 18 Jan 2008 20:39 GMT
(...)
> 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 - 18 Jan 2008 20:48 GMT
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]

Signature

HTH,
George

> 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.
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.