Hello,

How would I be able to specify the criteria in a select query so that
only the _earliest of records containing the same ID will be
displayed?

For example:

John Doe Dec 21, 2002
John Doe Dec 23, 2002
John Doe Dec 24,2002
Fred Flint Jan 12, 2003
Fred Flint Jan 14, 2003
Fred Flint Jan 15, 2003
etc.

For these records, pull up:

John Doe Dec 21, 2002
Fred Flint Jan 12, 2003
etc.

And while I'm asking, is it possible to do this with a select query of
a select query? And if so, would the second query work its way through
the first query automatically?

Thanks very much! Eric

You could add a calculated field to your query:

Age: (Now()-[YourDate])

Then use a Group By query to select the max value for Age

hth

you can do this with a correlated subselect query:rudy
http://r937.com/

Hi Marion,

Thanks for your reply. I like your idea.
I tried it but it didn't work though.

>> Age: (Now()-[YourDate])
>> Then use a Group By query to select the max value for Age

After running it, Access changed the calculated field to:

Age: Max((Now()-[DateDet]))
and changed the Total: to "Expression"

The results:
16.53...
12.53...
12.53...
12.53...
12.53...
11.53...
11.53...

In other words the same as I was getting before, but with an additional field with number dates.
I still get repeated dates. Thanks! Eric

Hi Eric,

Just re-created your table on this side to find the solution.
Forget about the calculated field Age, it's simpler than that.

Design a query,
Include both your fields PersonName en PersonDate
Switch to Group
Set PersonDate on Min-value.

That's all.

Doing so, my query looks like:

SELECT YourTable.PersonName, Min(YourTable.PersonDate) AS MinPersonDate
FROM YourTable
GROUP BY YourTable.PersonName;

hth

marion, that only works when you are selecting the a grouping field plus the date

it does not work when selecting the row that has the highest date

consider this: suppose the table is clientname, depositdate, depositamount

what was the amount of the last deposit for each client?

you will need that correlated subquery

Hi Rudy,

Point taken!
I will study the subquery topic.
Might bring more elegant solutions when things get complicated.

Thanks

Arco

Hi Rudy,

I guess I'll have a stab at your code now, since the "easier" solutions haven't worked.

select recordkey
, personname
, somedate
from yourtable X
where somedate =
( select max(somedate)
from yourtable
where recordkey = X.recordkey )

Do I enter all of the above in the criteria field?
Do I also use ,personname ,somedate ?
They aren't comments? I've never seen commas used before.
And if my table is named Detentions I'd put Detentions X ?

Thanks! Eric

Query > New > SQL View

paste the query

make sure you use your own table and column names

yes, the X is important, it is the correlation variable


rudy

Hi Rudy,

I tried out your code but had some problems.
I get the following:

Enter Parameter value:
somedate
recordkey
x.recordkey

I thought when I opened the query it would automatically find the records for me with the earliest dates.

Thanks! Eric

do you have a column called somedate? do you have a column called recordkey? i used those names as examples, because you did not say what the names of your columns are

make sure you use your own table and column names


rudy