asked on

Help with DELPHI and an ACCESS Query

I am using Delphi with an Access Backend.

I basically want to run a Query which returns a single record from a master/child relationship.

For example

MASTER_TBL
Field : STAFFID
Field: INJURY_ID
Field: STAFF NAME

CHILD_TBL
Field: STAFFID
Field: INJURY_ID
Field: UniqueID
Field: DateInjury

The above is NOT the exact Table Structures, but enough fields for explaining.

ONE Master Record to Multiple Child Records.
A Staff may have MANY Child Records with or without a DateInjury, a UniqueID is created for each incident.

What I Need to Do, is return the following result

ALL MASTER records even if there is NO match in the CHILD, AND where there is a MATCH, return A single CHILD record where the UNIQUE_ID is the MAXIMUM value.

ie. MASTER_TBL Data
Staff ID Injury ID Staff Name
10 1 J.Smith
11 2 B.Brown
12 3 C.White

CHILD_TBL Data
Staff ID Injury ID Unique ID DateInjury
10 1 1
10 1 2 1/1/2005
10 1 3 3/1/2005
11 2 4
11 2 5 1/2/2005

EXPECTED RESULT
Staff ID Injury ID Staff Name UniqueID DateInjury
10 1 J.Smith 3 3/1/2005
11 2 B.Brown 5 1/2/2005
12 3 C.White

Appreciate any help
Regards
PJE

Thomasian

hi PJE,

Try

SELECT M.*, C.*
FROM 
  (MASTER_TBL M LEFT JOIN
  (SELECT StaffID,MAX(UniqueID) As MaxID
     FROM CHILD_TBL
     GROUP BY StaffID
  ) As S ON M.StaffID=S.StaffID) LEFT JOIN
  CHILD_TBL C ON M.StaffID=C.StaffID AND M.MaxID=C.UniqueID

Open in new window

pjelias

ASKER

Am trying to understand this, and apply to my situation.

I have MORE Tables and fields linking to the database than my initial example.

I get an error when I try and modify the Query to suit my needs, and then Access Crashes ????

The last part of this Query -> AND M.MaxID=C.UniqueID
Should M.MaxID be S.MaxID ?

ASKER CERTIFIED SOLUTION

Thomasian

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

pjelias

ASKER

Thanks heaps, had to modify for my actual Table structures, but managed to get it working.