Update Query

[Joy M]

Hi -

I tried using an update query, but I didn't get the results I wanted.
Actually, I don't know if an update query is the right way to approach this.

These are my tables:
tblDepartment
DeptID
DeptName

tblStudent
StudentID
StudentName
StudentAddress
HomeDepartment
LookupDepartment

The way it works now, the secretary types in a HomeDepartment for each
student. There are problems with variations in spelling.

I would like to add another field to the table, call it LookupDepartment,
which works using the LookupWizard and connects to the DeptID and DeptName
in the Department table.

Then I would set the value of the LookupDepartment = HomeDepartment. When
all is said and done, I will have a dropdown box for the LookupDepartment
and it will make things a lot easier for the secretary. Eventually we can
delete the HomeDepartment.

What I tried to do was use an update query to set LookupDepartment =
HomeDepartment. It only changed the first record, whereas the message said
it was going to update all the records. I am using a test database with 4
records.

I thought using an update query would be a good idea because there are
really 9000 record in the student table. The other way to do it is to go to
the table and initialize LookupDepartment one record at a time, to the value
in HomeDepartment..

So, can I use an update query to set the value of a dropdown field equal to
the value in another field, for all records in the table? If so, please
give me the SQL statement you would advise me to use.

Thanks for your help. I appreciate it.

Joy

 

[Jeff Boyce]

Joy

Congratulations! You've discovered one of the reasons folks in the
tablesdbdesign newsgroup generally recommend against using a lookup field in
a table!

If I understand correctly, your table has a HomeDepartment field with
variations in spelling. You are trying to set the value of a lookup field
to the ID of the Department table.

What you want to store is the DeptID, but ONLY for matching DeptName!

Better still, lose the lookup field and store a data type compatible with
what you have for DeptID.

Regards

Jeff Boyce
<MS Office/Access MVP>