You really need three tables for this, Employees, CardTypes and CardMailings.
As the others have said Employees would have an EmployeeID column as its
primary key, and column such as Firstname, LastName etc. CardTypes can have
a text CardType column as its primary key as the text values, unlike names,
can't be duplicated.
The CardMailings table models a many-to-many relationship type between the
Employees and CardTypes entity types, so would have columns EmployeeID,
CardType and MailingDate.
You might be wondering why a CardTypes table is needed when the CardMailings
table includes a CardType column. By having a CardTypes table and enforcing
its relationship with CardMailings it prevents an invalid CardType value
being entered in CardMailings. Of course you could also do this by having a
combo box to select the CardType with a Value List of Birthday;Anniversary as
its RowSource. However, if you should decide to add another card type it
would then be necessary to change the design of the form in which the combo
box is located, whereas with a CardTypes table you just insert a new row. It
also violates what's known in the jargon of the relational database model as
the 'information principle' which is (quoting C J Date):
'The entire information content of the database is represented in one and
only one way, namely as explicit values in column positions in rows in
tables'.
For data entry you could have a stand-alone form based on the CardMailings
table with combo boxes for EmployeeID and CardType, and a text box for
MailingDate. The EmployeeID combo box would be set up as follows:
RowSource: SELECT EmployeeID, FirstName & " " & LastName AS FullName
FROM tblEmployees ORDER BY LastName, FirstName;
BoundColum: 1
ColumnCount: 2
ColumnWidths 0cm;8cm
If your units of measurement are imperial rather than metric Access will
automatically convert the last property. The important thing is that the
first dimension is zero to hide the first column and that the second is at
least as wide as the combo box.
The CardType combo box would have a RowSource property of:
SELECT CardType FROM CardTypes ORDER BY CardType;
The other properties can be left as the defaults in this case.
Alternatively you might like to use a continuous view subform based on the
CardMailings table within a single form view main form based on the Employees
table, linked on the EmployeeID columns. You would not need an EmployeeID
combo box in the subform just one fro CardType and a MailingDate text box.
As you go to each employee record the subform will show all cards sent to
that employee and you can enter a new row in the subform whenever a card is
sent to that employee.
To identify employees with upcoming anniversaries or birthdays you could
filter the form in a suitable way, e.g. to filter it to show to show only
employees with a birthday or anniversary in the month following the current
month add a button to the main employees form and out the following code in
its Click event procedure:
Dim strFilter As String
strFilter = "Month(DOH) = " & Month(DateAdd("m",1,VBA.Date)) & _
" Or Month(DOB) = " & Month(DateAdd("m",1,VBA.Date))
Me.Filter = strFilter
Me.FilterOn = True
If you also want a 'Show All' button to clear the filter put the following
in its Click event procedure:
Me.FilterOn = False
This assumes that DOH and DOB are of date/time data type.
If you want to print details of cards sent you could create a query based on
the Employees and CardMailings tables with parameters to select a date range
for the report, and base a report on the query. The query would be something
like this:
PARAMETERS [Start Date:] DATETIME, [End Date:] DATETIME;
SELECT EmployeeID; FirstName & " " & LastName AS FullName,
CardType, MailingDate
FROM Employees INNER JOIN CardMailings
ON Employees.EmployeeID = CardMailings.EmployeeID
WHERE MailingDate BETWEEN [Start Date:] AND [End Date:];
When you open the report you'll be prompted to enter the start and end dates
to define the period you want the report to cover. Note that the parameters
are declared. This is always prudent with parameters of date/time data type
as otherwise a date entered in short date format might be interpreted by
Access as an arithmetical expression and give the wrong results.
Ken Sheridan
Stafford, England
