union query still a problem

[Guest]

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]

Only one ORDER BY in a UNION.

Select ....
UNION
Select ...
UNION
.....
ORDER BY ......

The UNIONs construct what is in effect a table. Therefore, just as in a
single SELECT statement from a single table, you can only have a single ORDER
BY clause.

See the Note at the end of Access Help on the UNION operator.

 

[Lynn Trapp]

Sheri,
A Union query allows only one Order By clause and it should appear at the
end of the entire query. You might consider doing the following:

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

That simply says to order by the first column in Descending order.


--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html

 

[John Vinson]

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 !

Unfortunately you'll need to store four queries, each with its own
OrderBy and Top clauses; and UNION those queries. E.g.

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

and so on; then

SELECT qryVisa.StatementDate, qryVisa.Category, qryVisa.NewBalance,
qryVisa.MinimumPaymentDue, qryVisa.AccountNo
FROM qryVisa
UNION
SELECT qryVisa2.StatementDate, qryVisa2.Category, ...

etc.

The ORDER BY clause in a UNION query applies to the query as a whole,
and that's conflicting with your need to have separate ORDER BY's in
each SELECT so you can use the TOP clause.


John W. Vinson[MVP]

 

[John Spencer (MVP)]

Avoid the TOP clause. In this case you should be able to get back your desired
records using a subquery.

SELECT [VISA].StatementDate, [VISA].Category, [VISA].NewBalance,
[VISA].MinimumPaymentDue, [VISA].AccountNO
FROM [VISA]
WHERE StatementDate =
(SELECT MAX(V.StatementDate)
FROM Visa as V
WHERE V.NewBalance <> 0)

UNION

SELECT StatementDate, Category, NewBalance,
MinimumPaymentDue, AccountNO
FROM [VISA2]
WHERE StatementDate =
(SELECT MAX(V.StatementDate)
FROM Visa2 as V
WHERE V.NewBalance <> 0)

UNION

SELECT StatementDate, Category, NewBalance,
MinimumPaymentDue, AccountNO
FROM [VISA3]
WHERE StatementDate =
(SELECT MAX(V.StatementDate)
FROM Visa3 as V
WHERE V.NewBalance <> 0)

UNION

SELECT StatementDate, Category, NewBalance,
MinimumPaymentDue, AccountNO
FROM [MasterCard]
WHERE StatementDate =
(SELECT MAX(V.StatementDate)
FROM MasterCard as V
WHERE V.NewBalance <> 0)

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 !

Unfortunately you'll need to store four queries, each with its own
OrderBy and Top clauses; and UNION those queries. E.g.

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

and so on; then

SELECT qryVisa.StatementDate, qryVisa.Category, qryVisa.NewBalance,
qryVisa.MinimumPaymentDue, qryVisa.AccountNo
FROM qryVisa
UNION
SELECT qryVisa2.StatementDate, qryVisa2.Category, ...

etc.

The ORDER BY clause in a UNION query applies to the query as a whole,
and that's conflicting with your need to have separate ORDER BY's in
each SELECT so you can use the TOP clause.

John W. Vinson[MVP]