First record of a one to many relationship

[johnas1977]

HI FOLKS

Access 2003 database-

I have a one to many relationship where I need to just show one record on the many side. The table on the many side has mutliple incident numbers that join to the incident number on the one side, however the fields on the many side will each have different values. This means I can't use a Totals query to do Max or First because it sees each record as a unique group.

What I need is to create a SQL query but I need some help.

Tables involved...

tblIncidents
IncidentNumber (Primary Key), Severity, OpenDate, CloseDate
2000001, 1, 01/03/2012, 02/03/2012
2000002, 2, 01/03/2012, 01/03/2012
2000003, 4, 03/03/2012, 03/03/2012

tblImpact
Incident_Number (PK), BIMImpacted (PK), Rating, Outage, PointsLoss
2000001, DepartmentA, High, 10, 2
2000001, DepartmentB, Medium, 10, 1
2000001, DepartmentC, Low, 10, 1
2000002, DepartmentA, High, 200, 4

If possible just want to show a record from tblImpact with the highest points loss, however there may be records with the same points loss, so just need one of these.

Regards,
John.

 

[Remou]

How about:

SELECT * FROM tblIncidents a
INNER JOIN (
   SELECT i.Incident_Number, 
     First(i.BIMImpacted) AS FirstOfBIMImpacted, 
     First(i.Rating) AS FirstOfRating, 
     First(i.Outage) AS FirstOfOutage, 
     Max(i.PointsLoss) AS MaxOfPointsLoss
   FROM tblImpact AS i
   GROUP BY i.Incident_Number) b
On a.IncidentNumber=b.Incident_Number

http://lessthandot.com

 

[johnas1977]

Sorry for the lateness in reply. I have been away over Easter.

This does work exactly as I want it to. Thanks Remou.