S
SheriTingle
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 !!!
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 (
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 !!!