Lookup fields - relationships

[TS in FL]

Thanks to this forum, I have learned the evils of using the "lookup" data
type in tables. Thanks, guys!

I now have a combobox on a form to lookup values from Table B (job title)
and store the value in a field in Table A (employee table). But what happens
if the value looked up in Table B changes (i.e., job title changes)? The
value in Table A is not updated.

Which leads me to ask, am I supposed to create a relationship in the
relationships window after creating a combobox on a form to look up a value?

Thanks in advance for sharing your wealth of knowledge! I have learned so
much from you guys.

 

[Lynn Trapp]

I now have a combobox on a form to lookup values from Table B (job title)

and store the value in a field in Table A (employee table). But what
happens
if the value looked up in Table B changes (i.e., job title changes)? The
value in Table A is not updated.

Depends on what you have in the 2 tables and how you have chosen to set up
your combobox. You should only be storing some kind of ID field from Table B
in Table A. If, on the other hand, you are storing the actual text for the
job title, what you are seeing is what will happen. Table B should probably
look something like this:

JobID JobTitle
1 Salesman
2 Clerk
3 President
4 Vice-President

If you then store the JobID for a given person in Table A and change the
JobTitle in Table B, it will pull the correct information in your queries.

Which leads me to ask, am I supposed to create a relationship in the
relationships window after creating a combobox on a form to look up a
value?

That's probably not necessary for a lookup table.

HTH,

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html

 

[TS in FL]

Great. Thanks for your help!

Depends on what you have in the 2 tables and how you have chosen to set up
your combobox. You should only be storing some kind of ID field from Table B
in Table A. If, on the other hand, you are storing the actual text for the
job title, what you are seeing is what will happen. Table B should probably
look something like this:

JobID JobTitle
1 Salesman
2 Clerk
3 President
4 Vice-President

If you then store the JobID for a given person in Table A and change the
JobTitle in Table B, it will pull the correct information in your queries.


That's probably not necessary for a lookup table.

HTH,

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html