Just cannot understand lookup tables?

[dominover]

I thought I understood lookup tables but it appears that I'm even more confused than ever.


For example. Please see below for final comments.

Here is my table. I want to create a lookup combo box in Field2



I use the settings as shown below.


But, when I select the combobox values I get the below result. See how the field names appear in the combobox.
I have selected column 3 (Field 2) to be bound to the combobox in Table1. I have chosen only 1 value to be displayed.
Why is this happening??? I am clearly misunderstanding lookups!

I have a similar problem when I select Table/Query as the Row Source Type. If I'm using a whole table as my lookup table and I select the bound column to be 3 and the number of visible columns to be 1 then I should get the result I'm looking for but instead I can only get the ID Field? Again, I have clearly misunderstood lookups!

[RuralGuy]

They are simply a level of indirection and generally avoided by most developers since they can easily confuse the programmer. Using "lookup's" (comboboxes) on a form are just fine and the preferred design. Using Lookup Fields in a table hides what is really in the table.

[dominover]

They are simply a level of indirection and generally avoided by most developers since they can easily confuse the programmer. Using "lookup's" (comboboxes) on a form are just fine and the preferred design. Using Lookup Fields in a table hides what is really in the table.

Hmm. But that that's where I'm at. Confused..

Can anyone answer this?

[vicsaccess]

let me try and explain what ruralguy is saying. most programmers don't care to know that much about lookup fields because they break rules of normalization. combo boxes on a form display a easy to read value but store a single repetitive value in the table, good storage technique. lookup fields in a table break this rule by displaying a easy to read value in the form but store a unnecessary text field in the table that could have been accomplished with the number "1" or "3", bad storage technique. if you're interested take a look here for some good information.
http://www.rogersaccesslibrary.com/f..._topic238.html

[ssanfu]

Create a new table.
Add these fields

Field name	FieldType
ColorID_PK	Autonumber (PK)
ColorName	Text

Set the field "ColorID_PK" as the primary key field.
Save the table as "tblColors".

Add these colors to the table.
Red, Blue, Yellow, Black, White, Pink

--------------------------------
Create a new table.
Add these fields

Field name	FieldType
AutoID_PK	Autonumber (PK)
ColorID_FK	Number (Long) (FK to tblColors)
AutoMake	Text
AutoModel	Text

Set "AutoID_PK" as the primary key field.
Save the table as "tblAutos". Add these to the table

Make	Model
Chevy	Corvette
Chevy	Camero
Chevy	Serria
Honda	Accord

--------------------------------
Create a query.
The SQL looks like:

SELECT tblAutos.AutoID, tblAutos.AutoMake, tblAutos.AutoModel, tblAutos.AutoColor_FK
FROM tblAutos;

Name the query "qryAutos"

--------------------------------
Create a new form.
Open the properties window for the form.
Click on the DATA tab.
In the RECORD SOURCE property, select "qryAutos".

In the FORMAT tab, change the "Default View" to "Continous Forms".

Right click to highlight "DETAILS". In the menu, select "FORM HEADER/FOOTER"
Add the fields to the DETAILS section.

I click on the Deaign in the menu and click on "Add existing fields". Drag the fields to the DETAILS section. Or you can select them all and drag them.
Select all of the labels and cut them, then paste them in the form header section.
While the labels are selected, click format and change teh font to black.

Arrange the control in a horizontal line at the top of the detail section. Arrange the labels in the header section to be above the associated controls.
Drag the FORM FOOTER up to just below the controls.

Save the form as "frmAutos".

--------------------------------
Open the RELATIIONSHIP WINDOW
Add the table "tblColors" and "tblAutos"
Drag the "tblColors" PK field (ColorID_PK) to the "tblAutos" FK field (ColorID_FK). check "Enforce Referencial Integerity". Close and save the window.

--------------------------------
--------------------------------
Now we are ready to create a combo box to look up a color.

Open the form in design view.
Right click on the "ColorID_FK" control.
Hover over the "Change to" option, then select "Combo box".
Double click on the combo box to open the properties window.

Click on the DATA tab.
Click in the "ROW SOURCE" property.
Click on the ellipse (the 3 dots). Add "tblColors" to the design grid. Drag down the fields "ColorID_PK", then "ColorName" (in that order)
Close and save the query.

Settings:
The "ROW SOURCE" property should be "Table/Query"
The "Bound Column" should be 1. (the first field in the query you just created for the "ROW SOURCE" property)
Click on the FORMAT tab.
Set the 'Column Count" TO 2.
sET THE "COLUMN WIDTHS" to 0,1

Save the form

Open Form1.
You should see the list of Makes and Models, but no colors.
Click on the color combo box for the Camaro. Select "Blue".

Close the form.
Open the table "tblAutos".
You should see a number in the color field. Remember the number.
Open the table "tblColors". Find the number. The color name should be "blue".


Congratulations!! You have just created a look up to a table!



--------------------------------
--------------------------------
One more thing...........

The "ROW SOURCE" property of the combo box has 3 options:
1) "Table/Query" - you can pick a table, a saved query or creat an SQL string
2) "Value List" - you type in the values you want to have available.
3) "Field List" - displays the names of the fields in the "Row Source". If you select "tblColors" as the row source, the field list would be "ColorID_PK" and "ColorName". You would be able to only select "ColorID_PK" or "ColorName".

[dominover]

Thanks for the effort. It wasn't the question but no doubt this will come in handy.

[NTC]

if you bound column is 3; then your 'column widths' property would be: 0,0,1 (1 is width - it can be any number)

actually it may need to be 0,0,0,1 .... I believe the count left-to-right begins with 0 so if you bind the 3rd column then its visibility is the 4th sequentially in the column width property.... can't remember at the moment

what you could do is consider also is in row source - press the ellipses ... and turn it into a query that just contains the field you want; and then your bound field and visible field are the same as there is only 1 field in the record source.

[orange]

dominover,

To add to the info you have already been given, you are dealing with 2 subjects which sound similar but are different.

A Lookup Table is a separate entity that contains a code and some associated information.

example:
UN Countries or areas, codes and abbreviations

This table uniquely identifies Countries or Areas and associates its English Name and the ISO-3166-2 standard 3 character abbreviation.(see attached jpg)

This would be held as a separate table. In any other tables using a Country, the UN numerical code would be a foreign key to the record in this table. To do a lookup of the CountryName, given its numerical code, you would/could use a query.

In a situation using a form, you could have this Lookup table as the rowsource of a combo box. When the user opens the combo, s/he is presented with a list of CountryNames. The idea in this instance is that you have a reference list of Countries and codes. If you design to use such a Lookup Table you will not have spelling mistakes/typos in the CountryNames.

a description from "googling"

A lookup table is normally a table that acts as a "master list" for something and you use it to look up a business key value (like "Make") in exchange for its identifier (like the id column) for use in some other table's foreign key column.

Basically, you come in with something to "look up" and exchange it for something else.