Results 1 to 5 of 5
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Filling in the blanks (A2K SR1)

    In my database, I've linked to an Excel spreadsheet that has the names, payroll ID numbers and SSNs for most of our employees. This particular spreadsheet, however, has some blanks--the list of names is incomplete, and some records are missing either payroll ID or SSN or both. We've located another Excel file that has ALL the missing information, but the name field has just a single field for the name (example: SMITH AB). In Excel, I've parsed that field into three: Last name, first name, middle initial. Here's what I'd like to do next:

    1. Merge the two lists so as to fill in the missing data. Where a name in my current linked spreadsheet (table) has a name but no SSN or payroll ID, I'd like to insert the missing information.
    2. Names that do not appear on my existing linked table would be added. Of course, I'll probably have to fix the NameFirst records manually, but that shouldn't be too difficult.

    How can I do this?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Filling in the blanks (A2K SR1)

    (First a probably impracticable idea: can't you use the complete table instead of the incomplete one?)

    How much work this is, depends on the uniqueness of employees with missing data. If you have to go by name information only, you might have two employees "John R. Smith" without Payroll ID and SSN. Is there other identifying information?

    Once you've managed these "doppelgangers", you can create update queries to fill in the missing data. First handle the cases with both Payroll ID and SSN missing, then the cases with just Payroll ID missing, and finally those with SSN missing. In each of these queries, add both tables and link them on as many identifying fields as possible. Set the appropriate criteria for the missing field(s), and update the missing field(s) with the value from the other table.

    To append the records with missing names, first create a Find Unmatched Query using the wizard. Then make it into an append query.

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Filling in the blanks (A2K SR1)

    Mm-m-m...I've thought about using the complete one, but it really isn't quite complete. It has some fields I really, really need (SSN, payroll ID and all that), but it lacks certain other fields that I haven't mentioned.

    I have one or two "doppelgangers," including two employees with the same first AND last names--and they're not related. Anyway, so the update query is the way to go? Great. I'll try that when I get back to work Monday. (We're on holiday today.) If I understand what you've written, I need to run not just one query, but two or three. Thanks!!

  4. #4
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    NYC,USA,Earth
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filling in the blanks (A2K SR1)

    I would add to Hans post to use the Is Null expression in the criteria row in the columns which contain missing data. This way you will only replace the missing data and not the existing data.

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Filling in the blanks (A2K SR1)

    Hans and Paul: Thank you both very much! I'll try this Monday and let you know how it worked for me!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •