Access Lookup vs Relationship...

[Raincool]

Now, I start my Access journey… but I am really confused with some concepts as a beginner.. eg:
What is the different between lookup and relationship?

As I understood, the lookup function are more or less like the “data validation” function in excel, of course, the validation source can be updated and synched together with other lookup source table. It seems that when I defined the lookup, the relationship is also built.. but I delete the relationship later, and it seems that the lookup connection is still working well.
Then what does the relationship mean? Isn’t lookup and relationship the same thing?

Also, when I defined the lookup in my first database, I defined a lookup connected with multiple items from another table, and in the drop down list, I am able to see all of them, but when I select it, it only shows the primary value in the cell.. is that suppose like that?

Could anyone here explain this for me?
Thanks.

 

[xenou]

Hi,
Relationships are a general concept. Any two tables can be related if they have a common field. You do not have to actually go to the relationships window and "create" a relationship, though that can be useful to do and is necessary if you want to additionally implement referential integrity.

Lookup isn't really a term that is foundational in database theory. It just happens that many people call certain kinds of tables lookup tables - for instance, a table with the names and abbreviations of all the states in the USA could be a lookup table. However, it is essentially just a table like any other - "lookup" simply describes its what it does and how it is used.

For comboboxes or dropdowns, you have to tell Access what field you want to use for the "value" of the combobox, and what field you want to use for the "displayed value". This is in the design properties of the combobox. For instance, you may want to show the description from the lookup table as the displayed value (such as "Alabama"), but use the primary key as the value (such as a numeric primary key). More precisely, you set the bound column as the actual value of the combobox, and you set the column widths as you like: normally, set the primary key column width to zero, so the user doesn't need to see it, and set the descriptive column width to a size that displays the text appropriately.
MS Access: Bind combo box to primary key but display a description field in Access 2003/XP/2000/97

 

[Raincool]

Thanks Xenou.
Does that mean "Lookup" is actually some a part of relationship function?
And as i found out some posts on internet, said, we should try to avoid using lookup...
may i ask why?

 

[SydneyGeek]

Avoid Lookup fields. They create dummy relationships and can cause any number of problems. This link has more... The Access Web - The Evils of Lookup Fields in Tables
If you need a lookup, create an ID field as a foreign key in a table, then on a form, use a combo box to pull the data through from the table whose data you want to look up.

Denis

 

[Raincool]

thanks a lot. now it make sense to me...

 

[merlin777]

Hi,
For comboboxes or dropdowns, you have to tell Access what field you want to use for the "value" of the combobox, and what field you want to use for the "displayed value". This is in the design properties of the combobox.

I can't see a 'value' or 'displayed value' in properties. Where do you set these, please - and in what exactly would you put in those boxes?
tia

 

[SydneyGeek]

See if this summary helps you. Using combo boxes in Access, Part 1 -- DataWright Information Services

Denis

 

[merlin777]

thanks, denis. that's an excellent summary.