If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
building (i think) a many to many relationship
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 |
#2
|
|||
|
|||
building (i think) a many to many relationship
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. -- PC Datasheet Your Resource For Help With Access, Excel And Word Applications www.pcdatasheet.com "spence" wrote in message ... 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 |
#3
|
|||
|
|||
building (i think) a many to many relationship
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" wrote in message ... 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 |
Thread Tools | |
Display Modes | |
|
|