Need help writing query-I’m stumped!

45 pts.
Tags:
Microsoft Access 2007
Microsoft Access query
I have a table that looks like this: ID EEID DR CR 1 333 30 2 333 50 3 333 30 4 333 30 5 444 60 6 444 60 7 444 70 ID is my primary key column and it auto numbers 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 the above table, it should return to me records (beginning with ID number): 1, 3, 5, and 6. May get tricky because there can be multiple same-dollar value transactions per person-for example, person 333 has a couple $30 debits. The query should "know" that record 1 matches to 3 and it shouldn't match 4 to 3. "First come first 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.

Software/Hardware used:
Windows XP

Answer Wiki

Thanks. We'll let you know when a new response is added.

Thanks for your input. You are 100% correct that some of the relationships will need to be connected by hand because we do have instances where a debit is cleared by two credits. 

Discuss This Question: 19  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • philpl1jb
    ..
    If you join the file to itself 
    something like this ..
    
    Select a.id, a.eeid, a.dr, b.id, b.cr
    from myfile a join myfile b
    on a.eeid = b.eeid
    and a.dr = b.cr
    order by a.eei, a.dr
    49,550 pointsBadges:
    report
  • TomLiotta
    Since this is clearly an academic exercise, we can't tell you how to do it. We can give some guidance to help you understand how to do it.   First, you should understand that this is not a good table design. If payments should be matched with charges, then there should be an identifier like 'InvoiceNumber' that can be associated on both sides. But even that isn't especially useful. An 'OutstandingBalance' for the EEID might be more useful.   To solve this problem, start with an understanding that this TABLE can be seen in terms of two VIEWs. One VIEW would be those rows that have a non-zero value for DR; the other has non-zero for CR. With two VIEWs to work with, testing queries that match rows between them might suggest approaches to a solution.   Tom
    125,585 pointsBadges:
    report
  • kmanette
    TomLiotta-There are no invoice numbers associated with these transactions. These are debits and credits hitting a clearing account, and no other detail can be added to this table. There is one field on each record that you are not seeing, and that is date of transaction-though the date would be totally irrelevant to writing this query. This is not an academic exercise-not sure why you would think that. This is a real life work scenario and we are out of options at the moment for reconciling this particular clearing account. Philp- I am exploring doing some inner joins and see where that may take me. Though, I will ultimately be in the same predicament of not knowing what language/expressions to use when trying to write a query that returns matching or unmatching records.    Thanks
    45 pointsBadges:
    report
  • philpl1jb
    Good luck.  In academic experiments credits and debets might have a one-to-one relationship and are for exactly the same amount.  In academic experiments perhaps no two debits or credits for the same person are for the same amount.  Perhaps these rules exist in your database .. but not in any of the real world data that I've had to work with.  Of course two credits might tie to one debit or two debits might be paid by one credit.So what would I do .. create another table .. clearing table which contains a column for credit id, debit id and amount.  This table would sometimes have multiple rows for one credit or debit... but you will probably find that some of these relationships will have to be done "by hand".   
    49,550 pointsBadges:
    report
  • philpl1jb
    Then the dates become important .. I would take the lowest (oldest) dated credit for each person and match it with the lowest dated debit where the credit and debit has an amount that is > the total of that credit/debit in the clearing file.  Use the less of the credit - total cleared for that credit or the debit - total clearded for that debit to make a new row cleared.  I think that you'll be repeating this until you don't find any more credit/debits with uncleared balance that can be cleared. 
    49,550 pointsBadges:
    report
  • TomLiotta
    This is not an academic exercise-not sure why you would think that.   I can explain that, but it won't be a very nice description of the database that you have and of the requirements that you listed. It might help with future questions.   The 'academic' characteristics begin with the simplicity of the question parameters. You supplied a specific set of data. The requirements you gave can be satisfied easily if that data is used. It doesn't need any techniques above the novice level, techniques that would be part of a beginner's course in queries.   Also, the requirements themselves don't make business sense. (And it all doesn't actually seem to fit with the accounting definitions of a 'clearing account'.) They do make academic sense, though, as part of an academic assignment that would ilustrate principles.   Without procedural code, there is no valid way to match DRs and CRs in the manner that you requested using only a query. In order to do it with any set of data rather than just the example set, a procedure must be followed. Consider data like this:
    ID EEID DR CR
     1  333 30
     2  333 10
     3  333 20
     4  333 50
     5  333    30
     6  333    30
     7  333 30
     8  444 60
     9  444    60
    10  444 70
    It's very similar to your example data set, but with a few extra lines to illustrate a problem. ID 2 and 3 have been added for two new DRs, and ID 6 has been added to show a single CR that offsets those two new DRs in a single transaction.   The problem brought by this is that a "query" cannot tell that both ID 5 and ID 6 do not associate with ID 1. A procedure is necessary. The proc would need to associate ID 5 with ID 1, and then create some kind of mark that ensures that ID 1 can't be re-used for a different CR. It might DELETE both ID 1 and ID 5 before going on to the next CR, or it might UPDATE both with a flag to eliminate them from further processing. Then when ID 6 is processed, it wouldn't mistakenly match it with ID 1.   But your requirements only allowed a 'query' solution, and your database eliminates a 'flag' column and apparently it cannot be altered. Those are also general 'academic' requirements because they don't reflect common real-world business situations.   The question you wrote asks for a specific technical solution for a very specific scenario. A technical solution for the scenario is possible, but it can't be used for real-world unpredictable data sets. It will give incorrect results.   The business answer is to redesign the database to have it record actual transactions. The transactions need to be related to business entities such as order numbers or invoice numbers or payroll advance IDs or whatever these transaction represent. The amounts cannot be the basis in a real-world situation because you can't guarantee that they won't be duplicated. Some type of "EntityID" needs to be assigned that guarantees a valid association.   A business problem describes a problem environment and looks for a good resolution. An academic problem gives exact details and asks for a specific solution. A solution for an academic problem illustrates a definite principle and often doesn't have a valid solution that can be directly used in business, like this question.   This exact question can be solved with fairly basic novice-level queries. However, the queries cannot be relied upon for actual business use with unpredictable data. The problem environment (the database) is wrong, and the environment is what needs to be fixed if this really is a business problem.   If it can't be changed and queries are required, then there is no valid solution.   Tom
    125,585 pointsBadges:
    report
  • philpl1jb
    Assuming that your sample was in date order, I would make the following entries in the clearing file.  Per the rules in my last post.
    Not particularly correct, but about the best I can do
    with the info available.
    CR_ID DB_ID AMOUNT_CLEARED
    1      5    30
    2      6    10
    3      6    20
    8      9    60
     
     
    49,550 pointsBadges:
    report
  • TomLiotta
    The "repeating" part is where it starts changing from "query" to "procedure". Since the original query needed was so simple (with the original data), the date value wasn't actually important. All that was needed was [ A.ID < B.ID ]. In any case, the conversion of the whole process from "matching DRs and CRs" to "calculating person balance" is the only reasonable way to go. -- Tom
    125,585 pointsBadges:
    report
  • philpl1jb
    Right .. although I wrote my rules based on an SQL based solution this would be simplier with a procedural language.  Same rules, just a more straight forward way to manage them.
    49,550 pointsBadges:
    report
  • kmanette
    TomLiotta-There is nothing simplistic about the set of data I have provided. The format got wacky when pasting it in, but it is a perfect sample from the population of data in the table. If a query can be written to return the matching or unmatching records in the data above, then it can be applied to all the data in my table. For person 333 there is an example of a set of records which I would consider to match, as well as a record dated later for the same amount, that the query should not return to me. There is also a second person in the sample. THe format got pasted very strangely. The records beginning with ID's 3 and 6 have amounts that should be in the credit column. When you first called it an academic exercise, it seemed as though you were insinuating that I was a student looking to cheat on a test. I have posted on a lot of different Access and coding sites and have not seen anyone else use that terminology. Are you an accountant? A clearing account should theoretically have an ending balance of zero. In reality, there are items that are still "open" due to timing issues. I am trying to use Access right now to narrow down on the items that are "unmatched" ultimately. If I could write a query that returned to me matching records, then I could write another query to return to me records which are part of the original table, NOT appearing in the match query. Those would be the "leftover items" that make up the ending balance. There would still be some manually removal of records for the few instances where a debit is cleared by two credits. The majority of the records would be the true timing items making up the ending balance. WIth that said, it may be easier to write a query that returns records which are "unmatching". For instance, a $40 debit and a $50 credit for person 333. IF there are no other $40 credits dated later and $50 debits dated earlier for this person, than these are what I would consider to be leftover "unmatched" items. Right now we are transitioning to a new reconciliation software which will match transactions based on automatch rules. Access is only a temporary solution. You are incorrect when you say that I NEED to have more data (invoice number, etc) in each record. Not true. A recon software tool wouldn't need that, and I understand that Access is not a recon tool. I am trying to get Access to do more than it was designed to do, temporarily. I do have a query written in my current table that gives me a nice balance by EEID which sums to the ending balance in this clearing account, but the problem is that I don't have aging information on the records making up this query. The query is summing up ALL transactions by person and giving me the net, and I asked it to not show me any record with a zero net. Maybe this query is what you were deeming to be "novice level". ANyhow, I am trying to come up with a query on just unmatched records ULTIMATELY so that I can turn it into a pivot table and see the real dates associated with these unmatched items. I think it is pretty funny that you are questioning the "real worldliness" of this example. I don't know what world you are in, but in the real world there are inconsistencies, transitionary periods where you make due with what you have, and in the real world people reach out to others for assistance and do NOT want feedback from unrealistic people that have God-complexes. I currently have two other people on other sites who are helping me with query language as well as SQL in order to perform this recon in Access. So far it seems to be working on the "unpredictable" data getting loaded into my table each month.
    45 pointsBadges:
    report
  • philpl1jb
    "Nobody expects the Spanish Inquisition" You want matching records .. records with the same eeid and dr = cr. is that what you want? And how did this sql fail? Select a.id, a.eeid, a.dr, b.id, b.cr from myfile a join myfile b on a.eeid = b.eeid and a.dr = b.cr order by a.eei, a.dr What does it do that you don't want to do and how does it fail?
    49,550 pointsBadges:
    report
  • philpl1jb
    .... well the SQL now fails in my mind .. add a where clause .. where a.dr 0 something like this Select a.id, a.eeid, a.dr, b.id, b.cr from myfile a join myfile b on a.eeid = b.eeid and a.dr = b.cr where a.dr 0 order by a.eei, a.dr
    49,550 pointsBadges:
    report
  • philpl1jb
    Lots of trouble with the editor again .. where a.dr 0 should read where a.dr greater then less then 0 but it dropped the greather then sign and the less then sign
    49,550 pointsBadges:
    report
  • TomLiotta
    To see one thing that's wrong with the queries so far, try the SQL on the data that I provided. The error should be quickly apparent in the results. If the editor was working at all today, I'd post results; but it's easy enough for anyone to try. The process simply isn't robust enough. -- Tom
    125,585 pointsBadges:
    report
  • philpl1jb
    And that would be the duplicate entries? Already identified these as a problem so I can tell there are duplicate debits with Select c.id from myfile c join myfile d on c.eeid = d.eeid and c.dr = d.dr and c.id d.id and c.dr 0 and where there are duplicate credits with Select e.id from myfile e join myfile f on e.eeid = f.eeid and e.cr = f.cr and e.id f.id and e.dr 0 and those will have to be excluded from the query and serviced seperately. Select a.id, a.eeid, a.dr, b.id, b.cr from myfile a join myfile b on a.eeid = b.eeid and a.dr = b.cr where a.dr 0 and a.id not in (Select c.id from myfile c join myfile d on c.eeid = d.eeid and c.dr = d.dr and c.id d.id and c.dr 0) and b.id not in (Select e.id from myfile e join myfile f on e.eeid = f.eeid and e.cr = f.cr and e.id f.id and e.dr 0) order by a.eei, a.dr and the not equal to symbols are now in 5 places and probably will not be displayed
    49,550 pointsBadges:
    report
  • TomLiotta
    As long as it's understood that all duplicates are dropped, including ones that match, that's probably as good as it'll get. -- Tom
    125,585 pointsBadges:
    report
  • carlosdl
    Unfortunately, it seems that the OP didn't appreciate the attention and time you were devoting to his/her problem.
    68,430 pointsBadges:
    report
  • philpl1jb
    We were working on the premise that we should understand the problem before we create the solution.
    49,550 pointsBadges:
    report
  • TomLiotta
    @carlosdl... What was unappreciated was my response to the OP wondering how it could be seen as an 'academic' question. If no one ever says what the characteristics might be, then it it won't be easy for others to see why questions might be treated the same. The way to truly resolve the problem is with a procedure, such as is almost certainly done by "reconciliation software" mentioned by the OP. But the OP for some unknown reason doesn't seem interested in seeing how a proc might give correct results. Something like this only seems meaningful if the volume of transactions for unbalanced EEID accounts is large enough to make manual reconciliation non-trivial. But if this is a high volume problem, then accuracy becomes much more important. A recursive SQL proc could probably handle it well. But if a query is the only acceptable solution when it can be proven to give incorrect (at worst) or incomplete (at best) results, that's the way it should go, I guess. -- Tom
    125,585 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following