selecting earliest dates

  • Thread starter Thread starter evel79
  • Start date Start date
E

evel79

I need help selecting records with the earliest date between 2 or more
records with same Field 1 values. For example:

_Field_1_ *_Field_2_*
2314 *03/05/2003 14:21*
6789 *06/11/2001 05:30*
2314 *03/05/2003 10:45*

I would like for it to return or update/make a table where it gets rid
of the 1st record and keep the rest, meaning:
2314 03/05/2003 10:45 and 6789 06/11/2001 05:30 are the only ones to
show.

Thank you.
 
I need help selecting records with the earliest date between 2 or more
records with same Field 1 values. For example:

_Field_1_ *_Field_2_*
2314 *03/05/2003 14:21*
6789 *06/11/2001 05:30*
2314 *03/05/2003 10:45*

I would like for it to return or update/make a table where it gets rid
of the 1st record and keep the rest, meaning:
2314 03/05/2003 10:45 and 6789 06/11/2001 05:30 are the only ones to
show.
Click to expand...

A Delete query will do this for you. Use a criterion of

=DMin("[Field_2]", "[your-table]", "[Field1] = " & [Field1])

on Field2.

What do you want if there are THREE records with the same Field_1?
Delete only the earliest, leaving two; or delete all but the most
recent?
 
I dont want to delete anything actually. I just want to create quer
where it gives me all the records where Field_1 are the same AN
Field_2 (only date part) are different. How do I go about comparin
the date part of Field_2
 
I dont want to delete anything actually. I just want to create query
where it gives me all the records where Field_1 are the same AND
Field_2 (only date part) are different. How do I go about comparing
the date part of Field_2?
Click to expand...

A Totals query would work here:

SELECT First([Field_1]), MAX([Field_2]) As LatestDate FROM yourtable;

If you have more fields that you haven't mentioned, save this query
and create a second query joining it to your table by Field_1 and
LatestDate.
 
evel79 said:
I dont want to delete anything actually. I just want to create query
where it gives me all the records where Field_1 are the same AND
Field_2 (only date part) are different. How do I go about comparing
the date part of Field_2?
Click to expand...
 
Back
Top