Page 4 of 4 FirstFirst 1234
Results 46 to 53 of 53
  1. #46
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397

    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.

  2. #47
    GCLIFTON is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2016
    Posts
    84
    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.

  3. #48
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    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.

  4. #49
    GCLIFTON is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2016
    Posts
    84
    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
    Attached Thumbnails Attached Thumbnails 123.JPG  
    Last edited by GCLIFTON; 01-24-2017 at 05:58 AM.

  5. #50
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I need to be able to see the full data for the row to be able to work out why that has appeared

  6. #51
    GCLIFTON is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2016
    Posts
    84
    See the attachment and i havent changed the name of this qry yet to qrymatched
    Attached Thumbnails Attached Thumbnails 15.jpg  

  7. #52
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    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)

  8. #53
    GCLIFTON is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2016
    Posts
    84

    Thumbs up

    Ok and agree. The q2 changed worked. And i will be applying the other qry and working on that. You've been a great help

Page 4 of 4 FirstFirst 1234
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