Check Table, if found, fill Fields

[dohernan via AccessMonster.com]

I did a mess of a database, and I'm trying to clean it up. My original
database/Table has Date Received, Last Name, First Name, SSN, FormFrom,
FormType (pulldown) and several other fields, including Date Completed, and a
Record field that's an Autonumber.

Every time someone requests a letter/form etc from us they get added in as a
new Record. So one person may have 5 different things etc., and that 1 person
is listed 5 different times. It should really be 1 person with 5 different
transactions. I am trying to Normalize.

I want to break off the Last Name & First Name Fields. I created a 2nd table
(Namesetc) that has SSN, Last Name, First Name.
I just don't know how to tie the 2 together correctly.

I have a Personnel form, when someone enters a SSN I want it to check the
Namesetc table and see if it exists there. If the person is already in the
Namesetc table, as soon as the SSN is typed in and recognized I want the Name
fields in the Form to be filled in automatically.

If the SSN doesn't exist in the Namesetc table I want the Form to accept
Last/first names and add it to the Names table with the SSN.

I'm not sure how to do this.

Thanks.

 

[John W. Vinson]

I did a mess of a database, and I'm trying to clean it up. My original
database/Table has Date Received, Last Name, First Name, SSN, FormFrom,
FormType (pulldown) and several other fields, including Date Completed, and a
Record field that's an Autonumber.

Every time someone requests a letter/form etc from us they get added in as a
new Record. So one person may have 5 different things etc., and that 1 person
is listed 5 different times. It should really be 1 person with 5 different
transactions. I am trying to Normalize.

I want to break off the Last Name & First Name Fields. I created a 2nd table
(Namesetc) that has SSN, Last Name, First Name.
I just don't know how to tie the 2 together correctly.

I would say by SSN (or, better, by your own privately maintained unique ID;
strictly speaking it's illegal to use SSN for this purpose, not everyone has
one - noncitizens without green cards - and they're not necessarily unique)
should remain in your Record table as a link.

You can use an APPEND query selecting *distinct* SSN, LastName and FirstName
fields from your current table to append to the new table. Run a Duplicates
query to check for duplicate SSN, and another to check for duplicate last and
first names, to detect possible data entry errors.

I have a Personnel form, when someone enters a SSN I want it to check the
Namesetc table and see if it exists there. If the person is already in the
Namesetc table, as soon as the SSN is typed in and recognized I want the Name
fields in the Form to be filled in automatically.

If the SSN doesn't exist in the Namesetc table I want the Form to accept
Last/first names and add it to the Names table with the SSN.

Put a Combo Box on the form to allow selection of a SSN. Use the combo box
wizard option "use this combo to select a record". If the desired SSN isn't in
the combo, just go to the new record and start adding data.