Same Columns Different Queries

  • Thread starter Thread starter Douglas J. Steele
  • Start date Start date
D

Douglas J. Steele

Unless you need to be able to update the resultant query, use a UNION query:

SELECT ID, [Name], Account
FROM Query1
UNION
SELECT ID, [Name], Account
FROM Query2

If you need to know from which query the value came, try:

SELECT ID, [Name], Account, "Query1" AS Source
FROM Query1
UNION
SELECT ID, [Name], Account, "Query2"
FROM Query2
 
I have 2 different queries that I need to combine (perhaps) into one. The
problem is I need to have the data go to the same columns. Such as...

ID Name Account Number

Query 1
ID
Name
Account

Query 2
ID
Name
Account

As you can see they both have the same field names but different info.
Any help is appreciated.
Thanks
DS
 
I came up with this which I think is in the right direction but it still
doesn't return data.

With Me.ListAccounts
.RowSource = "SELECT tblPayName.PayNameID, tblPayName.PayName,
tblPayName.PayAppliedCOA " & _
"FROM tblPayName " & _
"ORDER BY tblPayName.PayName UNION " & _
"SELECT tblPayTypes.PayTypeID, tblPayTypes.PayTypeName,
tblPayTypes.PayTypeCOA " & _
"FROM tblPayTypes " & _
"ORDER BY tblPayTypes.PayTypeName;"
.ColumnCount = 3
.ColumnWidths = "0in;2 in;.5 in"
.Requery
End With

Thanks
DS
 
I got it to work but the sorting is a problem! Any suggestions on how to
sort this?
Thanks
DS
 
You can only put an ORDER BY at the end of the statement: it applies to the
entire query.

..RowSource = "SELECT tblPayName.PayNameID, tblPayName.PayName, " & _
"tblPayName.PayAppliedCOA " & _
"FROM tblPayName " & _
"UNION " & _
"SELECT tblPayTypes.PayTypeID, tblPayTypes.PayTypeName, " & _
"tblPayTypes.PayTypeCOA " & _
"FROM tblPayTypes " & _
"ORDER BY PayTypeName"

Note that since you're only select from a single table in each subquery, you
can simplify that to

..RowSource = "SELECT PayNameID, PayName, PayAppliedCOA " & _
"FROM tblPayName " & _
"UNION " & _
"SELECT PayTypeID, PayTypeName, PayTypeCOA " & _
"FROM tblPayTypes " & _
"ORDER BY PayTypeName"
 
Thanks Doug, I appreciate the info.
DS
Douglas J. Steele said:
You can only put an ORDER BY at the end of the statement: it applies to
the entire query.

.RowSource = "SELECT tblPayName.PayNameID, tblPayName.PayName, " & _
"tblPayName.PayAppliedCOA " & _
"FROM tblPayName " & _
"UNION " & _
"SELECT tblPayTypes.PayTypeID, tblPayTypes.PayTypeName, " & _
"tblPayTypes.PayTypeCOA " & _
"FROM tblPayTypes " & _
"ORDER BY PayTypeName"

Note that since you're only select from a single table in each subquery,
you can simplify that to

.RowSource = "SELECT PayNameID, PayName, PayAppliedCOA " & _
"FROM tblPayName " & _
"UNION " & _
"SELECT PayTypeID, PayTypeName, PayTypeCOA " & _
"FROM tblPayTypes " & _
"ORDER BY PayTypeName"
Click to expand...
 
Back
Top