one to one relationships

[gina]

This problem is driving me absolutely nuts, and I can't
seem to find a solution.

In my database I have a table that lists our customers.
I also have a table called contacts, that lists the usual
contacts information. In a third table is a list
of "roles".

For each customer, I need to know which contact fills
each of the roles.

The current setup is each of the above three tables with
a fourth table that selects customer/role/contact. I
think this is called a three-way join.

The problem that I have is that this set up does not show
me when I have an "unfilled" customer/role combination.

Someone please help me before my ulcer gets any worse!!!

Thank you.
Gina

 

[rpw]

Hi Gina,

I'm going to assume that "customers" are businesses and "contacts" are people and "roles" are the job positions the people have in the business.

I'm not sure of the exact nature of the problem. Is it that every customer SHOULD have every role and you want to know which do not? Or is it that you want to know which customer/role combination does not have a contact assigned?

For the latter, query your fourth table with criteria of null for the contact field.

For the former, if EVERY customer must have EVERY role assigned to it, then you can make a cartesian query to create all of those assignments and then update that to the fourth table.

Post back if you need more details.

 

[Steve Schapel]

Gina,

Your table design, as far as I can tell on the basis of the information
you have given so far, is not really practical. If any given Contact
can only be assigned to one Role, then the Roile should be a field in
the Contacts table. If a Contact can be assigned to more than one Role,
then it is correct to have an additional table to record the role
assignments, but it is not correct for the Customer to be in this table,
it should just have fields ContactID and Role.

 

[rpw]

Hi Steve,

If customers can have many roles and roles can have many customers AND contacts can have many customer/roles but customer/roles cannot have many contacts, then wouldn't the four table design work?

On the other hand, if customer/roles is M:M and contacts/CustomerRoles is M:M then five tables are needed.

But at this point there is not that much information provided so we're both at a disadvantage, eh?

 

[Gina]

Hopefully this explanation of my problem will be a little
more clear. Unfortunately I'm still hacking away at this
problem to no avail.

I have 4 tables (simplied examples below)

______Customer Table_______
Customer ID (Key)
Customer Name
Customer Address

______Role Table__________
Role Name (Key)- a 1-1 relationship with Customer Table
key

________Contact Table___________
Contact ID (Key)
Contact Name

________Junction Table___________
Customer ID- lookup to key in Customer table
Role Name- lookup to key in Role table
Contact ID- lookup to key in Contact table

In the junction table, the three lookup fields together
are a primary key.

For each of our customers, my boss wants to know which
person on in our contacts table fills a certain role.
They also want to know when we *don't* know who fills the
role. Roles could be ProjectManager, Consultant,etc.
Roles are defined in our contracts with our customers
depending on the type of project we have with them, and
really have nothing to do with the title of the person
doing the "role".

I need to be able to run a query that will show where
we "don't" have a role/contact/customer entry in the
junction table for each customer/role combination.

This query will be the basis of a form that is used by
our sales or technical force for data entry.


Thanks a million!!!!

 

[Guest]

Hi Steve,
I don't think I answered the relationships question in my
recent post.

A customer has a set number of "roles"
A contact can fill any number of roles at any number of
customers.

for example:

Customer Role Contact
Facility1: Project Manager: Steve May
Facility1: Contract Approver: Jill Roe
Facility 2: Project Manager: Steve May
Facility2: Contract Approver: Bill Foster
Facility2: Project Manager: ???
Facility3: Contract Approver: ???


This is the one report I need to create out of the
existing tables.

The other big function is (I think this might work)
an "append" query to the "junction" table that shows
nulls before we get the known info entered.

 

[rpw]

Do you use the same "Roles" for each customer? ie -someone must be a Project Manager, someone must be a Contract Approver, someone must be a Consultant, etc.?

If the answer is YES, then the relationship between tblCustomer and tblRole is not 1:1, it's M:M.

 

[rpw]

Also, you mention that the junction table has lookup fields. Even though this is possible in Access, most experts will tell you to avoid it and to use numeric long integer fields instead. On the form you can use a combo box to perform the lookup.

Plus, it is usually helpful if you post the actual table and field names as they are in your tables. If what you posted ARE the actual names, then you might consider renaming them without spaces. Here is an example:

tblCustomer
CustomerID (autonumber PK)
CustomerName
CustomerAddress

tblRole
RoleID (autonumber PK)
RoleTitle

 

[Steve Schapel]

Gina,

Ok, thanks for the clarification. I admit to making an assumption that
any Contact would only be associated with one Customer.

Ok, if I now understand you correctly, I would do it like this...

Table: Customers
Customer ID
Customer Name
etc

Table: Contacts
Contact ID
Contact Name
etc

Table: Roles
Role

Table: Assignments
Assignment ID
Customer ID
Contact ID
Role

Then, to get the unassigned roles, a query like this...

SELECT Customers.[Customer ID], Roles.Role
FROM Customers, Roles
WHERE ([Customer ID] & [Role]) Not In (SELECT [Customer ID] & [Role]
FROM Assignments)

 

[rpw]

Just one more thing. The form I suggested in my previous post can be used as a subform to the Customer form, so every time you view a customer it'll be easy to see which roles are not assigned.

And if you ever add a new customer or role, then you can do a new append query with the new items as criteria.

 

[Gina]

I just want you guys to know I really appreciate your
help. I will give this a try today and let you know how
it works! I've just printed the post with the guide!

Gina

-----Original Message-----
The following assumes that the set of roles in the Roles

table must be applied to each customer. Further it
assumes that any contact can be assigned to multiple
CustomerRoles but that one CustomerRole cannot have many
contacts - eg. only one person can be Project Manager for
Facility 1.

I'll list table names as I imagine them based on the

information provided so far. You will probably need to
make some modifications so that it works for you.

Also, it'd be a good idea to try this out on a test db

before you start working with the live one.

tblCustomer
CustID
CustName 'facility 1, facility 2, etc.

tblRole
RoleID
RoleTitle 'Project Manager, Contract Approver, etc.

Delete any relationship between tblCustomer and tblRole

tblContact
ContactID
ContactName

tblCustRoleContact
crcID 'I just like single field PK's - makes it

easier to create relationships....

CustomerID
RoleID
ContactID

Open a new query. Show tblCustomer and tblRole. Drag

the ID field of each down to the grid. Look at the query
in datasheet view. Each Role ID has all of the
customerID. If there are 3 roles and 3 customers, then
there are 9 combinations. Switch back to design view and
change the query to an Append query. Select
tblCustRoleContact as the table to append to.

Close the query and save the name as qcarCustRole. Then run the query.

Create a new form based (initially) on

tblCustRoleContact. Open the properties of the form and
change it to a query of the table. Add the tables for
customer and roles to the query. Drag CustomerName into
the grid and drag RoleTitle into the grid. Close the
query and save it.

Display the CustomerName and RoleTitle fields as text

boxes. Display the ContactID as a combo box based on
tblContacts.

Make the default view of the form Datasheet. Open the

form and you will easily see which customer roles are not
filled by a contact.