Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 9 of 9
  1. #1
    New to the CF scene
    Join Date
    Apr 2013
    Posts
    6
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Need help with Access Query

    Ok, it took me 20 minutes just to figure out how to start a darn post in here! If this is not the right location for this, please forgive me. I am going to bang my head on the wall real soon if I don't get my issue resolved. Please see my attachment for table sample. My table is named 201299 ALL RECORDS

    ID is my primary key column and it autonumbers the records. EEID is a person, and there are many records per one person. DR is a debit entry for that person, CR is a credit entry for that person.
    Is it possible to run one or both of the following queries?
    1) A query that returns to me matching records(matching means where the debit equals the credit for a person). If the query ran on attached table, it should return to me records (beginning with ID number): 1, 3, 5, and 6. "First come first match(if there are two matching CR values it should return to me the earlier date CR match").....
    2)A query that returns to me the opposite of what I defined in 1). A query that returns to me UNMATCHED records. If run on the above table, the query should return to me records 2, 4, and 7.

    THANKS IN ADVANCE!!!!!!!
    Attached Files Attached Files

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,567
    Thanks
    78
    Thanked 4,384 Times in 4,349 Posts
    What you are asking for is pretty simple...*EXCEPT* what happens if there are multiple matches?

    Let me add a couple of records to what you showed:
    Code:
    ID	EEID	DR	CR	Client	Date
    1	333	30		L00006	11/28/2011
    2	333	50		L00006	12/3/2012
    3	333		30	L00006	1/3/2013
    4	333	30		L00006	1/4/2013
    5	444	60		L00006	1/5/2013
    6	444		60	L00006	3/5/2012
    7	444	70		L00006	6/8/2012
    8       333          30    L00006    6/9/2012
    9       444  60            L00006    6/10/2012
    I saw you said you wanted to match the earliest matching pair. *THAT* is the part that is tough. Access isn't the smartest query engine in the world. I'll need to think on it a while.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,567
    Thanks
    78
    Thanked 4,384 Times in 4,349 Posts
    Actually, matching up the first pair (e.g., ID's 1 and 3) is not too tough. Matching up the second pair of the same values (e.g., ID's 4 and 8 in my post) gets much tougher.

    See, it's easy enough to describe any GIVEN match:
    Code:
    SELECT TOP 1 * FROM table
    WHERE CR = 30 AND [date] >= #1/3/2012#
    ORDER BY [date]
    But trying to do that with thousands of records... still thinking on it, but I admit to not seeing it yet.

    I think I do see a way to do it by combining a pair of SQL queries and then some ASP (or ASP.NET or whatever) code. Would that be acceptable?
    Last edited by Old Pedant; 04-01-2013 at 10:26 PM.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    kristenmanette (04-02-2013)

  • #4
    New to the CF scene
    Join Date
    Apr 2013
    Posts
    6
    Thanks
    2
    Thanked 0 Times in 0 Posts
    THank you so much for your effort so far! I would use code but I may need some help understanding where to put it. I have dabbled lightly with SQL but not ASP so that would be completely new to me.
    Let me just give you a quick background on this data-I am an accountant and I am trying to do a clearing account reconciliation in Access because at the moment we have no reconciliation software for it. I am really trying to do more with Access than it is meant to do
    I ultimately need to have a query that shows me all the records that do not match, so maybe we should focus our effort there only. (Perhaps it would be easier than the match query anyway). Using your data as an example, I would love to write a query that would return to me records 2, 7, & 9.
    I look forward to your response!

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,567
    Thanks
    78
    Thanked 4,384 Times in 4,349 Posts
    Non-match is, unfortunately, just as hard as match. Well...maybe not. Hmmm...If you don't really care *WHICH* entries matched, then all we need to do is find CR and DR that have no possible match. Hmmm...

    Yes, I think we can do that.

    Let me play with it...Ugh! Almost got there and then realized I wasn't detecting the case when there were, say, 2 more CR's of 30 than there are DR's of 30. I was only handling the case where there is only on mismatch. Double ugh. Not an easy problem.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,567
    Thanks
    78
    Thanked 4,384 Times in 4,349 Posts
    How many mismatches are you expecting to find? 20? 200? 2000? 20000?
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #7
    New to the CF scene
    Join Date
    Apr 2013
    Posts
    6
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Roughly 20,000 mismatches...I know...it's a lot!

  • #8
    New to the CF scene
    Join Date
    Apr 2013
    Posts
    6
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Let's just assume that I don't really care which $20 DR matches to $20 CR...let's see what ya got!

  • #9
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,567
    Thanks
    78
    Thanked 4,384 Times in 4,349 Posts
    Wish it were that simple. I can tell you HOW MANY $20 mismatches there are, and I can find the *LAST* such mismatch, but finding the next to last and others is a lot tougher.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    kristenmanette (04-03-2013)


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •