redesign of a database

[gls858]

I have a data base that isn't really working the way I would like
and I think it's due to a design flaw. After reading here
I'm pretty sure it needs to be redone. It pretty simple. We use this
database to track which customers get Christmas cards. I have a
list of customers and a list of employees. Each employee submits
a list of the customers they wish to receive a card. the problem is
that many of us have contact with the same customers. So one customer
might appear on multiple lists. I need to be able to create a list
for each employee to review but also create a list with no duplicates
to mail the cards.

Am I right in assuming that this is a many to many relationship?

I have an Employee table and a customer table but from what I've
read here I need a third table if this is a many to many relationship.

I'm not sure what needs to be in this 3rd table.

The customer table has a primary key that is an autonumber and the
employee table has a primary key that is a EmpID that is a number.
If I under stand correctly these two fields need to make up the
primary key on the third table. Is this correct?

I'm not sure what do from here.

gls858

 

[Jeff Boyce]

It sounds like you are saying one Employee can have many Customers, and one
Customer can be served by many Employees. If so, yes, this is a
many-to-many relationship, and you'll need a junction/relation table to
resolve this. One option would be to use the primary key of each of the
first two as a combined primary key for the junction table. Another option
is to create an autonumber field for the third table and include both
related tables' key values as foreign keys. It depends on what you will do
next with the junction table rows.

By the way, just because you and I served the same customer, what assurance
do you have that the following are the same customer:

John Smith 12345 Elm St
and
J.J. Smith 12345 Elm St SW

Resolve duplicate "persons" is not something Access is very smart about. I
recommend USB (using someone's brain!).

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[gls858]

It sounds like you are saying one Employee can have many Customers, and one
Customer can be served by many Employees. If so, yes, this is a
many-to-many relationship, and you'll need a junction/relation table to
resolve this. One option would be to use the primary key of each of the
first two as a combined primary key for the junction table. Another option
is to create an autonumber field for the third table and include both
related tables' key values as foreign keys. It depends on what you will do
next with the junction table rows.

By the way, just because you and I served the same customer, what assurance
do you have that the following are the same customer:

John Smith 12345 Elm St
and
J.J. Smith 12345 Elm St SW

Resolve duplicate "persons" is not something Access is very smart about. I
recommend USB (using someone's brain!).>
snip<

The customer name is actually a business name coming from another database.
So duplicates aren't problem. But you did make me think that there may be
multiple contacts within each company. Maybe I'll just tell them to stop
sending Christmas..ooops I mean HOLIDAY cards

gls858

 

[Guest]

Assuming each contact works within only one customer company at any one time
you'd need a Customers table and a Contacts table, the latter with a
CustomerID foreign key. To generate your card list each of your employees
you could simply generate a report based on a query which lists all their
selected contacts, which is fine as a paper reference of each employee's
recommendations, but you also need to store the recommendations in a separate
CardList table so that this can be added to cumulatively with each employee's
recommendations. So you'd need to set up a CardList table with columns:

EmployeeID, ContactID

Instead of just producing a report, when an employee makes their selection
they'd insert rows into the above table. To do this you could have an
unbound dialogue form with a combo box which lists all employees by means of
a RowSource:

SELECT EmployeeID, FirstName, LastName
FROM Employees
ORDER BY LastName,FirstName;

Its BoundColumn property would be 1, its ColumnCount 3, its ColumnWidths
something like
0cm;3cm;3cm (experiment for best fit) and its ListWidth 6cm (the sum of the
column widths).

Also on the form you'd have a multiselect list box of contacts for the
employee to select their recommendations from, with a RowSource:

SELECT ContactID, FirstName, LastName, CustomerName
FROM Customers INNER JOIN Contacts
ON Contacts.CustomerID = Customers.CustomerID
ORDER BY CustomerName, LastName, FirstName;

Its BoundColumn property would be 1, its ColumnCount 4, its ColumnWidths
something like
0cm;3cm;3cm;3cm.

The real work would be done with a 'Confirm' button on the form, with code
in its Click event procedure like this:

Dim cmd As ADODB.Command
Dim ctrl As Control
Dim strSQL As String

Set ctrl = Me.lstContacts

' make sure employee has been selected
If IsNull(Me.cboEmployee) Then
MsgBox "Please select an employee.", vbExclamation, "Warning"
Else
If ctrl.ItemsSelected.Count > 0 Then

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

For Each varItem In ctrl.ItemsSelected
' build SQL statement to insert row into CardList table
strSQL = "INSERT INTO CardList(EmployeeID, ContactID) " & _
"VALUES(" & Me.cboEmployee & "," & _
ctrl.ItemData(varItem) & ")"

cmd.CommandText = strSQL
cmd.Execute
Next varItem

' print report of selected contacts
DoCmd.OpenReport "rptCardList", _
WhereCondition:="EmployeeID = " & Me.cboEmployee

Else
MsgBox "No contact(s) selected.", vbExclamation, "Warning
End If
End If

Set cmd = Nothing

The report rptCardList would be based on the following query:

SELECT Employees.EmployeeID,
Employees.FirstName, Employees.LastName,
Contacts.FirstName, Contacts.LastName,
Customers.CustomerName, Customers.Address
FROM Employees, Customers, Contacts, CardList
WHERE Contacts.CustomerID = Customers.CustomerID
AND Contacts.ContactID = CardList.ContactID
AND Employees.EmployeeID = CardList.EmployeeID;

The report could also be opened independently to give a list of all contacts
recommended by all employees, grouped by CustomerName or EmployeeID as you
prefer.

To produce a report (e.g. a labels report) of all recommended contacts
without duplicates the following query would be used:

SELECT DISTINCT
Contacts.FirstName, Contacts.LastName,
Customers.CustomerName, Customers.Address
FROM Customers, Contacts, CardList
WHERE Contacts.CustomerID = Customers.CustomerID
AND Contacts.ContactID = CardList.ContactID;

More than one address field from the Customers table would be included in
the above queries in reality of course.

Once you've finished with the list you just need to delete all rows from the
CardList table ready for it to be filled again next time round. BTW a
Chinese customer of mine told me it was the Chinese Autumn Full Moon Festival
last week, so I hope you remembered to send all your Chinese customers a
Happy Full Moon Card!

Ken Sheridan
Stafford, England

 

[gls858]

Assuming each contact works within only one customer company at any one time
you'd need a Customers table and a Contacts table, the latter with a
CustomerID foreign key. To generate your card list each of your employees
you could simply generate a report based on a query which lists all their
selected contacts, which is fine as a paper reference of each employee's
recommendations, but you also need to store the recommendations in a separate
CardList table so that this can be added to cumulatively with each employee's
recommendations. So you'd need to set up a CardList table with columns:

EmployeeID, ContactID

Instead of just producing a report, when an employee makes their selection
they'd insert rows into the above table. To do this you could have an
unbound dialogue form with a combo box which lists all employees by means of
a RowSource:

SELECT EmployeeID, FirstName, LastName
FROM Employees
ORDER BY LastName,FirstName;

Its BoundColumn property would be 1, its ColumnCount 3, its ColumnWidths
something like
0cm;3cm;3cm (experiment for best fit) and its ListWidth 6cm (the sum of the
column widths).

Also on the form you'd have a multiselect list box of contacts for the
employee to select their recommendations from, with a RowSource:

SELECT ContactID, FirstName, LastName, CustomerName
FROM Customers INNER JOIN Contacts
ON Contacts.CustomerID = Customers.CustomerID
ORDER BY CustomerName, LastName, FirstName;

Its BoundColumn property would be 1, its ColumnCount 4, its ColumnWidths
something like
0cm;3cm;3cm;3cm.

The real work would be done with a 'Confirm' button on the form, with code
in its Click event procedure like this:

Dim cmd As ADODB.Command
Dim ctrl As Control
Dim strSQL As String

Set ctrl = Me.lstContacts

' make sure employee has been selected
If IsNull(Me.cboEmployee) Then
MsgBox "Please select an employee.", vbExclamation, "Warning"
Else
If ctrl.ItemsSelected.Count > 0 Then

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

For Each varItem In ctrl.ItemsSelected
' build SQL statement to insert row into CardList table
strSQL = "INSERT INTO CardList(EmployeeID, ContactID) " & _
"VALUES(" & Me.cboEmployee & "," & _
ctrl.ItemData(varItem) & ")"

cmd.CommandText = strSQL
cmd.Execute
Next varItem

' print report of selected contacts
DoCmd.OpenReport "rptCardList", _
WhereCondition:="EmployeeID = " & Me.cboEmployee

Else
MsgBox "No contact(s) selected.", vbExclamation, "Warning
End If
End If

Set cmd = Nothing

The report rptCardList would be based on the following query:

SELECT Employees.EmployeeID,
Employees.FirstName, Employees.LastName,
Contacts.FirstName, Contacts.LastName,
Customers.CustomerName, Customers.Address
FROM Employees, Customers, Contacts, CardList
WHERE Contacts.CustomerID = Customers.CustomerID
AND Contacts.ContactID = CardList.ContactID
AND Employees.EmployeeID = CardList.EmployeeID;

The report could also be opened independently to give a list of all contacts
recommended by all employees, grouped by CustomerName or EmployeeID as you
prefer.

To produce a report (e.g. a labels report) of all recommended contacts
without duplicates the following query would be used:

SELECT DISTINCT
Contacts.FirstName, Contacts.LastName,
Customers.CustomerName, Customers.Address
FROM Customers, Contacts, CardList
WHERE Contacts.CustomerID = Customers.CustomerID
AND Contacts.ContactID = CardList.ContactID;

More than one address field from the Customers table would be included in
the above queries in reality of course.

Once you've finished with the list you just need to delete all rows from the
CardList table ready for it to be filled again next time round. BTW a
Chinese customer of mine told me it was the Chinese Autumn Full Moon Festival
last week, so I hope you remembered to send all your Chinese customers a
Happy Full Moon Card!

Ken Sheridan
Stafford, England

Ken,
Thanks very much for your detailed help. It will take me some time to work
through all of it. At first glance it seems to be exactly the guidance I
needed. No Chinese customer so I got lucky on that one

OTOH the full moon here was magnificent. Weather was clear and the moon was
so bright I could walk through my woods at night without artificial light!

gls858