One to One

[GCLIFTON]

I have even change the transdate to transactiondate as it is stated in the query.

[GCLIFTON]

I have even changed the transdate to transactiondate

[Code]SELECT * FROM Book_WO_COIN INNER JOIN BANK_WO_COIN ON Book_WO_COIN.operation= BANK_WO_COIN.Operation AND BANK_WO_COIN.TransDate BETWEEN Book_WO_COIN.TransactionDate and Book_WO_COIN. TransactionDate +3 AND Book_WO_COIN.Amount= BANK_WO_COIN.Amount[code]

[aytee111]

(Mediating) You have spaces in weird places, for instance "Book_WO_COIN. TransactionDate". I am sure Access doesn't like them.

[GCLIFTON]

I agree and understand. I am going back and reviewing everythign and it is getting frustating. I took out the spaces and still have an error

[CODE]SELECT * FROM Book_WO_COIN INNER JOIN BANK_WO_COIN ON Book_WO_COIN.operation=BANK_WO_COIN.Operation AND BANK_WO_COIN.TransactionDate BETWEEN Book_WO_COIN.TransactionDate and Book_WO_COIN.TransactionDate+3 AND Book_WO_COIN.Amount=BANK_WO_COIN.Amount[CODE]

[GCLIFTON]

Should there be some brackets in the equation like [Bank_wo_Coin].amount. i am not sure just asking and tyring to figure out where i went wrong

[aytee111]

Why are you working in the SQL? If you did it all in query design view then Access would do all of that for you. Access puts brackets all over the place, I am not sure what is required however. But I would let Access do it for you.

[GCLIFTON]

Correct. I was just trying to make a suggestion. Which was way wrong. i have gone over this sql over and over and still cant seem to find why it is not working
Can any one or you help me with this one with this code and why it is erroring.
[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+3 AND Book_WO_COIN.Amount= Bank_wo_Coin.Amount
[CODE]

[aytee111]

It runs fine for me, without error. Are the data types correct?

[GCLIFTON]

Ok I looked at the data types in the table in which the query is pulling from. In the Bank Table the operation is a short text and in the book it is a Number. I attempted to change from short text to number and it gave me an error of data will be lost. In that table i have some operation number that are 15 characters long. How can change the types to make this work

[GCLIFTON]

Is there a way to alter this sql that the data is underneath each other besides side by side. So the book is on top and the bank is underneath

[CJ_London]

suggest try the other version

SELECT *
FROM Book_WO_COIN B INNER JOIN Bank_wo_Coin K ON B.operation=K.Operation
WHERE K.TransDate BETWEEN B.TransDate and B.TransDate+3 AND B.Amount=K.Amount

but still not sure what you are doing. I thought you wanted the totals to be matched which was where qryBank and qryBook came from

[GCLIFTON]

I am basically doing a bank reconciliation. In this reconciliation, the logic says

If the Operation Number on the Book side = the Operation Number on the Bank side, and the Bank date is +3 of the Book Date find every value (Amount) that nets to zero 1 for 1. in that range
Meaning (1) amount 25.78 on Book Side Nets to (1)-25.78 on the Bank Side that nets to zero. if it is the range of +3 days
So in theory the sql is looking at each amount in a operation group and finding the offset amount that nets it to zero in the date range requirements Attached is a example of how the data should look After the logic is ran. I will then export the data out of Access into excel in this format.

[CJ_London]

I understand what you are trying to do. The problem is with your data and your explanation of what you actually have.

For example, you want to match on the operation number OK, understood - that is in your latest data 1840 for example. But you also want to match on the transaction date+3days - previous example provided says all transaction dates are the same for a given operation - but your latest example has multiple transaction dates within the 1840 operation - so which one are you trying to match on+3 days? 19th? 20th? 23rd? 27th? The earliest one? the latest one?

We are now also seeing one table, not two although that is less of an issue - it is potentially easier if all in one table.

We are now up to post 28 and it is still not clear what you actually want. Given the data you have just provided, provide an example of the outcome you actually require from the data you have shown and explain how you have arrived at it. And be accurate, don't abbreviate transactiondate to trandate/transdate and other variations. Provide the table name you are actually using and the field names you are actually using. At the moment the best I can see you achieving is matching single transactions

[GCLIFTON]

Ok i have no problem with everything being in one table as long as long i can label Bank data and Book Data some kind of way. Do you have any suggestions on the best way to do this

So that is true on the dates. They do not suppose to exceed the 3 days but as you can see they do. So starting from the beginning i may change the date range from 3 to 6 until we get procedures in place. I still want all of the data to be visible like the example above in excel. Every one that match 1 to 1 in the range of dates for now +6. So attached are my two query tables. I would like to find all 1 to 1 match per the conditions.

Now since all of the data is in one place, is there a way to have the sql go back into the data and find 1 to many excluding the 1 to 1 matches that was run first. This is the ultimate goal. I dont know the best way of doing this maybe labeling the 1 for 1s with a 1 in a new column the created another query excluding them and run a new sql for one to many with the same date range

Query Table Names: [Bank_WO_COIN] AND [BOOK_WO_COIN]
Fields in Queries that do not change: OPERATION AMOUNT TRANSACTIONDATE

Note i want to also be able to see that the bank clear with the book data at all times. Hence the reason why i want to keep a label of some sort of bank and book

[CJ_London]

I am confused by your requirement. You say you are happy for data to be in one table, then provide 2. I don't see an example of the output based on operation 1840.