Re: redesign of a database

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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" wrote:

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


.

Quantcast