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?

 

[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?

 

[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?

 

[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.