select oldest record?

  • Thread starter Thread starter John Vinson
  • Start date Start date
J

John Vinson

I'm trying to select the oldest transactions in a table using a select
query.
(eventually I'll convert it to an update query, but one step at a time)

I'm trying to return only one record at a time. (or the oldest records if
multiple records exist for the same date.)
(I'm providing other criteria, but still retrieving multiple records, my
goal is to apply payments to the oldest outstanding transactions)
Click to expand...

You can do this three different ways:

- a Subquery, using a criterion of

=(SELECT Min([transactiondate]) FROM yourtable AS X WHERE <criteria>)

using criteria such as WHERE X.AccountNo = yourtable.AccountNo to find
the earliest transaction for that account; or using no WHERE clause at
all if you want to find the earliest transaction in the entire table.

- More efficiently, create a Totals query selecting the Min() of the
transaction and joining it to your table.

UNFORTUNATELY, neither of these queries will be updateable!

- Use the DMin() function to create a criterion selecting the minimum
date.
 
I'm trying to select the oldest transactions in a table using a select
query.
(eventually I'll convert it to an update query, but one step at a time)

I'm trying to return only one record at a time. (or the oldest records if
multiple records exist for the same date.)
(I'm providing other criteria, but still retrieving multiple records, my
goal is to apply payments to the oldest outstanding transactions)

Any suggestions would be greatly appreciated.

-Michael D. House
(e-mail address removed)
or
(e-mail address removed)

Thank You for any assistance you can provide.
 
John,

Thank you for the help, I do need a query that will be updateable.

Any other suggestions?
I'm not having any success with the DMIN function (likely because I'm too
new)


John Vinson said:
I'm trying to select the oldest transactions in a table using a select
query.
(eventually I'll convert it to an update query, but one step at a time)

I'm trying to return only one record at a time. (or the oldest records if
multiple records exist for the same date.)
(I'm providing other criteria, but still retrieving multiple records, my
goal is to apply payments to the oldest outstanding transactions)
Click to expand...

You can do this three different ways:

- a Subquery, using a criterion of

=(SELECT Min([transactiondate]) FROM yourtable AS X WHERE <criteria>)

using criteria such as WHERE X.AccountNo = yourtable.AccountNo to find
the earliest transaction for that account; or using no WHERE clause at
all if you want to find the earliest transaction in the entire table.

- More efficiently, create a Totals query selecting the Min() of the
transaction and joining it to your table.

UNFORTUNATELY, neither of these queries will be updateable!

- Use the DMin() function to create a criterion selecting the minimum
date.
Click to expand...
 
John,

Thank you for the help, I do need a query that will be updateable.

Any other suggestions?
I'm not having any success with the DMIN function (likely because I'm too
new)
Click to expand...
DMin() is the only way you'll get the query to be updateable. Please
post the current SQL that you're using, with some indication of what
problem you're having!
 
Back
Top