Hi, I am very much a beginner at this so forgive me. Essentially I'm having a problem with lookups of lookups (if that makes any sense). I have a value in a table (call it table A) that I want to be a lookup from another table (table B) and I want the dropdown to show two values (a date and a name) from that table so I can choose the correct one as there can be multiple names on the same day and the same name can be on multiple days. Unfortunately the names field in the lookup table (B) is itself a lookup from another table (table C) and, while it displays the name rather than the primary key from C in table B, on the dropdown for table A it shows only the primary key from C. This makes sense as the PK from C is what is actually stored in B but it doesn't help me choose between options unless I go look at table C and physically work out the names which really defeats the purpose. Any ideas? Do I have do design a query? I really have no idea how to go about doing that... Cheers, Yak
"Nested" lookups
- Thread starter DrYak
- Start date
boblarson
Smeghead
- Local time
- Yesterday, 20:28
- Joined
- Jan 12, 2001
- Messages
- 32,025
Do not use lookups at table level...do not use lookups at table level...
First thing you need to get the concept of is DO NOT USE LOOKUPS DIRECTLY AT TABLE LEVEL! Read this for why:
http://www.mvps.org/access/lookupfields.htm
You can have tables which hold lookup values but you use the actual lookups on FORMS and not in tables. So, the first thing you need to do is to pull all of them out of the tables. See here for how:
http://www.btabdevelopment.com/ts/removelookups
First thing you need to get the concept of is DO NOT USE LOOKUPS DIRECTLY AT TABLE LEVEL! Read this for why:
http://www.mvps.org/access/lookupfields.htm
You can have tables which hold lookup values but you use the actual lookups on FORMS and not in tables. So, the first thing you need to do is to pull all of them out of the tables. See here for how:
http://www.btabdevelopment.com/ts/removelookups
Placing lookups in tables can make it easy to build the forms and reports because Access will transfer the structure to the new object's combos.
However it does not deal well with lookups of lookups and this kind of complexity will completely flumox any attempt to sort it if you have table level lookups.
As Bob (and virtually every professional developer) says it is much better to avoid the lookups in tables so you can directly see what the table is holding.
It is absolutely essential to understand how to structure a combo from first principles so I would advise avoiding the lookups in tables entirely and focus on learning how to do this in your forms.
Moreover I strongly suspect a structure that uses lookups of lookups is not properly normalized.
However it does not deal well with lookups of lookups and this kind of complexity will completely flumox any attempt to sort it if you have table level lookups.
As Bob (and virtually every professional developer) says it is much better to avoid the lookups in tables so you can directly see what the table is holding.
It is absolutely essential to understand how to structure a combo from first principles so I would advise avoiding the lookups in tables entirely and focus on learning how to do this in your forms.
Moreover I strongly suspect a structure that uses lookups of lookups is not properly normalized.
