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:
Please Login or Register  to view this content.
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