Replicating vlookups with access?
The main process I am trying to replace at my company is a very time intensive auditing practice where for our clients we receive several rosters of information which show their employees at the beginning of the year, end of the year, their hires, terminations, and transfers. With that data we perform a lot of vlookups to make sure everything is as we would expect- i.e. we combine the hires with the employees at the beginning of the year and make sure that it accounts for everyone on the end of year roster (if anyone appeared without an explanation they get flagged for research). The same is done in the other direction- looking at where things started at beginning of year and who was terminated to see if anyone disappeared without a corresponding termination record.
When I started this course I was very optimistic that I was going to be able to write several queries one time only to replicate this process; then be able to point them to new tables for each new client. But now I'm realizing I don't think i learned any skills to help me handle a "who is missing problem" (im not adding columns from different tables, aka the core function of a query, and i'm not looking for duplicates in a table aka the help of a total query).
Lifelong excel user looking for ways to integrate Access into my daily routine. Curious for someone's opinion on whether what I'm trying to do in Access is actually more suited for Excel.
The main process I am trying to replace at my company is a very time intensive auditing practice where our clients send us several rosters of employee information which show their employees at the beginning of the year, at the end of the year, their hires, terminations, and transfers. With that data we perform a lot of vlookups to make sure everything is as we would expect- i.e. we combine the hires with the employees at the beginning of the year and make sure that it accounts for everyone on the end of year roster (if anyone appeared without an explanation they get flagged for research). Likewise we check to see if those who disappeared between the beginning and end of year are accounted for on the terminations.
When I started with access I was very optimistic that I was going to be able to write several queries one time only to replicate this process; then be able to point them to new tables for each new client. I know how to use the find unmatched records query, but just to do the first comparison (beginning of year + hires versus the end of year) it seems to me that I have to (1) use make table queries to parse the pertinent information from my beginning of year roster and hires list since each contains dozens of columns many of which have minor spelling issues (2) use an append query to add the hires together with the beginning of year (3) check that new table against the end of year roster with a find unmatched records query (4) build a make table query to put all of the records that weren't found on the End of Year list into their own table (5) Compare that last table to my terminations table to find unmatched records.
If I am running 5 queries just to see whether all of my people who were there at the beginning of the year/added as hires are explained by the end of year list/terminations then it's hard to see the value add over vlookups. So I'm hoping someone can either confirm this conclusion or give me some pointers on how I'm structuring my tables / queries.