troubleshooting Question

Help with DELPHI and an ACCESS Query

Avatar of pjelias
pjelias asked on
Microsoft SQL ServerDelphi
4 Comments1 Solution267 ViewsLast Modified:
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
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 4 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros