Results 1 to 4 of 4
  1. #1
    4 Star Lounger
    Join Date
    Apr 2001
    Location
    Guatemala City
    Posts
    515
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Analyzer - Autonumeric field (Win XP Access 2003)

    I am trying to learn about the Table Analyzer. My problems come with how to handle an Autonumeric field. If I keep the old Autonumeric field in the new table, it converts to a Numeric field. I would have to manually keep track of and enter new items. If I accept a new Primary code as Autonumeric, the current numbers are messed up. How can I handle this?

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Analyzer - Autonumeric field (Win XP Access 2003)

    The table analyzer is intended to help you normalize a table. The old autonumber is converted to numeric because it provides a link back to data that might have been created using the unnormalized table. It isn't intended to be used as the primary key for the new tables converted from the old one. Table Analyzer isn't an instance fix for a denormalized database, it's a tool for normalizing, one table at a time. Normalization requires a lot of work on the part of the developer to connect the dots.

    Perhaps if you explain more about what you're trying to accomplish, someone can help.
    Charlotte

  3. #3
    4 Star Lounger
    Join Date
    Apr 2001
    Location
    Guatemala City
    Posts
    515
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Analyzer - Autonumeric field (Win XP Access 2003)

    I have extracted a new table from an old one. The new table is called Plant, and includes IDPlant from the old table (numeric), plus PlantSite (Text), and the new ID field (Automeric), the new primary key. That IDPlant field was previously an Autonumeric primary key. I understand that the old IDPlant field is there to maintain a link with other objects. Now what do I do if I add a new record? It will possibly duplicate numbers in the old IDPlant field. Do I have to go back and correct all of my records to substitute the new ID field for the old IDPlant field?

    Is the table Analyzer a practical tool for trying to normalize an old table that has problems? Is it used?

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Analyzer - Autonumeric field (Win XP Access 2003)

    If you add a new record, it won't have links to other objects will it? You can't just run the analyzer on one table and solve your problems. You have to look at the related tables as well, but not necessarily with the analyzer. If the primary key from the Plant table is a foreign key in other tables, then you need to add a new field to the other tables and populate it from the new ID field using an update query. Then you can delete the old key field if it is no longer needed in the related tables. If the old plant table had other data in it that was connected on that field, then you have more work to do.

    The analyzer is a good tool if you aren't firmly grounded in normalization already. However, like all tools, it is limited in scope. Without knowing more about the old table structure as it compares to the new table structure, it's a bit hard to be more specific.
    Charlotte

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •