Trying to control results of a JOIN

[WorkingVBA]

OK, this is a tricky one, but I am hoping someone else might have seen something like this. I am joining two tables but I need to be able to control (turn on or off) certain records returned by the query. I will try to illustrate:

Table Test1:
K FK Display
1 21 Always
2 22 Always
3 31 Only when Joined
4 32 Always
5 33 Always
6 40 Only when Joined
7 51 Always
8 52 Always

Table Test2
K Data
21 Yes
32 Yes
33 Yes
40 Yes
51 Yes

A simple Join:
SELECT Test1.Key, Test1.FK, Test1.Display, Test2.Data
FROM Test1 LEFT JOIN Test2 ON Test1.FK = Test2.Key;

The results
K FK Display Data
1 21 Always Yes
2 22 Always Null
3 31 Only when Joined Null (This one should not be there)
4 32 Always Yes
5 33 Always Yes
6 40 Only when Joined Yes
7 51 Always Yes
8 52 Always Null

This is the desired result:
K FK Display Data
1 21 Always Yes
2 22 Always Null
4 32 Always Yes
5 33 Always Yes
6 40 Only when Joined Yes
7 51 Always Yes
8 52 Always Null

I have tried a number of things. The actual table Test1 has 27 records of which 19 record should always be displayed in the results regardless of the join, and table Test2 will contain data for about 340 clients each client having any number of records (about 12,000 records in total) that will join to the records in table Test1. It just is that some of those records may be missing.

(The join gets even tricky-er when you try to filter out a single client, since the records that are normally shown through the left join with a single client may now be filtered out because they appeared in the results for another client that was not included in the query results)​

The data in Test2 is AS-IS so making changes there is not possible. Table Test1 is supposed to "drive" the results and can be modified.

This is an interesting and challenging problem. I will post the results if I find them myself. At this point I am not sure how to go about solving it and hope I might get some pointers, suggestions and/or ideas from the community.

Thanks

 

[plog]

You need to create a calculated field to determine if the record should be shown, then filter underneath that criteria to show just those that meet the criteria. This SQL should accomplish what you want:

SELECT Test1.K, Test1.FK, Test1.Display, Test2.Data
FROM Test1 LEFT JOIN Test2 ON Test1.FK = Test2.K
WHERE (((IIf([Display]="Only when Joined" And IsNull([Data]),0,1))=1));

 

[WorkingVBA]

You need to create a calculated field to determine if the record should be shown, then filter underneath that criteria to show just those that meet the criteria. This SQL should accomplish what you want:

SELECT Test1.K, Test1.FK, Test1.Display, Test2.Data
FROM Test1 LEFT JOIN Test2 ON Test1.FK = Test2.K
WHERE (((IIf([Display]="Only when Joined" And IsNull([Data]),0,1))=1));

Thanks Plog for your suggestion. I tried it out thought I modified the query to include a client field to simulate the multiple clients aspect of the problem. I ended up creating tables Test1 and Test 2 in he database for testing and changed the field K to Key. Here is the modified SQL statement:

SELECT Test1.FK, Test2.Client, Test2.Data, Test1.Display
FROM Test1 LEFT JOIN Test2 ON Test1.FK = Test2.Key
WHERE (((IIf([Display]="Only when Joined" And IsNull([Data]),0,1))=1))
ORDER BY Test2.Client, Test1.FK;

and this is the result:

FK Client Data Display
52 Always
21 Bill Yes Always
32 Bill Yes Always
33 Bill Yes Always
40 Bill Yes Only When Joined
51 Bill Yes Always
31 Bob Yes Only When Joined
32 Bob Yes Always
33 Bob Yes Always
40 Bob Yes Only When Joined
51 Bob Yes Always
21 John Yes Always
22 John Yes Always
51 John Yes Always
​

Where I need it to look like this:

FK Client Data Display
21 Bill Yes Always
22 -Bill Null Always
32 Bill Yes Always
33 Bill Yes Always
40 Bill Yes Only When Joined
51 Bill Yes Always
52 -Bill Null Always
21 -Bob Null Always
22 -Bob Null Always
31 Bob Yes Only When Joined
32 Bob Yes Always
33 Bob Yes Always
40 Bob Yes Only When Joined
51 Bob Yes Always
52 -Bob Null Always
21 John Yes Always
22 John Yes Always
32 -John Null Always
33 -John Null Always
51 John Yes Always
52 -John Null Always
​

Where the names with the dashes would be null since the records don't exist and "Null" in the "Data" field indicates null values as returned by the query. I'd have to find a way to add the names, which I am not sure is possible.

Because I am not sure that this will work I am exploring alternate possibilities. One such possibility involves using VBA with the intent of populating a separate temporary source table with the data the way I need it before running the report. But is there is a better way to do this I would enjoy hearing about it.

 

[nanscombe]

I think the attached database will give you what you want.

I have split the task into three parts:

1) qry001_Always

Shows all data in Test1 where Display is "Always" and any associaed data in Test2

SELECT Test1.K, Test1.FK, Test1.Display, Test2.Data
FROM Test1 LEFT JOIN Test2 ON Test1.FK = Test2.K
WHERE (((Test1.Display)="Always"));

2) qry002_OnlyWhenJoined

Shows only data in Test1 where Display is not "Always" and only where there is matching data in Test2

SELECT Test1.K, Test1.FK, Test1.Display, Test2.Data
FROM Test2 INNER JOIN Test1 ON Test2.K = Test1.FK
WHERE (((Test1.Display)<>"Always"));

3) qry003_DesiredResult

Combines the results of qry001_Always and qry002_OnlyWhenJoined

SELECT Q1.*
FROM qry001_Always AS Q1
UNION SELECT Q2.*
FROM qry002_OnlyWhenJoined AS Q2;

K	FK	Display			Data
1	21	Always			Yes
2	22	Always		
4	32	Always			Yes
5	33	Always			Yes
6	40	Only when Joined	Yes
7	51	Always			Yes
8	52	Always

 

[WorkingVBA]

Thanks nanscombe,

your suggestion works great with the data from the original example. But it looks like I need to know the client for each block of data. This is what I got as the results:

Key FK Client Data Display
8 52 Null Null Always
1 21 Bill Yes Always
4 32 Bill Yes Always
5 33 Bill Yes Always
6 40 Bill Yes Only When Joined
7 51 Bill Yes Always
3 31 Bob Yes Only When Joined
4 32 Bob Yes Always
5 33 Bob Yes Always
6 40 Bob Yes Only When Joined
7 51 Bob Yes Always
1 21 John Yes Always
2 22 John Yes Always
7 51 John Yes Always​

There is still a lot of data missing. I appreciate your help though.

 

[plog]

I modified the query to include a client field to simulate the multiple clients aspect of the problem

You know, if you included all the specifications in your initial post, I could have found a solution for your exact issue and not the simplified one you posted. Piecemealing out various elements of the problem helps no one.

 

[WorkingVBA]

Duly noted. My apologies.

 

[WorkingVBA]

I ended up resolving this in a completely different way. In essence I am running 3 INSERT queries, to insert data into a temporary table, for each client independently:
- First all records that match between the two tables (Join)
- Second all the records in Test 2 that are not found in Test1 for that particular client where the display variable is yes, and finally
- any records in Test1 that were missed in the first Join. I tried doing a Left Join in query 1, which would have included the missing records, but there was some problem with the result. The temporary table is the data source for the report.

I'll admit that this is not the most elegant solution, but it works, and though the processing is not super fast, it isn't super long either. We won't be processing 1000's of records so this won't be a problem.

Thanks for your help, I'll close this discussion for now, but if you have any comments or suggestions I'll still look at them and take them under advisement for educational purposes.