Help normalize data - what's a "good' approach?

[Larry Kahm]

I am supporting an existing A2000 database that was built by someone who
learned Access "informally" (that's as polite as I'm going to be with that).
I need to clean up some of the errors and this is the first one that will
have any significant impact.

The Staff table contains pertinent information about the firm's employees.
It contains a field, Title, which is defined in the table as combo box /
look-up field (I know, this is considered a "sin"). The value list contains
items such as: Associate, Principle, Administrator, etc.

I want to build a new table, tblStaffTitles, with an autonumber key field
and the values from the look-up field.

But, what is the best approach for correcting and updating the Staff table
itself?

Here's what I was thinking of doing:

add a new column to the table, StaffTitle as a long integer (for the
foreign key)
run an update query that would use the current textual value of the Title
field to populate the StaffTitle field from the tblStaffTitiles
delete the Title field and rename StaffTitle to Title
add the two tables to the Relationships window and link the fields (pk to
fk).

How far off is this approach? Is there something better or more practical?

Thanks, in advance!

Larry

 

[Douglas J Steele]

If you're using a lookup field, you should already have the two tables. All
you should have to do is open the table in Design mode, select the lookup
field then look at the Lookup tab in the bottom left-hand corner. Change it
from Combo Box to Text Box, and you should see what's actually stored in the
lookup field.

 

[Craig Alexander Morrison]

Not too sure what you want the AutoNumber for, unless you are very short of
disk space or you have a large database nearing the 2GB limit.

Why not establish the StaffTitle table with a single field containing values
such as "Associate", "Principle" etc. Make that field the Primary Key (PK)
and then link the two fields establishing RI Cascade Update (but not Delete)
between StaffTitle and Staff.

If you are going to use an AutoNumber in StaffTitle and use it as PK you
will need to establish yet another unique index in that table for the actual
Staff Title otherwise there is nothing stopping you having duplicate records
(other than the AutoNumber) at the engine level.

If you do insist on using an AutoNumber or other such unnecessary field
then:-

1. run an append query into your new table having also established the
unique index on the actual title this will ensure that exact duplicate
titles will not be created although you will have to watch out for spelling
mistakes.

2. run an update query whilst joining on the text field on the Staff and
StaffTitle tables to update the new Long Integer field.

3. establish the relationship

4. delete StaffTitle text field in Staff table.

If you avoid the AutoNumber just run the first append query (assuming that
any spelling mistakes have been cleaned up) and establish RI Cascade. You
won't save disk space (but then todays top laptops have over 100GB) but you
will ensure that only valid values that exist in StaffTitle can be selected
for the Staff table.

 

[Larry Kahm]

I'm sorry that I wasn't clear in my original post - and I should have been.

In the Staff table, the Title field's look-up values are supplied by a value
list that is hardcoded in the field.

Larry

 

[Larry Kahm]

I want the Autonumber because it will provide me with a "quick and dirty"
means of identifying the leading 8 titles in the firm. I >know< I'm not
supposed to depend on something like that, but if I enter the data in the
exact manner that I need it, I can use that key to help clean up some other
coding nightmares.

An office admin is giong to be maintaining/updating this table in the
future. With less than 20 titles, there is no likelihood that an existing
one will be duplicated.

Thanks for the clarification of the procedure. I really do appreciate it!
And of course, I'm only going to do this after I back-up the current
database and have a second copy of the table on hand, just in case.

Larry

 

[Craig Alexander Morrison]

I would suggest that rather than an AutoNumber you use a Byte data type
field and record a ranking order to the titles.

The byte field allows values in the range 0 to 255.