checklist question

[Guest]

Hi,

I have a database that has vendor information as well as project
information. Within the project information form we would like to have a
list of the vendors that and be able to check off which ones will each
project be using.

How do I best implement this?

So far this is what I have done:

I created a simple query (qryVendors) that lists all the vendors and used it
to populate a table (tblVendors) which has a checkbox and then dragged that
table over to the project info form (frmProjectInfo). This works if there is
just one project. Problem is, there are multiple projects so I can't keep on
using the same table because the checkbox would have already been used for
another project.

Please help!

Thanks a lot!

 

[Douglas J. Steele]

You've got what's referred to as a many-to-many relationship between the two
tables: a project can apply to more than one vendor, and a vendor can
participate in more than one project.

To resolve that many-to-many, you need to add a new intersection table. That
table would contain the primary key from the Vendor table and the primary
key from the Project table.

Take a look in the Northwind database that comes with Access. This is
similar to how invoices are modelled: an Order can contain many products,
and each Product can appear on many invoices, so a table Order Details was
introduced to resolve that many-to-many between the Orders and Products
tables. From a user interface perspective, what's usually done is to have a
form and subform, like the Orders and Orders Subform in Northwind.