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

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.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/9886-help-normalize-data-whats-good-approach.html
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9886

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

 
Do share your immediate thoughts or reactions on this issue? We value your views! Login Now! or Sign Up Today! to share your views with us.. Gurfateh!

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9886
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.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Larry Kahm" wrote in message
news:GD%ag.5595$RY2.1298@trnddc02...
> 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9886
> 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
>
>

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9886
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.

--
Slainte

Craig Alexander Morrison
Crawbridge Data (Scotland) Limited

"Larry Kahm" wrote in message
news:GD%ag.5595$RY2.1298@trnddc02...
>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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9886
>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
>
>

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9886

"Douglas J Steele" wrote in message
news:%23WjHL7oeGHA.1260@TK2MSFTNGP05.phx.gbl...
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9886
> 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.
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no e-mails, please!)
>
>
> "Larry Kahm" wrote in message
> news:GD%ag.5595$RY2.1298@trnddc02...
>> 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
>>
>>
>
>

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.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9886

Larry

"Craig Alexander Morrison" wrote in
message news:u9tDB$oeGHA.3692@TK2MSFTNGP03.phx.gbl...
> 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9886
> 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.
>
> --
> Slainte
>
> Craig Alexander Morrison
> Crawbridge Data (Scotland) Limited
>
> "Larry Kahm" wrote in message
> news:GD%ag.5595$RY2.1298@trnddc02...
>>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
>>
>>
>
>
>
>
>

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.


--
Slainte

Craig Alexander Morrison
Crawbridge Data (Scotland) Limited
"Larry Kahm" wrote in message
news:738bg.14934$Nw6.6284@trnddc03...
>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" wrote in
> message news:u9tDB$oeGHA.3692@TK2MSFTNGP03.phx.gbl...
>> 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.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9886
>>
>> Why not establish the StaffTitle table with a single field containing
>> values
>> such as "Associate", "Principle" etc. Make that field the Primary Key
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9886
>> (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.
>>
>> --
>> Slainte
>>
>> Craig Alexander Morrison
>> Crawbridge Data (Scotland) Limited
>>
>> "Larry Kahm" wrote in message
>> news:GD%ag.5595$RY2.1298@trnddc02...
>>>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
>>>
>>>
>>
>>
>>
>>
>>
>
>