LookUp Wizard

Question

Hi

I am trying to build a zoological database using MS Access and I am having a problem sorting out a LookUp Field

Family: Table - Fields: Parent,Taxon Name, Author, Year

SubFamily: Table  - Fields: Parent,Taxon Name, Author, Year

Genus: Table - Fields: Parent,Taxon Name, Author, Year

The parent in the Genus Table could come from either the Taxon Name in the Family Table or the Taxon Name in the Subfamily Table

I can get the LookUp to work from either of the two tables but not both

Regards

Answer 1

That is by design.

Lookup fields may be too restrictive for your situation. Junction tables are more common, and more flexible.

Building such database is a non-trivial matter. We don't know your skill set but this is not for rookies.

It is critical that you get the database design right before you do anything else. You're not the first one to do this, so learn from those who have gone before you. I would expect you spending several days with the search engines to find examples and smaller nuggets and others who have asked for advice.

---

-Tom. Microsoft Access MVP

Answer 2

The parent in the Genus Table could come from either the Taxon Name in the Family Table or the Taxon Name in the Subfamily Table

I never tried this but maybe use a UNION query to combine data from both the Family Table and the Subfamily Table as source.

---

Build a little, test a little

Answer 3

Hi Tom

I suppose I am a rookie, this is my first experience with Access

I use excel all the time

I will keep persevering

Many thanks

Mike

Answer 4

Hi HighlandMike,

is your issue solved now?

if yes, I suggest you to post your solution and mark it as an answer.

if you got the solution from above suggestion then mark it as an answer.

if your issue is still pending then let us update about the latest status of your issue.

provide the detailed steps for your table. you can also post your database. we will try to check it and if possible we will try to provide you solution.

Reference:

Create a lookup field

Regards

Deepak

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 5

When modelling a tree structured hierarchy a fundamental requirement is that the hierarchy be continuous at all levels above the lowest recorded for any specific entity.  If there is a row in the Genus table for which there is no intermediate value in the Subfamily table via which a row in the Family table can be referenced, then the gap must be filled.  This is done by creating a row in the Subfamily table with a value 'N/A' or similar which references the relevant row in the Family table.  The relevant 'N/A' row in the Subfamily table is then referenced by the row in the Genus table, thus maintaining the continuity of the hierarchy.

It follows from this of course that the Subfamily table can contain many 'N/A' rows, each referencing a different row in the Family table.  The column containing the 'N/A' values is therefore not a candidate key.  The usual solution would be to provide the table with a 'surrogate' autonumber key which is then referenced by a foreign key of long integer data type in the Genus table.

---

Ken Sheridan, Stafford, England

Answer 6

Hi HighlandMike,

Did your issue resolved?

If it did, I would suggest you mark the helpful reply as answer.

If not, could you share us your simple database, we will try to reproduce your issue.

Regards,

Tony

---

Help each other