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.