3 Querys combined into 1 main, one to many? (1 Viewer)

HI,

Sorry title not very informative. I should be able to solve the following but just can't get my head round. Would be fine if just 1 linked table but as I have 3 below struggling.

I have booking reservation system.
I am trying to get a list of clients that have NOT contacted me by one of three means since 160 days (or whatever)

I have the main booking table & 3 other related
1) Phone Calls,
2) Emails
3) Notes/dates of any alterations (I know they have been in contact if altered)

I have each table listing the last time they were in contact.
At the moment, lists those over 160 days (I could list those that have been in contact but this seems most likely way I can get to work)

So if I have had NO contact at all or not since 160 days ago, by any or all of the 3 method/tables above.

Tried so many variations but it either misses some out or includes those it should not.
If I have a relationship link with the shared ID I can't do 1 to many in both directions.

Sorry will get back to this as confused myself whilst trying to describe what circumstances & how it fails.
I thought I had this clear in my head but now not so sure,
I know I should not post half cocked but will update shortly as got to go.

Try this

Create a UNION query including data for all 3 tables giving records for all clients who have been in contact in specified number of days. Call it qryUnion.

NOTE you will need exactly the same number of fields in the same order for all 3 tables

Now do an unmatched query e.g using the wizard on your Client table and qryUnion.
This will list all clients who have not been in contact

Hi,
Many thanks for that, clever & looks interesting.

In all my years never used a union query so going to have to learn.

My only initial unskilled thoughts are the fields have to be the same, my 3 tables are very different apart from the ID, they are like lookup (they are not lookup tables) but similar in that nothing from the main table is duplicated in any of them.

I will try to see if a Union query allows all the fields necessary to filter & sort in the 3 tables but only SHOW the ID so they match and then included in the final unmatched main query.

Thanks again. I would never have thought of that, I always get there in the end but sure not always pretty or the most efficient.

Normally with union queries you just add 'dummy' fields where necessary so the structure of each part is identical.

However if you only need the ID field its easy.
Something like this

This should do the job... Paste this into the SQL View of a blank query, and then replace the 4 tbl_ table names with your actual table names.
Change "Date()-90" to whatever timeframe you want.
Change [CustName] to whatever field(s) you want returned.
Change Call_Date, Email_Date & Alter_Date to the actual field names of the dates.
Assuming ID is called the same thing in all 4 tables then no change is needed there. On the other hand, if for example, the ID in tblEmails is called "eID", and the date of the last email is called "Date Of Email", then change the middle subquery to
"UNION Select [eID] as [ID], [Date Of Email] as [ContactDate]".

Basically, the goal is to have all 3 tables in the UNION query returning the same 2 field names. I went with [ID] and [ContactDate]

See Allen Browne and Stackoverflow.

Many thanks to you both,

I managed to get all I wanted from Ridders, but Ashleedawg code looks really clean & nice

Got it working really well.

Many Thanks again.

You're welcome.

Ashleedawg's solution is in fact very similar to mine but he was kind enough to provide detailed code in case you or anyone else reading this needed it