Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

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

Discussion in 'Information Technology' started by Larry Kahm, Jul 28, 2006.

  1. Larry Kahm

    Larry Kahm
    Expand Collapse
    Guest

    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
     
  2. Loading...


  3. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    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" <lkahm@nospam_heliotropicsystems.com> 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
    >
    >
     
  4. Craig Alexander Morrison

    Craig Alexander Morrison
    Expand Collapse
    Guest

    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.

    --
    Slainte

    Craig Alexander Morrison
    Crawbridge Data (Scotland) Limited

    "Larry Kahm" <lkahm@nospam_heliotropicsystems.com> 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
    >
    >
     
  5. Larry Kahm

    Larry Kahm
    Expand Collapse
    Guest

    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

    "Douglas J Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
    news:%23WjHL7oeGHA.1260@TK2MSFTNGP05.phx.gbl...
    > 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" <lkahm@nospam_heliotropicsystems.com> 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
    >>
    >>

    >
    >
     
  6. Larry Kahm

    Larry Kahm
    Expand Collapse
    Guest

    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" <cam@microsoft.newsgroups.public.com> 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
    > 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" <lkahm@nospam_heliotropicsystems.com> 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
    >>
    >>

    >
    >
    >
    >
    >
     
  7. Craig Alexander Morrison

    Craig Alexander Morrison
    Expand Collapse
    Guest

    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" <lkahm@nospam_heliotropicsystems.com> 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" <cam@microsoft.newsgroups.public.com> 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
    >> 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" <lkahm@nospam_heliotropicsystems.com> 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
    >>>
    >>>

    >>
    >>
    >>
    >>
    >>

    >
    >
     

Share This Page