Re: building (i think) a many to many relationship
From: lbrinkman (lbrinkman_at_rcn.com)
Date: 05/11/04
- Next message: lbrinkman: "Re: Read Write File"
- Previous message: Van T. Dinh: "Re: Error passing parameters"
- In reply to: spence: "building (i think) a many to many relationship"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 11 May 2004 18:31:14 -0400
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.
"spence" <anonymous@discussions.microsoft.com> wrote in message
news:b6d201c43776$249f8840$a101280a@phx.gbl...
> 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
- Next message: lbrinkman: "Re: Read Write File"
- Previous message: Van T. Dinh: "Re: Error passing parameters"
- In reply to: spence: "building (i think) a many to many relationship"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
