building (i think) a many to many relationship

[spence]

I have what I hope is a pretty simple problem. I am
building a simple database for a social services agency
and it consists of two tables. TblCustomer has basic
demographic information about our customers and
TblProvider has similar information about the people who
provide support services to our customers. I need to link
the tables together so I can connect any given customer to
the providers who serve him/her. Some providers serve only
one customer but many serve multiple customers. Being a
novice Access 2000 user, I'm a little unsure how to create
the proper key fields and relationships (many to many i
presume?)in order to generate useful queries. Your
guidance would be appreciated. Thanks.

spence

 

[PC Datasheet]

Here's a start:

TblCustomer
CustomerID
<Name, Address, and other fields specific to a customer>

TblProvider
ProviderID
<Name and other fields specific to a person who provides service>

TblService
ServiceID
<List of services provided>

TblServiceToCustomer
ServiceToCustomerID
CustomerID
ServiceDate
ProviderID
Notes

TblServiceToCustomerDetail
ServiceToCustomerDetailID
ServiceToCustomerID
ServiceID
<Other fields related to a service provided to a customer>

The above tables assumes a provider can provide multiple services to a customer
on a single date. If a provider ALWAYS provides only a single service to a
customer on any date, there is no need for
TblServiceToCustomerDetail and the fields ServiceID and <Other fields related to
a service provided to a customer> can be in TblServiceToCustomer.

 

[lbrinkman]

Spence,

A.) Since one customer can have many providers AND one provider can have
many customers, the "proper" method would be to have 3 tables, turning the 2
many-to-many tables into 3 one-to-many tables:
1.) tblCustomer:
CustomerID
CustomerFirstName
CustomerLastName
etc.

2.) tblProvider:
ProviderID
ProviderFirstName
ProviderLastName
etc.

3.) tblCustomersProviders (a "linking" table)
CustomerID (Key field) highlight both at once and click the
KeyField button
ProviderID (Key field)

Next, create the relationship from tblCustomersProviders TO tblCustomer (as
a one-to-many) using CustomerID (from tblCustomersProviders); then create
the relationship from tblCustomersProviders TO tblProvider (as a
one-to-many) using CustomerID (from tblCustomersProviders). Save the
relationships.

Next, create a form (frmCustomers) based on tblCustomer. Then a create a
query using tblProviders and tblCustomersProviders. Use this query as the
basis for a SUBFORM called SubfrmProviders. Make sure that the 2 fields from
tblCustomersProviders are in the query. Then insert the subform into the
main form. You can even create a combobox on frm Customers which will insert
selected providers into the subform SubfrmProviders.

This 3-table method will prevent any duplication of data, i.e., the
providers will
only be listed once in tblProvider.
====================================================
B.) A "quick and dirty" method would be to use only 2 tables.
1.) tblCustomer:
CustomerID (Key field)
CustomerFirstName
CustomerLastName
etc.

2.) tblProvider:
CustomerID -note that CustomerID is in BOTH tables
ProviderID
ProviderFirstName
ProviderLastName

Then create a one-to-many relationship between tblCustomer (one) and
tblProvider (many). Save the relationship.
Then create a form frmCustomers as before AND a subform (frmProviders).
Insert
the subform frmProviders into frmCustomers. Look at the Properties of
frmProviders
and the "LinkChild", etc. properties should have CustomerID as the field
linking the
frmCustomers and SubfrmProviders.

This will work. HOWEVER, it will allow DUPLICATE entries into tblProvider
(assuming AutoNumber is used for ProviderID. This goes against database
theory. Also, if you print a report of providers, if you go by ProviderID,
it will list duplicates of providers. The "work-around" would be to build
the report based on provider names.
---Phil Szlyk

P.S. I have a great example of this (Version A.) at work, with tblPatients
and tblClinicians. It is very simple and small. However, the news group
frowns on
attachments -- and I am at home right now anyway.