In both of those examples, we have 2 books and 1 bank, so we need a similar subquery to eliminate the book duplicates from the query. So I suggest something like this.
SELECT *
FROM qryCombined Q
WHERE Q.Q2.Transaction_ID=(SELECT min(q2.Transaction_ID) from qryCombined Q3 WHERE Q2.amount=Q.Q2.Amount AND Q2.Operation=Q.Q2.Operation AND Q2.transactionDate>=Q.Q1.transactiondate) AND
Q.Q1.Transaction_ID=(SELECT min(q1.Transaction_ID) from qryCombined Q3 WHERE Q1.amount=Q.Q1.Amount AND Q1.Operation=Q.Q1.Operation AND Q1.transactionDate<=Q.Q1.transactiondate)
Remember I'm writing this 'on the fly' with no means of testing. I am at clients later today so probably will not be able to respond further until tomorrow.
ok and your 'on the fly' was 'spot on'. Everything is working. For Tomorrow, Are we able to move to the next step of seeing the left data, and then using the same logic for 1 to many.
Assuming the last query is called qryMatched then this query will show the remaining records - you will need to check it to make sure that there are no records missed that should have been included
SELECT T.*
FROM (Combine_tbl T LEFT JOIN qryMatched MB ON T.Transaction_ID=MB.Q1.Transaction_ID) LEFT JOIN qryMatched MK ON T.Transaction_ID=MK.Q2.Transaction_ID
WHERE MB.Q1.Transaction_ID is Null and MK.Q2.Transaction_ID is Null
once this is done, we have the basics for doing the rest.
I apologize before we go further there was a duplicate in the last data. from this last code
SELECT *
FROM qryCombined Q
WHERE Q.Q2.Transaction_ID=(SELECT min(q2.Transaction_ID) from qryCombined Q3 WHERE Q2.amount=Q.Q2.Amount AND Q2.Operation=Q.Q2.Operation AND Q2.transactionDate>=Q.Q1.transactiondate) AND
Q.Q1.Transaction_ID=(SELECT min(q1.Transaction_ID) from qryCombined Q3 WHERE Q1.amount=Q.Q1.Amount AND Q1.Operation=Q.Q1.Operation AND Q1.transactionDate<=Q.Q1.transactiondate)
FYI this actually fixed the problem in Q1 and then created the exact same problem back in Q2. relating to -3200. Just need them both to operate like the q1 side
I think you will need to play around with this yourself - I've got a lot of work over the next few days, and at some point you need to understand what the code is doing
but first try this, change the bit in red
SELECT *
FROM qryCombined Q
WHERE Q.Q2.Transaction_ID=(SELECT min(q2.Transaction_ID) from qryCombined Q3 WHERE Q2.amount=Q.Q2.Amount AND Q2.Operation=Q.Q2.Operation AND Q2.transactionDate>=Q.Q1.transactiondate) AND
Q.Q1.Transaction_ID=(SELECT min(q1.Transaction_ID) from qryCombined Q3 WHERE Q1.amount=Q.Q1.Amount AND Q1.Operation=Q.Q1.Operation AND Q1.transactionDate<=Q.Q2.transactiondate)