Results 1 to 3 of 3
  1. #1
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Lookup Tables Again (any version)

    The thread beginning with <post#=748,116 >post 748,116 </post#> has prompted me to ask about the question of when to use us a numerical lookup, and when a text lookup.

    I insert lookups regularly, and I don't have any definite rules/policies about when to do it each way.

    Just to clarify the question. I have a main table, and for a field in that table I want to provide a combo box of options. The options will come from a lookup table.
    The lookup table can have one field, typically a text field, or it can have two: a number (typically autonumber) , and a text.
    If the lookup table has two fields, the main table field will be a number. If the lookup just has a text field, the field in the main table will be text.

    Case 1 This morning I added a Gender field to a table. Only possible values were to be M or F. I used a text field. Did not even bother with the lookup table - just used a value list instead. ( I am never confident doing this. Someone will always want to add an extra option to the list at some point in the future.)

    Case 2 The same table has a Status field, which can have about 6 possible values, but this list might grow. In this case Status is a number field, because I want to set the status in code sometimes, and I suspect that the wording of the Status descriptions might change in the future. So I can use me.Status =3, without worrying what the exact wording of this status is.

    Case 3 In the same table. Children are being weighed, and a BMI calculated. They will have a BMI status, which can be one of : Underweight, Overweight or Obese. This list will not change and will not grow.
    I put just one field in the lookup table, and made the BMI status field a text field.

    The Issues
    <UL><LI>Numbers are more efficient than text
    <LI>When you use a numerical lookup, you need to add the lookup table to the query, or always use a combo box, to display the field as text.
    <LI>When you use a numerical lookup, query criteria need to test for the numbers. So a query that tests for Status = 3 or Status =4 does make any intuitive sense, until you look up the table to see what 3 or 4 mean....But if you change the text description, the query will still work.
    <LI>If the lookup tabel has any other fields that you will sometimes use, I would use the numerical option. In the same table, I have a "Reason Not Eligible" field. Some of the Reasons were rather long, so I decided to have a short form and a long form of the reason. Normally the short from is displayed, but sometimes the longer version will be displayed: e.g "Receiving ongoing weight management in a secondary or tertiary care program".[/list]What do others do?
    Regards
    John



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

    Re: Lookup Tables Again (any version)

    I always use a lookup table.

    In almost all situations, I will use a number field (usually an AutoNumber field) as primary key and a text field for the descriptions. Even though Status = 1 as condition may be less than intuitive, I prefer it to having to go through the database and the VBA code to see whether Status = "Closed" has to be changed to Status = "Locked".

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Lookup Tables Again (any version)

    Thanks Hans

    In practice I use a lookup table with a numerical key nearly always, and whenever I don't I wonder if I will come to regret taking a lazy shortcut.

    I do quite a bit of work for an organisation that does research projects. Each project needs a database, and often I get to build them, but sometimes I just "help" the project manager do it herself (Yes they are always women, not that that is relevant.) When I put in numerical lookups I am sure they think I am just making life complicated for them.

    One other issue that comes to mind...with numerical lookups, you can end up with a lot query joins, (to display the text fields) so you need to be very aware of the join type and null values in the main table(s) or lots of records can drop out of the results.
    Regards
    John



Posting Permissions

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