Hi All,
I have 3 tables that hold different stages of a client journey through health. The query attempts to link a service (tests) provided to the correct point of the client journey.
I am aiming for a best fit where the Date/Time of the service falls between the Date/Times of the tables, and only displays the best fit. (there may be some overlap, and am ok with seeing these).

My query works, but my problem is that as it looks at all 3 tables, it also returns all the options from all the tables. I get multiple returns from each table, as i have no idea on how to define/refine the query to return only the relevant ones.

I had a look at IIF, and came away more confused. Maybe i should use NULL? I even thought about doing each table separately, but i would still have to amalgamate the outputs.

the query is below:
SELECT DISTINCT [Blood Products Jul-Dec 2018].Hospital, [Blood Products Jul-Dec 2018].PatientNumber, [Blood Products Jul-Dec 2018].ServicingDepartment, [Blood Products Jul-Dec 2018].StartDateTime, ED.Hospital, ED.StartDateTime, ED.EndDateTime, ED.EncounterNumber, tbl_ppm_Encounter_V23_2.Hospital, tbl_ppm_Encounter_V23_2.StartDateTime, tbl_ppm_Encounter_V23_2.EndDateTime, tbl_ppm_Encounter_V23_2.EncounterNumber, EncounterNAP.Hospital, EncounterNAP.StartDateTime, EncounterNAP.EndDateTime, EncounterNAP.EncounterNumber
FROM (([Blood Products Jul-Dec 2018] INNER JOIN EncounterNAP ON [Blood Products Jul-Dec 2018].[Extra:MRN] = EncounterNAP.[Extra MRN]) INNER JOIN tbl_ppm_Encounter_V23_2 ON [Blood Products Jul-Dec 2018].[Extra:MRN] = tbl_ppm_Encounter_V23_2.[Extra:MRN]) INNER JOIN ED ON [Blood Products Jul-Dec 2018].[Extra:MRN] = ED.[Extra:MRN]
WHERE (([Blood Products Jul-Dec 2018].StartDateTime) Between [ED].[StartDateTime] And [ED].[EndDateTime])) OR ((([Blood Products Jul-Dec 2018].StartDateTime) Between [tbl_ppm_Encounter_V23_2].[StartDateTime] And [tbl_ppm_Encounter_V23_2].[EndDateTime])) OR ((([Blood Products Jul-Dec 2018].StartDateTime) Between [EncounterNAP].[StartDateTime] And [EncounterNAP].[EndDateTime]));
If i can get this to work, i will scale up to include more services. Sorry, but i can't attach the source files.

if there is a relevant topic, i'm happy to be pointed in the right direction.

many thanks
Andrew