Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Sep 2002
    Location
    Indianapolis, Indiana, USA
    Posts
    80
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Lookup field Table (2003)

    I am fixing a database (really a spreadsheet in Access, you get the idea) where the original developer (I'm being nice) used the lookup feature in the table field design. So I have this big table with all of the lookup values from autonumber fields from 60 lookup tables. I want to store the display value and not the number (lookup value) in a normalized table. Is there an easy way to reference the display value and not the stored number in a query without running 60 separate queries or am I stuck with constructing an individual query against each of the lookup tables?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Lookup field Table (2003)

    It seems to me that if you're storing the lookup value instead of its ID in a table, you are denormalizing it instead of normalizing it.

    What objection do you have against storing the lookup values in the table?

  3. #3
    Star Lounger
    Join Date
    Sep 2002
    Location
    Indianapolis, Indiana, USA
    Posts
    80
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Lookup field Table (2003)

    I have no objection to storing lookup values in a table and in fact am converting the 60 lookup tables to just one table and converting the 60 fields they represent to two fields in the main table. The problem is that the 60 lookup tables are really data items and should not be in separate tables. The main table has just the autonumbers stored and I was looking for an easier way, if it existed, to insert the actual value in place of the autonumber without having to perform 60 separate queries.

    eg of what I inherited
    table: eyes
    id color
    1 blue
    2 black
    3 brown

    table: hair
    id color
    1 brown
    2 black
    3 white

    Table: main
    id eyes hair
    1 2 3
    3 1 1

    Converting to:

    Table: trait
    Trait Characteristic
    eyes blue
    eyes brown
    eyes black
    hair brown
    hair white
    hair black

    Table: Main
    ID Trait Characteristic
    1 eyes blue
    1 hair black

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Lookup field Table (2003)

    I don't think it's a good idea to store text values in a table when a number would suffice. I'd stick with the original design.

  5. #5
    Star Lounger
    Join Date
    Sep 2002
    Location
    Indianapolis, Indiana, USA
    Posts
    80
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Lookup field Table (2003)

    The problem with the current structure is the items really are data and not worthy of table status. I loath storing a table of numbers that are meaningless without a magic decoder ring. I have had to fix too many corrupted databases using this type of philosophy. There is absolutely nothing wrong in a modern database with storing text or using text in a primary key, especially when you control the text through a lookup table, as I will here. The storage savings argument isn't really applicable anymore and proper indexing helps to mitigate query time issues (not totally remove them, but it is 6 or 1/2 dozen in Access) with text. The other big problem is working with through 60 tables with 60 decoder rings. I'd rather reference a couple of fields rather than 60-120 tables if I wanted to query all data. It is 60 to 120 depending on where you are querying or referencing, ie whether or not you can rely on Access's automatic behind the scene lookup or you have to write all of the references out.

    In the current system, a 1 can have 60 different meanings and is only valid when directly referencing a table name. That is just plain silly. So if you have 1.brown which 1.brown are you talking about, eyes or hair? I prefer eyes.brown and hair.brown as opposed to eyes.1.brown and hair.1.brown. Plus I do not have to change the data structure to add new data types. I can even add another table to control what the lookup types are.

    So I think I am at the 60 query process to convert the structure as I have not found anything searching here or in Utter Access.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Lookup field Table (2003)

    Either that or write a lot of VBA code...

  7. #7
    Star Lounger
    Join Date
    Sep 2002
    Location
    Indianapolis, Indiana, USA
    Posts
    80
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Lookup field Table (2003)

    That's what I was afraid of. I thought at one time I had seen a way to pass or refence the value in an autolookup, but I can't find it. Probably mixing it up with something else.
    Thanks Hans. I'll get busy on the queries.

Posting Permissions

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