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.