Page 1 of 4 1234 LastLast
Results 1 to 15 of 53
  1. #1
    GCLIFTON is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2016
    Posts
    84

    One to One


    Need help with a SQL that would: Per the Bank_Wo_Coin. [Operation] take the amounts in Bank_Wo_Coin.[Amount] and find the exactly opposite of it in the Book_WO_Coin.[Amount] where it sums to zero if you would add them together. So basically i am comparing one query table by Operation to another Query Table by Operation. This query will basically give me eveything that totals to zero one to one
    Attached Thumbnails Attached Thumbnails Bank_W0.JPG   BOOK_W0.JPG  

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    provide some examples of what you mean. I'm assuming it is not as simple as adding all transactions by operation and all you have to compare on is the sum of amount. If I have understood this, what is supposed to happen if you have multiple same amounts e.g.

    operation amount
    1............-10
    2............10
    3............10
    4............-10
    5............-10


    do you match 1 to 2, 3 or 4?
    do you match 4 to 2, 3 or 4?
    do you match 5 to 2, 3 or 4?

    perhaps you say, that the date of the negative trasaction must be before the date of the positive transaction - but what if two of the negative transactions are before the positive transaction?

    Assuming this does not matter and you just want to see possible matches try this (which because of the join, can only be viewed in the SQL window)

    SELECT *
    FROM Q1 INNER JOIN Q2 ON q1.sumofamount=q2.sumofamount*-1

    or use a Cartesian query which can be viewed in the query window

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

  3. #3
    GCLIFTON is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2016
    Posts
    84
    I apologize

    i am looking for 1 to 1 match having the same operation within a 3 day range of tranaction date, Remove them and place them in a query or table

    From your example if Transdate was added, it would be look like
    operation amount Transasdate
    1256............-10 1/3/17-------------bank
    1256............10 1/1/17------------Book
    34567............10 1/2/17-----------Book
    76892............-10 1/1/17----------Bank
    34567............-10 1/5/17-----------Book

    So 1256 Data will be place in a table or Query Both lines and all of its information
    The same for 34567 and place into that same table or query
    And 76892 will be left remaining.

    The operation has to be same and the date a 3 date range. Also just to say, The Book Data amounts are the Main amounts. Meaing the the sql will start to look at the book data to find in Bank data. All netting to Zero. I am only looking for 1 to 1 debit and credits that net to zero and then if you can include possible 1 to many that net to zero but first you would have to exclude the 1 to 1 first and then run the 1 to many because i cant use the same number twice. All of the results will return to one table or query having all the columns assocationed with it. A test to all of this would be the sum of the amount column will net zero.

  4. #4
    GCLIFTON is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2016
    Posts
    84
    Could you aslo include in the logic to run this on all data that does not have a Trans Code of 666 in either tables or BR-* in [Pending Activity]. So none of my return data should have a trans code of 666 or BR-*They are all excluded. So basically skip over those

  5. #5
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    The operation has to be same and the date a 3 date range
    is that bank within +- 3 days of book? or bank is within 3 days after book?

    With regards appending to another table, I will leave that to you - it is not clear whether you want the two records side by side or as two separate records. If the latter, you will need two append queries based on the below. Also not clear whether you want to append the individual amounts or just the totals, if the former use the below to identify the operations (i.e. instead of SELECT *, use SELECT B.Operation) and then link back to the individual operations in another query


    Assuming +-3 days and totals only, your query will look something like this

    Code:
    SELECT *
     FROM qryBook B INNER JOIN qryBank K ON B.operation=K.Operation AND K.TransDate BETWEEN B.TransDate-3 and B.TransDate+3 AND B.Amount=K.Amount
    or viewable in query design

    Code:
    SELECT *
     FROM qryBook B INNER JOIN qryBank K ON B.operation=K.Operation 
    WHERE K.TransDate BETWEEN B.TransDate-3 and B.TransDate+3 AND B.Amount=K.Amount

  6. #6
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    Could you aslo include in the logic to run this on all data that does not have a Trans Code of 666 in either tables or BR-* in [Pending Activity]. So none of my return data should have a trans code of 666 or BR-*They are all excluded. So basically skip over those
    you would exclude those in your original grouping queries

  7. #7
    GCLIFTON is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2016
    Posts
    84
    Bank within 3 Days after book date.


    Not sure on your last statement regarding side by side or separate records.
    but I would like in return to have the query show both lines that match together because in the original data they both have transaction IDs that i need for an upload. So i need to see the credit information and the debit information together. And what i mean by information all of the data in each column for each line. And what i was hoping is that i place this sql into a need query and all of this data will come thru in one query table

  8. #8
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    Not sure on your last statement regarding side by side or separate records
    .

    side by side is what you have

    Book.Operation..Book.sumAmount...Book.Trandate...B ank.Operation..Bank.sumAmount...Bank.Trandate

  9. #9
    GCLIFTON is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2016
    Posts
    84
    Ok could you help me as well. i am unable to insert the acutual names of the query in the sql correctly. Bank is [Bank_wo_Coin] Book is [Book_WO_COIN] int the following code [code]SELECT * FROM qryBook B INNER JOIN qryBank K ON B.operation=K.Operation AND K.TransDate BETWEEN B.TransDate-3 and B.TransDate+3 AND B.Amount=K.Amount

  10. #10
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    not sure why you are having an issue

    replace qryBook with Book_WO_COIN
    replace qryBank with Bank_wo_Coin

    so

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

    both of these are aliased as B and K respectively to make it easier to write the query

    you also said
    Bank within 3 Days after book date.
    so you also need to remove the -3 highlighted in red

  11. #11
    GCLIFTON is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2016
    Posts
    84
    Ok could you help me as well. i am unable to insert the acutual names of the query in the sql correctly. Bank is [Bank_wo_Coin] Book is [Book_WO_COIN] int the following code [code]SELECT * FROM qryBook B INNER JOIN qryBank K ON B.operation=K.Operation AND K.TransDate BETWEEN B.TransDate-3 and B.TransDate+3 AND B.Amount=K.Amount[CODE]

  12. #12
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    I've already responded

  13. #13
    GCLIFTON is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2016
    Posts
    84
    I apologize accidentally sent it twice but i apologize i am having trouble this is the error that i received.
    Attached Thumbnails Attached Thumbnails AERROR1.JPG  

  14. #14
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    OK, since I provided the exact code you required, you have failed to simply copy it correctly and have also ignored my last comment.

    Revisit my post and compare what you actually have with what I posted

  15. #15
    GCLIFTON is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2016
    Posts
    84
    Please forgive me. I am still getting an error after reading what you stated. Unless i am still miss understanding you.
    Attached Thumbnails Attached Thumbnails AERROR2.JPG  

Page 1 of 4 1234 LastLast
Please reply to this thread with any new information or opinions.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums