How can I get the earliest date with associated fields?

[mohebk]

Hi,
I have a table that looks like this:

Name Number Date Event1 Event2
AAAA 455 1/1/01 A A
AAAA 455 3/2/02 B A
AAAA 455 8/3/05 C J
BBBB 123 3/5/02 A G
BBBB 123 8/2/04 K N

I am looking to write a query to get the earliest record for each name (one record per name) based on the date field with the associated Event1 and Event2.

You help is always appreciated.
Thanks

Mo

 

[fneily]

The very first step in creating tables is to normalize them. This is extremely important. Your table is not normalized. You have duplicate data of non-primary keys and repeating column headings - Event1, Event2 (drop the number you have Event, Event,etc.)
See:
Fundamentals of Relational Database Design

http://r937.com/relational.html

You should have at least three tables based on your example:
tblPerson
PersonID Primary Key
FirstName
LastName
Number

tblEvent
EventID Primary Key
Description

tblPerEvt
PEID Primary Key
PersonID Foreign Key
EventID Foreign Key
DateOfEvent

Using the tblPerEvt, you would just create a query grouping on PersonID, EventID and Min the DateOfEvent in the QBE pane.

As you have it now, you'll need to do a Union query to normalize your table. If you search the Access Query forum, you'll see how to do a Union query if you don't want to correct the table.

 

[mohebk]

The data is extracted from a legacy system that we can't really change.

Mo

 

[Remou]

How about:

[tt]SELECT t1.Name,t1.Number,t1.Date,t1.Event1,t1.Event2
FROM Table t1
INNER JOIN (SELECT Name, Min(Date)
FROM Table
GROUP BY Name) t2
ON t1.Name=t2.Name
AND t1.Date=t2.Date[/tt]

 

[lespaul]

I think you need an alias for the field:

SELECT t1.[Name],t1.Number,t1.[Date],t1.Event1,t1.Event2
FROM Table t1
INNER JOIN (SELECT [Name], Min(Date) As FirstDate 
            FROM Table
            GROUP BY Name) t2
ON t1.[Name]=t2.[Name] 
AND t1.[Date]=t2.FirstDate

and Date and I think Name are reserve words and will need to be in [brackets].

Leslie

Have you met Hardy Heron?

 

[Remou]

Good point on the alias, thanks. Let's hope that the names of the fields are not real.

 

[mohebk]

Faaaaaaaaaantastic. Thanks a lot for alias tips.

Mo