Isolate oldest record

[ohmbru]

I need to isolate the oldest record in a group of records. Details follow.

Fields involved are:

CaseID
Date
Time

The CaseID will appear many times in the table. It identifies specific work required for a client. Each time an event occurs on the case a new record is created with the current date and time. The date and time are separate fields.

I realize this is not the best way to design a database, but the tables are proprietary tables and designed for use with imaging software, then coverted to tables to facilitate reporting. Therefore, I am stuck with what I have as far as table design goes.

My mission is to extract the first event that happened to a case, based on the date and time.

Example data:

CaseID Date Time
*0001 09/01/01 7:00 AM
0001 09/01/01 7:25 AM
0001 09/03/01 10:22AM
*0587 09/01/01 9:13 AM
0587 09/02/01 1:57 PM

I want my query to return only the astericked records.

Thanks for any help,

Brian

 

[Databaseguy]

Use a group by query. Include the case number, and concoctenate the date and time. In the total field of the new, concoctenated expression, choose "Min"

Your query will look like this
Case ID Expr1
0001 09/01/091 7:00AM
0587 09/01/01 9:13AM
Tyrone Lumley
augerinn@gte.net

http://server47.hypermart.net/databaseguy

 

[ohmbru]

Thanks! (that was fast)

I must have concoctenated the fields incorrectly. At first it looked like it was working perfectly, then I saw a record that was not the oldest (the latest, in fact).

I used [Date]&[Time]

 

[ohmbru]

After further review...

The query does not seem to recognize the date. It sees 10/2/00 as less than 8/2/00. It did this for 10th, 11th, and 12th months.

 

[tempclerk]

Looks like your dates are being sorted as strings. Maybe use CDate([Date]&[Time])?

 

[Databaseguy]

Yes. They have to be dates. Stings sort like this:

1
10
100
2
20
21
22
200
2000
23

Etc..... Tyrone Lumley
augerinn@gte.net

http://server47.hypermart.net/databaseguy