How to perform VLOOKUP equivalent in Access for whole column.

Hi,  I have a payroll table that includes 150 debits and credits and employee ID’s.  In excel I separate the Debits and Credits into separate tabs and concatenate the EmpID and the Absolute value of the Debits and Credits.

In the third tab, I copy the DEBIT row info, and then in the last column, I use Vlookup (using the Concatenated EmpID & DEBIT) to match all the row data for all the EmpID& Credits (Absolute value) information (for the next ten columns).  I then verify that the sum of the debit and credit info = zero.

In the fourth tab, I repeat the same process in the third tab, but instead of the debits and credits being on the same row, the credit and debit info is stacked “interlinearly” so that each  EmpID&Debit  row is followed by its corresponding EmpID&Credit row, plus all the detailed row information. This tab is used as the backup for a journal entry.

In the last tab, I sum all the EmpID & Debit info, and all the EmpID & Credit info (and all the supporting information) and the total sum of all the amounts should net to zero.  This information is used for the actual journal entry

I would like to put all of this information into Access, but I do not know how to replicate the VLOOKUP function in Access.  I have tried to use the  DUPLICATEs query on the concatenated EmpID& AbsAmt, but I end up with too many results (too many debits).  I need exactly a match of EmpID and the AbsAmt, no more, no less. (Also unsure I have normalized data).

  Can you please suggest how to resolve this?

Your kind assistance is deeply appreciated.
Who is Participating?

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

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.

Do you have some sample data and could you post a sample result?  What exactly are you trying to accomplish?  Did you want a report that looks like an accounting journal or did you just want a query that can tell you if you're balanced?

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
The concept of how storing data in Access could be different from what we usually do in Excel. Since Access is a database, we try to normalise our data.

In Access, it's easy that to ONLY store the necessary data in separate tables, and then when it's needed, we use a View to joining the tables with relevant criterion to produce the fields we required. In your case, I guess a View is good enough for you, which a joining SQL in Access is similarly producing the results as VLookup did in Excel.
Dale FyeOwner, Developing Solutions LLCCommented:
Agree with Ron,  need some sample data and to actually see the format of the data in the other tabs.

Depending on exactly what you are trying to track, you could probably get away with a couple of tables:

1.  Employees: contains employee info + EmpID
2.  Debits: contains the details about a debit, DebitID, Amount, Period, rate, ...
3.  DebitsAndCredits: EmpID, DebitID, ActivityDate, DebitAmt, CreditAmt

I believe that with tables like these, you would be able to generate reports which replicate the way you are displaying your data in Excel.
In a relational database the VLookup() is normally implemented as a query that joins to the lookup table.  In some instances, you might use DLookup() but ONLY from a form or code (but not inside a code loop) where you only need one field from one record.

Please don't start multiple threads on essentially the same topic.  I think you should close this one and continue with the other one where Rey has offered a solution that uses a query.

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:
Thank you for your kind assistance.  I closed this thread as suggested.
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.