Results 1 to 9 of 9
  1. #1
    swooshgang is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    3

    Replicating numerous vlookups with access to save time?

    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.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Why would you have 'new tables for each new client'? This statement is a big red flag indicating non-optimized data structure.

    Access is certainly the best tool for relational data storage. And if you need 5 queries to accomplish the same thing as Excel, so what? If it's faster and less 'time intensive', that's what matters.

    Spelling errors would certainly be an issue in both apps.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    swooshgang is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    3
    With respect to your comment on the new tables for each client: I am probably missing something huge and fundamental to the way Access works (I'm a brand new convert) so please help me understand how I can apply the same queries to a new client? I would have envisioned importing the data for the next client (i.e. a new list of beginning of year employees, hires, terminations, end of year employees, etc) into new tables in Access.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I would expect to import records into existing tables, not create new tables every year.

    Apply filters in queries to limit the dataset. If you only want to see a particular client records, then apply filter for that client ID.

    Have you studied an introductory tutorial book?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    When I started this course I was very optimistic
    Referring to an actual instructional course you took, or is that a "course" of action you undertook here? FWIW, I will second the motion that you are raising red flags with your comments. Does the concept of db normalization ring a bell and do you understand it? If not, time to put the brakes on.
    While it is not unheard of to need more than one query to achieve a goal 5 is a bit much - especially for a properly structured db and what you need out of it.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    swooshgang is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    3
    I have taken two online courses on Access. I'm building this purely as an exercise to determine the feasibility; there is no risk of me deploying something that is going to be damaging to my clients.

    The issue is that we have hundreds of clients and hundreds of corresponding excel workbooks, all which have 8 tabs of data. Pulling it into Access one client at a time and building a client field is a good suggestion, although the data we receive from clients is constantly inaccurate and needs revision (in fact what I'm trying to achieve with Access is a more automated revising process).

    And while online courses represent normalization based on cliche and extremely black and white examples (an "orders" table, "products" table, etc.) there are no such clear dividing lines here except for the ones already stated (beginning of the year, end of year, hires, terminations) all of which contain 15+ fields (employee ID, first name, last name, hire date, job title, job code, pay grade, last job change date, department, department ID, work city, work state, manager work city/state, etc.).

    If I was in HR then I could see my way to creating tables for different buckets of information: perhaps a compensation table for all of the aspects of a persons compensation, a employee history table for all of their pertinent change dates, etc. But in this case such divisions would be totally arbitrary as no relationships exist; the client updates the data at their own pace and just provides us snapshots at a moment in time which will be outdated within 10 minutes of sending them to us.

  7. #7
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Curious for someone's opinion on whether what I'm trying to do in Access is actually more suited for Excel.
    Your last post clears up a lot of things. If there's no consistency among all of the rosters then it would seem whether or not Excel is easier would be a matter of trial and error and choice. About all Access can do for you is make it easier to get at data for reporting or sub dividing by way of queries. But if VLookups are easier and reporting/querying isn't beneficial then Excel just might be the way forward - especially if work sheets are haphazard in terms of layout, data formatting and the like.

  8. #8
    Join Date
    Apr 2017
    Posts
    1,673
    It is possible to get such messed up data into Access too, but I think you need both Excel (I assume clients send data in formats Excel is able to read - Excel files with any extension, csv-files, text-files, xml-files, etc.) and Access for this. And the info clients are sending must definitely have table-like structure.

    Then you need Excel file(s) with standardised table(s), which is/are linked into Access database (You did write about 8-column table, and about 15-column tables from clients, so probably you need 2 standardised tables). Additionally to info in client tables, this table/those tables must have also a field with client identifier, and probably also a field with data entry date (when you need this date later in access to validate data).

    Then you need a 'translator' Excel app, where in some table for every client is described according table structure (client identifier, client file extension, client's column headers for every column in client file, in case some client sends e.g. text files without headers, then column numbers, etc.). This app also has sheet, where you can select client identifier, and a button to start translation procedure.
    The translation procedure opens file picker, where you pick client data table (only files with right extension are listed), and then the procedure reads data from there accordingly to table structure described for this client, checks is this data in standardised table or not, and when not, then makes preliminary data integrity check (it is fully up to you what this includes), may ask when there are some problems with data what to do, and when all was OK, then writes new data into standardised table.

    After that Access can take over. You have Excel table(s) linked into your Access app, but it is bad idea to use this data directly - e.g. because after some time you have to delete older data from standardised Excel table. You need separate table(s) for info in linked tables, and procedure(s), which update Access table(s) with info from linked table(s). And probably make some additional data integrity check too, e.g. comparing new data and old data from same client. And of-course you need some event which runs this procedure (or a Window's scheduled task, which runs e.g. every night).

  9. #9
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    I think what you're trying to do can be accomplished.

    Can you give an example of your various forms of data? If you have a standardized format (if not standardized terminology) within the files I don't see why you can't do what you want. As others have mentioned, I would tend to aggregate this on a single table and just pull out the customer(s) you are interested in when you run your queries. I'm just getting stuck on how you describe your incoming data.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 07-08-2015, 08:59 AM
  2. Replies: 5
    Last Post: 02-28-2015, 10:56 AM
  3. How to Tackle Numerous Fields for Calculations?
    By Heatshiver in forum Access
    Replies: 4
    Last Post: 03-16-2012, 07:17 PM
  4. access replicating itself
    By tintincute in forum Access
    Replies: 3
    Last Post: 09-08-2010, 12:49 PM
  5. GROUP numerous lines to one record
    By tweetyksc in forum Queries
    Replies: 3
    Last Post: 07-21-2010, 01:14 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums