lookup column

[chris11590]

i am having trouble understanding lookup columns. is the lookup column (field) in the same table or is the lookup column (field) in a different table which then connects(relationship) to where the lookup column is?

 

[Uncle Gizmo]

I am making the assumption that you have created the look ups when you created the tables.

It's best not to do this because when you look at the table directly you will not see the actual information in the table, but the information that the particular cell/record is linked to.

If my assumption is correct, then I would suggest you create a copy of your database, and in this copy remove the look up instructions from the tables in the table design view, then you will see a correct representation of the data.

 

[chris11590]

Lesson learned

fortunately i did not create the table with the lookup column. i ve learned from past mistakes to just put in enough data to test drive my tables or anything im creating. plus, it didnt make sense to have the lookup column in the same table. but what also doesnt make sense to me is having a lookup table to put the redunant data in a second table. Am i understanding this lookup column field/table info correctly?

 

[Uncle Gizmo]

Let's say you had to include in your database a list of the vehicles, Honda, Mercedes, Citron, Ford, range rover, Lotus, land rover,

Every time you stored one of these text entries in your database you would be increasing the size of your database. The idea of a look up is to store these text values in a separate table, along with a unique ID against each vehicle, then all you need do is store this ID in your main table.

You can access this ID with a correctly set up combo box attached to the field that you wish to record the vehicle.

The other advantage is that you now have a short list of vehicles which is easy to maintain, and add to. Avoiding duplication, allows correct spelling to be maintained throughout the database.

 

[chris11590]

followup lookup column

thank you for all the help but im truly having trouble understanding lookup boxes. i am new to access, and im trying to teach myself from a book and this great forum.

anyhow, can you explain if i would require two tables. the first table would require the list of vehicles and the 2nd table would require the lookup column which would be pulling the values (vehicle list) from the first table.

problems:
1. when i create the a combo box it seems to display the autonumber id number, which is also the primary number. i have the lookup column named "xxxID", which is the second table. and i adjusted properties within that field according to what i am trying to accomplish.

2. when i use the wizard to create the lookup columns, it converts the combo box property to number. i am selecting text.

 

[manny_cb]

not sure what your problem#1 is.. when you draw a combobox.. the wizard would ask you if you want to lookup from a table.. select the table then a column
it will include the autonumber ID as well but you can check the option to hide it so it wont show.. then you select where to store the value and thats it..

about your #2 problem.. yeah i had the same thing.. it gets converted to number .. dont know why but it still stores it as a text

 

[chris11590]

thank you.

problem number one is when you dropped the combo box it displays what looks to be the autonumber from the field of the first table- instead of the other two columns. i did checked off "hide" button. i also changed the bound column in the combo box property to the number of columns within the table.

prob 2. i received an error message stating that im trying to put a text value with numberic value- so im not so fortunate.

 

[chris11590]

i guess what im trying to say about problem one is that the table is displaying the bound value as oppose to the display value.

 

[neileg]

We've got some spreadsheet thinking going on here!

It really doesn't matter what the table view shows. Users don't see tables. Access is a relational database. When you relate two tables, you do this by holding the primary key of the table on the one side of the relationship as a foreign key in the many side table. If your PK is numeric, then the FK field will be numeric. In a form, you join the two tables, either in the underlying query, or via the query embedded in a combo, to show the data you want to see and not the numeric key, or as you say, the bound value as opposed to the display value. That's the way it's supposed to work.

 

[chris11590]

combo box followup

ouch!!

can you tell me specifice how to take do this?

learning from a book and this great forum, sorry

make the primary key a foreign key?

 

[Uncle Gizmo]

Have a look at the video
Convert contacts Spreadsheet to MS Access database 4 --- 5min

which demonstrates how to turn a text box into a combo box, and use that combo box to look up information in the look up table.

 

[Uncle Gizmo]

Alternatively for text and pictures based instructions follow this link here:

Excel in Access (Part 2)



follow the instructions from:
While in design view select the combo box and access the combo box properties press the Ellipsis (…) in the "row source" property box to access the query builder.

 

[neileg]

make the primary key a foreign key?

Say you have two tables, tblParent and tblChild. The PK of tblParent is ParentID. You have a field in tblChild also called ParentID and you hold in there the PK value from the relevant Parent record. This way you know that child 72 has parent 23. This field is a primary key in the parent table but when you holed this value in the child table it is known as a foreign key.