In one table, how to exactly match one debit and one credit of same EmpID, with ten columns of data.

I have a table of Various Debits and Credits, and I want a one-to-one exact match for the same EmpID. I have created a separate Debit query and a Credit query, then using the Debit query I tried a Duplicate query to get the exact match of ONLY debits that match the credits but I have too many results (I don't want debits that do not match a credit; also, I do not want multiple credits that match one debit).  I would like to do this the most efficient way possible.

Thank you for your kind assistance beforehand.
PRworkerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeOwner, Developing Solutions LLCCommented:
What does your table structure look like?

Can you post some data?

Best way to do this would be to provide an Excel spreadsheet with some sample data, and what you expect the output to look like based on that data.
Gustav BrockCIOCommented:
You can use a query:

Select
    Debits.*,
    Credits.*
From
    YourTable As Debits,
    YourTable As Crebits
Where
    Debits.EmpID = Credits.EmpID
    And
    Debits.Debit = Credit.Credit

/gustav
Rey Obrero (Capricorn1)Commented:
try this query

select A.*
from tableX as A
Inner join
(select B.empid, B.credit
      from tableX as B
      group by B.empid,B.credit) as C
on A.empid=C.empid and A.Debit=C.Credit


.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

PRworkerAuthor Commented:
OK, I will  compile sample data, as well as the desired result.    

Thank you so much for your prompt replies.
Gustav BrockCIOCommented:
You are welcome!

/gustav
PRworkerAuthor Commented:
Ok, attached is the sample data with the sample download and the sample JE needed.

I also added the vlookup for the segment info.

 
Dear Gustav and Rey,  I am a rookie with Access, so if you could provide more detailed instructions, I would be deeply grateful.  

Many thanks again for your kind assistance,

;0
PRworkerAuthor Commented:
Gustav BrockCIOCommented:
I imported the JE worksheet in a demo, and created a conversion query to obtain debit and credit.
It also contains a query "Matches" which uses my query above to find the matches.

/gustav
JE.accdb

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PRworkerAuthor Commented:
The solution was simple and efficient, and the response was quick.  i am very thankful for the kind assistance and input from these online experts and would highly recommend them to others.  Thank you so much!
Gustav BrockCIOCommented:
You are welcome! Thank you for the kind Words.

/gustav
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.