look-up vs relationship

[JulieFall]

I am trying to make a good foundation with my tables and forms and I seem to be alittle confused when to use a look-up or make it a relationship.

Could someone explain the difference between look-up and relationships and when you would want to use each one??

Thanks

Julie

 

[WayneRyan]

Julie,

I'll take a stab at it.

Lookup tables are just collections of information that
pertains to a particular subject. They may be job categories,
payment types, or just about anything. One purpose that they
serve is to keep your data consistent; if you have users type
in values, you'll get: electrishun, electrician, electric guy.
The second thing that they do is save effort, you just select
the field, not type it.

Relationships define how your tables work together. They tell
Access what information in Table a "belong" with what information
in Table B. If my main table has an entry for NY social security
number (SSN), then other tables can use their SSN field to
show that those are MY jobs, relatives, etc. If you define
relationships, then make a new query with multiple tables,
Access will do all of your "joins" for you. It knows which
elements in the different tables belong together.

The relationships, can also define whether you have "cascading"
deletes and updates. If I change my SSN in the main table,
all of "MY" SSNs in the related tables change with it. There
are no "orphans".

Wayne

 

[SAK]

Good explanation Wayne.

Julie, I use look up tables with my forms pretty much exclusively. When you add a combo box you get that wizard that asks if you want to type in the values to look up, or do you wish to use values in an exiting table or query. Using the lookup table/query is so much more efficient at the outset and down the road, if you must edit your lookups.

You can edit if you typed in the data via properties but I find that a bit of a pain.

Hope that two cents helps also.

 

[Pat Hartman]

I seem to be alittle confused when to use a look-up or make it a relationship.

- this isn't an either or situation. These two things are totally separate concepts.

Relationships are defined using the relationship window. Their purpose is to define how tables relate to each other. There most important use is to enforce referential integrity. By enforcing RI, you prevent certain types of bad data from being added to your tables. All properly defined schemas enforce referential integrity.

I'm not sure what you mean by lookups so I'm going to assume that you are talking about comboboxes. The rowsource for a combo can be a table/query or you can type the values right there. The preferred method is to use a table to store the lookup values and a query as the rowsource in the combo to reference them.

The table that you use to store the lookup values should be added to the relaitonships window and connected to the tables where the lookup value will be stored. For example, in the Payment table will be a column called PaymentTypeID. This will be the foreign key to tblPaymentType where all the possible lookup values are defined. When you join these two tables in the relationship window and specify to enforce RI, Access will not allow you to place an invalid PaymentTypeID in the Payment table.

Just as an aside, I NEVER define "lookup"s in my table definitions. I find that they are more trouble than not and they save almost no work. If I need to see the "decoded" values of foreign keys, I use a query which gives me much more control.