Can you help me understand lookup table?

[sueviolet]

I have been recently been given an empty database that is in the process of being populated from various data sources. Eventually the db will be used in an GIS and mapped.

I have 2 tables in a one to many relationship - table 2 is a lookup table:

Table 1:

Fields:
Barrier_id
utm_zone
northing
easting
barrier_type (record can only have one type of barrier)
barrier_type_id

Table 2:

Fields:
Barrier_type_id
Description (this field lists 15 possible barriers)
Acronym (acronyms for barriers)


First of all, what exactly is a "lookup table"? What is its purpose?

Could you give me an example of how it would or could be included in queries. What is table 2's function? All it seems to do to me is merely list possible barrier types...
Why is it related to table 2?

I think i am not seeing something here..

Thanks for your help

 

[David R]

Lookup tables store repeatedly used data.

An example is if you had a lookup table for the states of the US. The first column might be their two-letter postal code (the ID field). The Second column might be the full name of the state. The next column might be the date of admission to the union. The next might be the state flower. Then the state bird, and state tree. All these fields only need to be entered ONCE in the lookup table.

Now in your 'data' table, you can store "OK" in a two-space Text field (about 4 bytes). If you need it at any time, you can pull up the full name of the state, the state bird, governor's name, or whatever.

Governor's name is a good example. Jesse Ventura is not running next year for Governor again. So if you'd stored "Jesse Ventura" in 1563 records dealing with Minnesota, you'd have to run an Update Query to update all of those after the election next fall. If you store it in the lookup table, you have to enter it once and all the other records will pull the new, correct data, the next time they look it up.

Does that make more sense now? To be more specific to your case, if you store the [barrier_type_id], then you don't need to store the Acronym (unless it's unique and always will be unique, and thus can be the ID field), or the Long Description. If your ID field is Integer type in Table 1 and Autonumber in Table 2, you've got a good setup. Go into Tools>Relationships and define a One:Many relationship between the two tables, with Referential Integrity enforced, and possibly Cascade Update/Delete.

If [Barrier_type] is encompassed within the [barrier_type_id] information, then you can store that in Table 2 as well, and get rid of a redundant field in Table 1.

Post back if I've thoroughly confused you.

 

[sueviolet]

Thanks for your explaination

I think i understand that look up tables contain values that will be used as the content of a field located in the main table.

Here is the original table design i started with (before I started messing around with it:

Table 1:

Fields:
Barriers_id
utm_zone
easting
northing
type (record containing type of barrier found at a location)

Table 2:

Fields:
barrier_id
barrier_type

Table 3:
barrier_id
description (list 15 possible barriers)
acroymn


That was the original design. I was told by someone on this board that table 2 was unneccesary - a one to many relationship was need instead. (b/c one barrier cannot have many barrier types)


The thing is, i have a excel table that contains informtion related to barriers. (type of barrier, where it is located etc)


I appened this table to table one (i had to add a type field - the excel table had a type info - i need this information)

Where should the excel table field containing info about barrier type go? Where does the lookup table come in in all of this?

Help!

 

[cogent1]

David R's explanation of a lookup table is a more cogent description than the one I posted in a previous thread. What you have to bear in mind is that you only want to enter lengthy data once into your database. By linking the data to other tables by a join between short ID fields, you can display all that data hundreds of times in a form or report. That, as I said, is the essence of a relational database....

BTW, I didn't say there was no need for Table 2. I suggested that it was only necessary if one Barrier could have many Barrier types, which you said was not the case

 

[David R]

I can see why you're confused...

Plus, you've got this topic going here in this thread with me, and also with cogent1 in another thread. So you're receiveing slightly different answers in each (which is why keeping one question to one thought is a good idea).

I agree with cogent that what you need is two tables.

Table 1:
Fields:
Barriers_id
utm_zone
easting
northing
barrier_description (the long description specific to THIS barrier)
barrier_id (an Integer field, linked to your lookup table)

Table 2:

Fields:
barrier_type (list 15 possible barriers)
acroymn
any other data that would just be repeated...

The problem becomes, as you've noticed, your excel table. Is that something you're going to be getting ongoing from outside sources, or is it just where it's been stored up til now?

If it's just been that way til now and you're migrating to Access, great. Take the time to move it into Access properly and you'll have a much easier time in the future.
If this is going to be an ongoing source of information, we may need to help you figure out how to interpret it in an ongoing manner....

 

[sueviolet]

Sorry guys, i didn't mean to switch posts - will stay with this one

I think i am beginning to understand my tables (well...sort of!- still confused to the logic of all this)

I need to get a handle on this, because the db I am inputing data into has many lookup tables (for different tables). This is just the first one i have really examined. I started to question the relationships between my three (now 2) when i input data into table 1 from an excel file - and wondered where the look up table came in to all of this.

If it is ok, i would like to ask more questions tommorrow, as my work day is over - thanks for the help