Multiple additions to a subform

[David Thompson]

My son-in-law (Horticultural Consultant)wants to have a table with Clients details (Client Table) listed and another table with a list of available plants (Plants Table).
I need a subform that enables me to add to the Client Table a list of plants that they already have from the Plants Table. Do I need an additional table (eg Lookup table)

I'm new at this!!!

 

[Rich@ITTC]

Hi David

Presumably a client can have multiple plant types (so a "one client to many plants relationship" is required) and also a plant type can be supplied to many clients ( so a "one plant type to many clients relationship" is needed). This means you require a many to many relationship.

Access does not permit two tables (plants and clients) to be linked directly in a many to many relationship .. you have to use one to many relationships. However the way round this is to have a bridging or linking table that sits between your two main data tables and stores only the linking ID refernces of clients and plants.

To summarise - you have a client table (tblClient)linked in a one to many relationship with your linking table (via the primary key of the client table).You aslo have a plants table (tblPlant) linked to the same linking table again in a one to many relationship. That will allow a many to many relationship to be created.

HTH

Rich Gorvin

[This message has been edited by Rich@ITTC (edited 09-13-2001).]