I have two tables: tblPatient, which contains an AttendingMD field, and tblPhysicians, a lookup table which contains an autonumber field and a Name field.

The AttendingMD field is a ComboBox with the following parameters:
RowSource:SELECT [tblPhysicians].[Name] FROM tblPhysicians;
Bound Column:1
Column Count: 2

Data is entered into tblPatient using a form, which has a cboAttendingMD field with Row Source = SELECT Name from tblPhysicians;

I have entered a considerable amount of data (2000+ records), and I notice that one of the physicians' names is misspelled.

So I go into tblPhysicians and correct the misspelling.

Now I would like to update tblPatient so that all the records that have that physician in the AttendingMD field have the correct spelling.

Thanks for your assistance!

Hi,



What version of Access are you using? Please remember to select the version number when posting questions in case it becomes relevant to the discussion.

If you are up to it, I would recommend that you redo the table structure for your Patients table. Using a lookup field at the table level is not recommended. Also, you should have been storing the Primary Key from your lookup table as a Foreign Key so that any changes you make to the lookup table will not affect your main table.

If possible, I recommend you do the following:

1. Make a backup copy of your db
2. Add a numeric field (long integer) to your Patients table
3. Create an UPDATE query to insert the primary key values from the Physicians table based on matching names from both tables
4. Link this new field to the primary key from the Physicians table and set Data Integrity

If everything matches, you can delete the lookup field from your table and just use the foreign key field from now on.

Just my 2 cents...

Thank you for your prompt and helpful response. I am using Access 2007.

Unfortunately, I am relatively new to Access and not sure of the exact steps I need to take to implement your suggestions.

I will try to find some tutorials on update queries and data integrity; however, if you (or anyone else) could give me a bit more guidance, I would deeply appreciate it.

Thanks again,
David

Hi David,

Were you able to add the new numeric field to your table?

I'm not sure I understand your combo box query. Your query contains only one column, Name (which is a reserved word and a very bad choice of fieldname!), but you're saying that you want to include the first two columns - column 2 of 1???

Does your Physicians table have a numeric or autonumber Primary Key? What is its name?

What is the datatype of the AttendingMD field? It SHOULD be a Number... Long Integer, and it should contain the numeric value of the doctor's ID. Be sure that you're looking at the actual content of the field by changing its Lookup property from Combo Box to Textbox - the Lookup misfeature conceals the actual content of your table from view and decieves you!

Yes, I added a numeric field. Here's where things get a little confusing

Hi

Assuming that

1 - You've added the Number (Long Integer - Indexed Yes, Allow Duplicates) field to your patients table and called it something like PhysicianFK (this will be what's known as the Foreign Key to your tblPhysicians table)
2 - You have an Autonumber field in your tblPhysicans table called PhysicianID, and it's set to be the Primary Key of that table
3 - Change the name of the Name field in the physicians table to PhysicianName

then the steps you'd need to take are :

1- Make a backup of your db (or even two or three backups) and work on one of the backups for now
2 - Create a new query, and switch to SQL view of that query. Copy and paste the following SQL into the SQL view of the new query

3 - Run the query agreeing to the You're about to update etc etc message box if you get one
4 - Check in your tblPatients table that the PK values from tblPhysicians have been added
5 - Have a look in your physicians table for the mis-spelled name and make a note of the Primary Key number of that record, and also of the number of the Primary key of the correctly spelled name
6 - Create another new query, switch to SQL view again and paste the following

7 - Run the new query and check that the numbers have been correctly updated. Repeat steps 6 and 7 for any other incorrectly spelled names
8 - Delete the old text name field from your tblPatients table so you're just left with the new numerical Foreign key field

That's about it, you should be good to go. Note, all of the above is untested - and did I mention you should try it on a backup. If I didn't, try it on a backup!!!

Hi,


That's great! Please follow Alan's suggested steps. If it doesn't work, you could try this SQL:

UPDATE tblPatients
INNER JOIN tblPhysicians
ON tblPatients.PhysicianName=tblPhysicians.PhysicianName
SET tblPatients.PhysicianID=tblPhysicians.PhysicianID

Please make sure you use the appropriate field names from your tables.

Also, to avoid using two queries, perhaps you could put back the wrong spelled names in the Physicians table first and then correct them later after you update the Patients table.

After you made sure that all IDs from the Physicians match the Patients, then you can delete the physician names from your Patients table.

Just my 2 cents...