Query oldest record by date

[BillT]

Hi:

I'm trying to create a query that retrieves the oldest
dated record.

Scenario: Battery rotation program.

User inputs dead (discharged) battery from lift truck into
database and upon input, receives the next available
charged battery for his/her truck.

I've been fooling around with the date expressions but I
can't seem to get the correct syntax.

TIA
BillT

 

[Guest]

If you have a date field in your query, you can (in the
design view) sort the query by descending order from the
date field.
HTH

 

[BillT]

Thanks for your reply however, that's not exactly what I'm
looking for.

I need a charged battery# queried by the oldest date to be
presented to the user when he/she enters a dead battery.

Regards
Bill

 

[Bob]

You could order them by date in descending order, then
select the top 1, which would give you the oldest date.

 

[Tom Ellison]

Dear Bill:

It sounds like you have a table with Battery# and DateExchanged. If
you want the earliest (oldest) date of exchange for a Battery#, you
will need a "correlated subquery" which returns the date for each
battery you have:

SELECT BatteryNumber, MIN(DateExchanged) AS MinDateExchanged
FROM YourTable
GROUP BY BatteryNumber

This may be just a starter. It will show the oldest date for each
battery. You must change it for the proper table name and column
names you have used.

If you want to see other columns of the table for the row which has
the oldest exchange for each battery, that's where the correlated
subquery comes in:

SELECT *
FROM YourTable T
WHERE DateExchanged = (SELECT MIN(DateExchanged)
FROM YourTable T1 WHERE T1.BatteryNumber = T.BatteryNumber)

If the earliest date on which someone exchanged a battery is a date on
which two exchanges were made, then both will show up. There's no way
to arbitrate between them given the above information. Perhaps it
isn't possible for this to happen, or it is unlikely.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts