Filling in the blanks (A2K SR1)

Topic

New Reply

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?

Reply | Quote

Replies

(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.

Reply | Quote

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!!

Reply | Quote

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.

Reply | Quote

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

Reply | Quote