LAST in a Totals Query

[slickdock]

Using a totals query, I need to get the last [txtStatus] value in
tblPayments. It is returning LAST with respect to [PaymentID], which is the
primary key in that table. How do I specify that I want last [txtStatus]
value with respect to [PaymentDate]?

Thank you.

 

[Tom van Stiphout]

On Thu, 23 Jul 2009 13:01:02 -0700, slickdock

Use an ORDER BY clause.

Personally I don't use First and Last too much. I like Min and Max
better because they are independent of sort order. In your case I
might get the Max(PaymentID), and then look up the Status for that
PaymentID.

-Tom.
Microsoft Access MVP

 

[slickdock]

I don't want the Max(PaymentID), I want the [txtStatus] of the Max(PaymentDate)

On Thu, 23 Jul 2009 13:01:02 -0700, slickdock

Use an ORDER BY clause.

Personally I don't use First and Last too much. I like Min and Max
better because they are independent of sort order. In your case I
might get the Max(PaymentID), and then look up the Status for that
PaymentID.

-Tom.
Microsoft Access MVP

Using a totals query, I need to get the last [txtStatus] value in
tblPayments. It is returning LAST with respect to [PaymentID], which is the
primary key in that table. How do I specify that I want last [txtStatus]
value with respect to [PaymentDate]?

Thank you.

 

[Tom van Stiphout]

On Thu, 23 Jul 2009 15:16:01 -0700, slickdock

select Status from tblPayments
where PaymentDate = (select Max(PaymentDate) from tblPayments)

-Tom.
Microsoft Access MVP

I don't want the Max(PaymentID), I want the [txtStatus] of the Max(PaymentDate)

On Thu, 23 Jul 2009 13:01:02 -0700, slickdock

Use an ORDER BY clause.

Personally I don't use First and Last too much. I like Min and Max
better because they are independent of sort order. In your case I
might get the Max(PaymentID), and then look up the Status for that
PaymentID.

-Tom.
Microsoft Access MVP

Using a totals query, I need to get the last [txtStatus] value in
tblPayments. It is returning LAST with respect to [PaymentID], which is the
primary key in that table. How do I specify that I want last [txtStatus]
value with respect to [PaymentDate]?

Thank you.

 

[slickdock]

That didn't work. What I was able to do was create a query of recordID and
PaymentDate, sorted ascending by PaymentDate. Then I built a totals query off
of that that Grouped recordID, with Max of PaymentDate. That gave me the last
payment date that I was seeking. Funny, when I tried Last PaymentDate
instead of Max PaymentDate in this second query, Last gave me wrong results,
but Max gave me right results.

On Thu, 23 Jul 2009 15:16:01 -0700, slickdock

select Status from tblPayments
where PaymentDate = (select Max(PaymentDate) from tblPayments)

-Tom.
Microsoft Access MVP

I don't want the Max(PaymentID), I want the [txtStatus] of the Max(PaymentDate)

On Thu, 23 Jul 2009 13:01:02 -0700, slickdock

Use an ORDER BY clause.

Personally I don't use First and Last too much. I like Min and Max
better because they are independent of sort order. In your case I
might get the Max(PaymentID), and then look up the Status for that
PaymentID.

-Tom.
Microsoft Access MVP



Using a totals query, I need to get the last [txtStatus] value in
tblPayments. It is returning LAST with respect to [PaymentID], which is the
primary key in that table. How do I specify that I want last [txtStatus]
value with respect to [PaymentDate]?

Thank you.

 

[John W. Vinson]

Funny, when I tried Last PaymentDate
instead of Max PaymentDate in this second query, Last gave me wrong results,
but Max gave me right results.

Last means "the last record in disk storage order" - it does NOT necessarily
mean the most recent record, or the record with the latest date, or anything
else predictable! It's simply an arbitrary, unpredictable record.