Please help with query

[kristenmanette]

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 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 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!!!!!!!

[kristenmanette]

well apparently I have stumped the experts or I am just getting ignored If these queries are not possible can someone please let me know?

[June7]

I had looked at your thread and made attempt to do something realized how difficult it would be and put aside for later. When I saw the thread was no longer in 'unanswered' list I checked it to see if someone was more successful. Your second post moved it from the 'unanswered' listing.

What you want will not be easy. Queries that involve criteria dependent on other records of the same table never are. Usually require subqueries. Review http://allenbrowne.com/subquery-01.html

Yours sounds particularly challenging. Why do you need this?

[kristenmanette]

Thanks for your response June! I am an accountant and I am attempting to do a clearing account reconciliation in Access. All these records are really journal entries, and the end result would look like a list of balances by EEID that total the ending balance in our clearing account. Currently, the account reconciliation software that we would normally use to reconcile cash and clearing accounts can not be used for this particular account(for reasons I will not bore you with).
With that said, I had come up with a query in Access which does reconcile these records and give me a nice listing that ties to our GL. THe problem though is that I can't get "aging" information on these ending balances by EEID because my query is grouping all records by EEID and giving me the net amounts. If I was to look at dates associated with these ending balances per EEID, it would be all the dates related to all the transactions per EEID and that isn't right. So, I need to basically get query that will show me the true remaining UNMATCHED records per EEID so that when I look at the dates assocaited with these records, they are just the dates assoicated with the unmatched records.
I left the date column out of my above example because it isn't relevant to the query, but I do have a date in each record, in Access.
I hope that makes sense. I am really tired and I hope I don't reread this post on Monday and realize I made it sound more confusing than it is!! ANyway-any help would be appreciated...any idea....

Thanks again!

I had looked at your thread and made attempt to do something realized how difficult it would be and put aside for later. When I saw the thread was no longer in 'unanswered' list I checked it to see if someone was more successful. Your second post moved it from the 'unanswered' listing.

What you want will not be easy. Queries that involve criteria dependent on other records of the same table never are. Usually require subqueries. Review http://allenbrowne.com/subquery-01.html

Yours sounds particularly challenging. Why do you need this?

[June7]

I googled: access database query aging invoice

Review this hit http://www.dbforums.com/microsoft-ac...-invoices.html