How to choose later of two or more records

[Phil Smith]

I have a union query in two pieces. One piece deals with payments by
cash and checks, one deals with payments by credit card and wire
transfer, all per invoice.

There does come situations where a single invoice will be payed
partially in each category, with DateReceived and a few other details
being different for each record. In this case, I get multiple entries
for each invoice, which is a NO-NO.

I want the record which has the latest DateReceived, and all of it's
attendant info.

I can do it by running a totals query on my union query with
MAX(DateReceived), then another query that looks for the record in my
union query matching that invoice and that MAX(DateReceived,) but that
means I am effectively running 4 queries to get my information.

Is there an easier way to get that final result?

Phil

 

[Banana]

I have a union query in two pieces. One piece deals with payments by
cash and checks, one deals with payments by credit card and wire
transfer, all per invoice.

There does come situations where a single invoice will be payed
partially in each category, with DateReceived and a few other details
being different for each record. In this case, I get multiple entries
for each invoice, which is a NO-NO.

I want the record which has the latest DateReceived, and all of it's
attendant info.

I can do it by running a totals query on my union query with
MAX(DateReceived), then another query that looks for the record in my
union query matching that invoice and that MAX(DateReceived,) but that
means I am effectively running 4 queries to get my information.

Is there an easier way to get that final result?

Phil

Wouldn't it be simpler to group by invoices?

Furthermore, I'm no accountant but if they paid in two different ways,
I'd want it to pop up as two entries with accompanying total on the
report to clearly trace the money...

 

[Phil Smith]

I don't have to worry about the accounting, that is already handled. In
this case, an invoice is not closed and paid off until that last payment
is made. The purpose of the report is to determine when it was paid
off, and how long it took each invoice to pay, among other things.
Since it is a Union Query, each piece of the SQL pulls seperately.

 

[Banana]

I don't have to worry about the accounting, that is already handled. In
this case, an invoice is not closed and paid off until that last payment
is made. The purpose of the report is to determine when it was paid
off, and how long it took each invoice to pay, among other things. Since
it is a Union Query, each piece of the SQL pulls seperately.

Fair enough.

Did you try this:

SELECT u.<whatever>
FROM (SELECT <whatever>
FROM <whatever>
UNION
SELECT <whatever>
FROM <whatever>) u
GROUP BY u.invoices;

 

[Phil Smith]

No I didn't. Seems liek it is still basicly running multiple queries,
so I am not sure if I gain much, but I will try it.

Thanx