Union Query not working yet

[Guest]

Hello again. I can't seem to get this union query thing going right. I just
posted a question about 7 items down the list with the subject "union query
still a problem". I have tried everything people have suggested to fix the
union problem but what keeps happening is that when I make the suggested
changes the query runs without an error message but the data selected from
the tables is not the correct information. Instead of selecting the most
recent date it selected the first date in the list or the first date in the
current year. I need the original query to run as written to select the most
recent date.

Here's the original post:

Thanks for the earlier help but I still can't seem to make this work. I'm
using Office 2003.

I have 4 queries that hold credit card data. Each one finds the most recent
date of the payment and therefore the current balance on that credit card.
Now I want to make a union query that puts all four of them into one query.
When I copy the SQL syntax from the original queries I get a message that
tells me the "ORDER BY expression (

.field) includes fields that are
not selected by the query. Only those fields requested in the first query can
be included in an ORDER BY expression."

When I posted this last night the suggestion was to use only one order by
query placing it at the end but when i did that the query gave me the wrong
dates and data. Maybe with the exact syntax life would be easier. THANKS !

Here's my syntax:

SELECT TOP 1 [VISA].StatementDate, [VISA].Category, [VISA].NewBalance,
[VISA].MinimumPaymentDue, [VISA].AccountNO
FROM [VISA]
WHERE ((([VISA].NewBalance)<>0))
ORDER BY [VISA].StatementDate DESC;

UNION

SELECT TOP 1 [VISA2].StatementDate, [VISA2].Category, [VISA2].NewBalance,
[VISA2].MinimumPaymentDue, [VISA2].AccountNO
FROM [VISA2]
WHERE ((([VISA2].NewBalance)<>0))
ORDER BY [VISA2].StatementDate DESC;

You all rock being willing to help so much !!!

 

[Guest]

Try this:

SELECT [A].* FROM [Visa] as A
UNION ALL SELECT .* FROM [Visa2] as B;

Hope this works.

 

[Michel Walsh]

Hi,


I *think* we have to explicitly mentioned the fields, when we use a UNION.


That can be done, in the query designer by double clicking on the "caption"
of the table, in the upper part, that will select all the fields, then drag
and drop them in the grid, that gives all the fields.



Hoping it may help,
Vanderghast, Access MVP

Try this:

SELECT [A].* FROM [Visa] as A
UNION ALL SELECT .* FROM [Visa2] as B;

Hope this works.

Hello again. I can't seem to get this union query thing going right. I
just
posted a question about 7 items down the list with the subject "union
query
still a problem". I have tried everything people have suggested to fix
the
union problem but what keeps happening is that when I make the suggested
changes the query runs without an error message but the data selected
from
the tables is not the correct information. Instead of selecting the most
recent date it selected the first date in the list or the first date in
the
current year. I need the original query to run as written to select the
most
recent date.

Here's the original post:

Thanks for the earlier help but I still can't seem to make this work. I'm
using Office 2003.

I have 4 queries that hold credit card data. Each one finds the most
recent
date of the payment and therefore the current balance on that credit
card.
Now I want to make a union query that puts all four of them into one
query.
When I copy the SQL syntax from the original queries I get a message that
tells me the "ORDER BY expression (

.field) includes fields that
are
not selected by the query. Only those fields requested in the first query
can
be included in an ORDER BY expression."

When I posted this last night the suggestion was to use only one order by
query placing it at the end but when i did that the query gave me the
wrong
dates and data. Maybe with the exact syntax life would be easier. THANKS
!

Here's my syntax:

SELECT TOP 1 [VISA].StatementDate, [VISA].Category, [VISA].NewBalance,
[VISA].MinimumPaymentDue, [VISA].AccountNO
FROM [VISA]
WHERE ((([VISA].NewBalance)<>0))
ORDER BY [VISA].StatementDate DESC;

UNION

SELECT TOP 1 [VISA2].StatementDate, [VISA2].Category, [VISA2].NewBalance,
[VISA2].MinimumPaymentDue, [VISA2].AccountNO
FROM [VISA2]
WHERE ((([VISA2].NewBalance)<>0))
ORDER BY [VISA2].StatementDate DESC;

You all rock being willing to help so much !!!