Union Query Problem

[Allen Browne]

Your UNION query contains 2 SELECT statements.
You added a WHERE clause to one of them.
Did you need to add a WHERE clause to the 2nd one as well?

If that is not the solution you need, it might help if you explained why
your results are not what you expect. Is it generating an error? Wrong
results? Nothing?

 

[Guest]

What's not so fine now? I'm guessing it's that you have 4 fields returned in
the upper part of the statement and only three in the lower. Union queries
require the same number and type of fields returned.

 

[DS]

I'm using this UNION Query that worked fine untill I added the WHERE clause.
Any help is appreciated.
Thanks
DS

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

 

[John Spencer]

Change Row source to one of the following and also change columnCount and
columnWidth to match


..RowSource = "SELECT tblPayName.PayNameID, tblPayName.PayName,
tblPayName.PayAppliedCOA" & _
"FROM tblPayName " & _
"WHERE tblPayName.PayNameTypeID = 2 UNION " & _
"SELECT tblPayTypes.PayTypeID, tblPayTypes.PayTypeName,
tblPayTypes.PayTypeCOA " & _
"FROM tblPayTypes " & _
"ORDER By PayName;"
..ColumnCount = 3
..ColumnWidths = "0 in;2 in;.5 in"


OR

..RowSource = "SELECT tblPayName.PayNameID, tblPayName.PayName,
tblPayName.PayAppliedCOA, tblPayName.PayNameTypeID " & _
"FROM tblPayName " & _
"WHERE tblPayName.PayNameTypeID = 2 UNION " & _
"SELECT tblPayTypes.PayTypeID, tblPayTypes.PayTypeName,
tblPayTypes.PayTypeCOA, Null " & _
"FROM tblPayTypes " & _
"ORDER By PayName;"
..ColumnCount = 4
..ColumnWidths = "0 in;2 in;.5 in;0 in"
..Requery
End With

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[DS]

It seems that a union Query won't work unless both statements have an equal
number of columns. I didi that and that worked!
Thanks
DS

 

[DS]

So I'm assuming that whats in the where statement doesn't have to be in the
Select Part. This evens out the columns so it works!
Thanks
DS