How to relate one table to two other tables each with a one-to-many relationship?

[Mo Ewing]

I have one table called "Plants" that has a many-to-many relationship with a table called "Locations" through a third table called "PlantsToLocati ons". This setup works fine. I have a form where I can open a location and enter a list of all the plants found there.

Now, the problem is that individual plants may have two (or more) scientific names and I want to be able to search a name field for either name. So I tried to set up a second one-to-many relationship between the "Plants" table and a new table called "Species", but no matter how I attach the Species Table to the Plants Table, I cannot get it to work.

Is it possible attach one table to two other tables each with a one-to-many relationship?

[RuralGuy]

Does every table have an AutoNumber as a PrimaryKey?

[Mo Ewing]

Yes. Species has speciesID as an AutoNumber and PrimaryKey, Plants has one called PlantsID, PlantsToLocatio n has PlantsToLocatio nID, and Locations has LocationsID.

[RuralGuy]

Any chance you can post a picture of your relationships? Something does not sound right.

[Mo Ewing]

Access will produce a PDF of the relationships table but I don't know how to attach it to this message.

[RuralGuy]

After going to Advanced then scroll down to Manage Attachments. If it is too big then ZIP it up first.

[Mo Ewing]

Here is a jpeg of the table relationships.

[RuralGuy]

Why are there two tables with Species_Name fields?

[Mo Ewing]

The Species_Name in the Plants table is where the Species_Name used to be, so that field will be deleted when I get the Species Table set up and working. Sorry, I should have taken it out for this discusion.

[RuralGuy]

You should be able to use an OUTER JOIN (LEFT OR RIGHT) and get all of the Species_Names in your query.

[Mo Ewing]

The querys do work! I think what I was doing was testing the queries by stringing them all together, and when I tried to enter data I got an error message saying,"Field not up-datable". But if I enter data into one query with Species and Plant data, and close it, then I can enter Plant and location data.

In any case, you have solved my problem. Thank you.

Mo

[RuralGuy]

Excellent! Thanks for posting back with your success.