One to One

[GCLIFTON]

Attached are the 2 queries in 1 union Query having an extra column label saying Book and Bank see attachment I have also had this union query sent to a Table name: Combine_tbl



I would like (for example) to take all the one for one data that automatically nets to zero for all, but for this example, 1840 that is highlighted, to be removed out of this table to a [NET_TO_ZERO] table or query so can i create a report showing all that nets to zero 1 to 1. This output should look exactly like the combine_tbl format but only showing all that nets to zero 1 to 1
NEXT
once the above data has been pull out, the 80.00 and 2 (-40.00) should be the only things left. They should go to another query or table that is call [Outstanding] I will then take the [outstanding] query or table and run the logic for a 1 to many and combine all that nets to zero from that run to the same tbl or qry [NET to Zero]. If there is anything still left it shoudl stay in the outstanding Tbl or qry

Same logic: if operation are the same, for each book item, find one to one offset within +5 data range of the book data---- Then after 1 to 1 are found and moved: if operation are the same, for each book item find one to many offset within +5 data range of the book data and move.

[CJ_London]

we are not moving forward, my original post provided some sample code

SELECT *
FROM Q1, Q2
WHERE q1.sumofamount=q2.sumofamount*-1

after many toings and froings this should now be modified to

SELECT *
FROM Combine_tbl Q1 inner Combine_tbl Q2 on Q1.operation=Q2.operation
WHERE Q2.trandate between Q1.trandate and Q1.trandate+5 and q1.amount=q2.amount*-1


which is the basis of what you want, but you keep adding to the requirements, providing further twists to issues with the data without resolving the basis.

get this working first. Once that is done, we can look at the options for the rest of it

for example

SELECT *
FROM combine_tbl T LEFT JOIN (SELECT *
FROM Combine_tbl Q1 inner Combine_tbl Q2 on Q1.operation=Q2.operation
WHERE Q2.trandate between Q1.trandate and Q1.trandate+5 and q1.amount=q2.amount*-1) Q3 ON T.Transaction=Q3.Transaction
WHERE Q3.transaction is null

should provide this the 80.00 and 2 (-40.00) should be the only things left

We cannot proceed further until you have confirmed the above query is working as you require

[GCLIFTON]

Before i start does

Q1=Combine_tbl
AND
Q2=Combine_tbl

Because the code works with

q1= Book_wo_coin
and
Q2= Bank_wo_coin

[GCLIFTON]

So i got this sql to work with the 2 Qry table. I was unable to get it to run from the Table:Combine_tbl
[CODE][
SELECT *
FROM Book_WO_COIN INNER JOIN Bank_wo_Coin ON Book_WO_COIN.operation = Bank_wo_Coin.Operation
WHERE (((Bank_wo_Coin.TransactionDate) Between [Book_WO_COIN].[TransactionDate] And [Book_WO_COIN].[TransactionDate]+5) AND ((Book_WO_COIN.Amount)=[Bank_wo_Coin].[Amount]*-1));][CODE]

I attempted to change all book_coin and bank_coin to Combine_tbl. it would not work.

[CJ_London]

I was unable to get it to run from the Table:Combine_tbl

it's because you keep removing the aliases. you use aliases as a requirement to differentiate when a table is used more than once in a query and otherwise as a convenience to make a query easier to read.

So the query is working, now you need to check what it is returning is correct. When you have done that, we can move on to the rest of it.

Incidentally, if your tables have lots of records, it is advisable to index the fields you are using in the join and criteria

[GCLIFTON]

So i went thru the fields in the table and change index to Yes (Duplicates ok) for each Field. And then open a new query design and copy and paste:
SELECT *
FROM Combine_tbl Q1 inner Combine_tbl Q2 on Q1.operation=Q2.operation
WHERE Q2.trandate between Q1.trandate and Q1.trandate+5 and q1.amount=q2.amount*-1

Still got an error.
Here are the fields being changed. Any other suggest

[CJ_London]

Combine_tbl Q1 inner Combine_tbl Q2 -notice something missing? It was my fault, but despite your 'advanced beginner' status, you are struggling to understand basic sql syntax to correct it yourself. The query would have generated an error which explains what and where the issue is.

Please, please, please stop jumping around. You agreed your query in post #34 was working, so lets move forward from there. Is the query generating the correct results?

[GCLIFTON]

My apologies Inner Join is what was missing. I need to pay more attention and get back on the swing of things.

Data is coming thru with the Combine_tbl sql but there are duplicates where there shouldn't be i have a couple highlighted. A spot check of the transactions ID is a way that i can check. Each line has its own special id if i can see twice then i know it is a duplicate

and it is not related to the index duplicate

Here is the sql:SELECT *FROM Combine_tbl AS Q1 INNER JOIN Combine_tbl AS Q2 ON Q1.operation=Q2.operation
WHERE Q2.transactiondate between Q1.transactiondate and Q1.transactiondate+5 and q1.amount=q2.amount*-1;

In the attachment you can see there is only 1 entry but in the 1_to_1_Comb you can see it came thru twice

[GCLIFTON]

And we can we eliminated the side by side and have the data look the Combin_tbl. I know we spoke about it earlier

[GCLIFTON]

Dont worry about the last statement when i create the report it will be fine.

[CJ_London]

from the example you provided it looks like it is because bank and book occurred on the same day. check the other duplicates and see if this is the pattern. It also looks like you can check for duplicates where Q1.Amount is negative -f it is, you know to look for a duplicate.

If it is, you need to add a bit more to the criteria

...AND Q1.Amount>0

This assumes that book amounts are always positive and bank amounts are always negative. If this is not the case then another identifier is required to identify what is a book transaction.

Thinking about it, another time when you might get incorrect data is if you have 2 book amounts for the same value and one negative bank amount for the same amount within a 5 day period of both book amounts e.g.-

book 500 1/1/2017
book 500 1/2/2017
bank 500 1/3/2017

both books will be matched to the one bank.

You will be able to identify these (or at least it might be happening) but taking another copy of the query and changing ....and q1.amount=q2.amount*-1; to ....and q1.amount=q2.amount and q1.Transaction<>q2.Transaction;

if occurring or you think it could happen if this is not a one off process we will need to modify the query further

[GCLIFTON]

sO 8559 with the amounts 400 was solved with this SQL. below. I provided a shot of that. But like you said we still have the same date scenario still pops out How can we alter that



SELECT *
FROM Combine_tbl AS Q1 INNER JOIN Combine_tbl AS Q2 ON Q1.operation = Q2.operation
WHERE (((Q2.transactiondate) Between [Q1].[transactiondate] And [Q1].[transactiondate]+5) AND ((Q1.amount)=[q2].[amount]*-1AND Q1.TRANSACTION_ID<>Q2.TRANSACTION_ID AND Q1.AMOUNT>0));

[CJ_London]

with regards 5544 that is the scenario I was referring to, although in this case you have multiple books and banks with the same value both within 5 days.

Looking at the book amount of 3200 there are two transactions one dated 12/5 and the other 12/8, but you have 3 bank transactions, 1 dated 12/5 and the other 2 12/8, so one of those should not be matched anyway.

you could reduce the time period - say to 2 days rather than 5. But even reducing to just the same day won't work for 5544 for 3200 amount because you would still have one books and two banks which would match on the same day 'twice' so still a duplicate

Assuming you have called this qryCombined

SELECT *
FROM Combine_tbl AS Q1 INNER JOIN Combine_tbl AS Q2 ON Q1.operation = Q2.operation
WHERE Q2.transactiondate Between [Q1].[transactiondate] And [Q1].[transactiondate]+5 AND Q1.amount=[q2].[amount]*-1 AND Q1.TRANSACTION_ID<>Q2.TRANSACTION_ID AND Q1.AMOUNT>0

Not sure if this will work, but try it

Assuming you have called the query qryCombined

Create a new query based on it

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)

this should see transaction 3446894 matched to 3668640 and 3528457 matched to 34358760 and lever bank transaction 3668641 unmatched

As before you will need to check the dataset for any other duplicates/mismatches

[GCLIFTON]

i think we are almost there. still have duplicate But not as many. I also change the date range to +3. just to see what happens. But here are some example of the duplicate data highlighted. It seems as if the the duplicates are happening under q2. data

[GCLIFTON]

And for the record the follow happen the way you said for

this should see transaction 3446894 matched to 3668640 and 3528457 matched to 34358760 and lever bank transaction 3668641 unmatched