Avatar of pjelias
pjelias 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
Microsoft SQL ServerDelphi

Avatar of undefined
Last Comment
pjelias

8/22/2022 - Mon
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

ASKER
pjelias

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

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
pjelias

Thanks heaps, had to modify for my actual Table structures, but managed to get it working.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck