Hi June
Sorry I've been offline almost all day.
You were right to question my comments. It is possible to do this with a union query though not the way I suggested!
The following explains how I did this using several intermediary queries.
However all of those could be omitted if preferred with the union doing all the work
First of all I created qryT2 as follows adding an extra field to get the last character in the OilTitle field
Code:
SELECT T2.*, Right([OilTitle],1) AS OilTNo FROM T2;
Similarly qryT3:
Code:
SELECT T3.*, Right(BrakeTitle,1) AS BrakeTNo FROM T3;
and qryT4
Code:
SELECT T4.*, Right([EngineTitle],1) AS EngineTNo FROM T4;
Then I created qryMatchedRecords as follows:
Code:
SELECT T1.Deal_ID, T1.DealDate, qryT2.OilTitle, qryT3.BrakeTitle, qryT4.EngineTitle
FROM ((T1 INNER JOIN qryT2 ON T1.Deal_ID = qryT2.Deal_ID) INNER JOIN qryT4 ON (qryT2.OilTNo = qryT4.EngineTNo) AND (qryT2.Deal_ID = qryT4.Deal_ID))
INNER JOIN qryT3 ON (qryT2.OilTNo = qryT3.BrakeTNo) AND (qryT2.Deal_ID = qryT3.Deal_ID)
This gives the first 2 records:
Deal_ID |
DealDate |
OilTitle |
BrakeTitle |
EngineTitle |
3 |
03/08/2020 |
Oil1 |
Brake1 |
Engine1 |
3 |
03/08/2020 |
Oil2 |
Brake2 |
Engine2 |
Next I created qryUnmatchedRecords to get the other 2 records
Code:
SELECT DISTINCT T1.Deal_ID, T1.DealDate, qryT2.OilTitle, qryT3.BrakeTitle, qryT4.EngineTitle
FROM ((T1 INNER JOIN qryT4 ON T1.Deal_ID = qryT4.Deal_ID) LEFT JOIN qryT2 ON (qryT4.EngineTNo = qryT2.OilTNo) AND (qryT4.Deal_ID = qryT2.Deal_ID))
LEFT JOIN qryT3 ON (qryT4.EngineTNo = qryT3.BrakeTNo) AND (qryT4.Deal_ID = qryT3.Deal_ID)
WHERE (((qryT3.BrakeTNo) Is Null))
ORDER BY qryT4.EngineTitle;
Deal_ID |
DealDate |
OilTitle |
BrakeTitle |
EngineTitle |
3 |
03/08/2020 |
Oil3 |
|
Engine3 |
3 |
03/08/2020 |
|
|
Engine4 |
Finally union those two queries:
Code:
SELECT T1.Deal_ID, T1.DealDate, qryT2.OilTitle, qryT3.BrakeTitle, qryT4.EngineTitle
FROM ((T1 INNER JOIN qryT2 ON T1.Deal_ID = qryT2.Deal_ID) INNER JOIN qryT4 ON (qryT2.OilTNo = qryT4.EngineTNo) AND (qryT2.Deal_ID = qryT4.Deal_ID))
INNER JOIN qryT3 ON (qryT2.OilTNo = qryT3.BrakeTNo) AND (qryT2.Deal_ID = qryT3.Deal_ID)
ORDER BY qryT4.EngineTitle;
UNION
SELECT DISTINCT T1.Deal_ID, T1.DealDate, qryT2.OilTitle, qryT3.BrakeTitle, qryT4.EngineTitle
FROM ((T1 INNER JOIN qryT4 ON T1.Deal_ID = qryT4.Deal_ID) LEFT JOIN qryT2 ON (qryT4.Deal_ID = qryT2.Deal_ID) AND (qryT4.EngineTNo = qryT2.OilTNo)) LEFT JOIN qryT3 ON (qryT4.Deal_ID = qryT3.Deal_ID) AND (qryT4.EngineTNo = qryT3.BrakeTNo)
WHERE (((qryT3.BrakeTNo) Is Null))
ORDER BY qryT4.EngineTitle;
Deal_ID |
DealDate |
OilTitle |
BrakeTitle |
EngineTitle |
3 |
03/08/2020 |
Oil1 |
Brake1 |
Engine1 |
3 |
03/08/2020 |
Oil2 |
Brake2 |
Engine2 |
3 |
03/08/2020 |
Oil3 |
|
Engine3 |
3 |
03/08/2020 |
|
|
Engine4 |
See attached database.
However, whilst it does what the OP wanted, I'm not suggesting this is a good approach for the OP to use.
As previously stated the tables should be redesigned