select oldest record?

[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)

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.

 

[Michael D. House]

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.

 

[Michael D. House]

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)

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)

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.

 

[John Vinson]

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)

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!