Irene:
You'll need three main tables to start with e.g. Contacts (names and
addresses), Groups (one row per group) and Letters (one row per letter type).
To keep tack of which letters each contact has received and when you'd also
need a LetterLog table say.
If a contact can be a member of more than one group simultaneously you'd
need a further table which models the many-to-many relationship between
Contacts and Groups by having two foreign key columns, ContactID and GroupID
say, which reference the primary leys of Contacts and Groups. If they can
only be a member of one group at any one time then you can do without this
table and simply have a GroupID foreign key column in the Contacts Table,
provided you don't need to record the history of which groups each contact
has been in.
Assuming the simpler scenario where a contact is in only one group at any
one time the Contacts table would be related to the Groups table on the
GroupID columns. The Contacts table would also be related to the LetterLog
table on the ContactID columns. Similarly the Letters table would be related
to the LetterLog table on the LetterID columns.
In addition to the above tables you'd also have tables such as Cities,
States (or whatever the regional unit is for the country in question) etc,
the former refernced by a foreign key CityID column in Contacts, the latter
by a foreign key StateID column in Cities.
To determine which contacts receive a letter you would use queries which
identify the relevant contacts. The query can be the RecordSource of a
labels report, and/or of a report which prints the letters themselves (or you
could use a Word mail merge based on the query). For new contacts to receive
letter 1 for instance you could use a query which identifies those contacts
who have not received a letter before. You can also restrict these to
members of a certain group if necessary, so for this example I'll assume you
want to restrict it to members of group ID 1. A query to do this would use a
subquery to find contacts ii group 1 who have no matching rows in the
LetterLog table, e.g.
SELECT Contacts.*
FROM Contacts
WHERE GroupID = 1
AND NOT EXISTS
(SELECT *
FROM LetterLog
WHERE LetterLog.ContactID = Contacts.ContactID);
This type of subquery is 'correlated' to the outer (main) query, which means
it runs once for every row of the outer query; the latter only returns a row
if the subquery finds no rows in the LetterLog table which match the current
ContactID, i.e. contacts wh have never been sent a letter.
As well as identifying the contacts to be sent the letter you need to update
the LetterLog table by inserting one row for each contact sent the letter.
This is done by means of an append query, which would be a variation on the
above query. Assuming the LetterID of the first letter sent value is 1 the
query would insert this value into the LetterID column of one row for every
contact sent the letter, wuth each contact's ContactID being inserted into
the ContactID column in the same rows. Today's date would be inserted into a
LetterDate column. The query to do this would be like this:
INSERT INTO LetterLog
(ContactID,LetterID,LetterDate)
SELECT ContactID, 1, DATE()
FROM Contacts
WHERE GroupID = 1
AND NOT EXISTS
(SELECT *
FROM LetterLog
WHERE LetterLog.ContactID = Contacts.ContactID);
Running this query would append the appropriate rows to the LetterLog table.
As further letters are to be sent you would need to devise queries to
identify the contacts to receive the letters and append queries to insert
rows into the LetterLog table.
In a fully developed application an interface would be built so that the
mailing labels and/or letters can be generated from dialogue forms which
allow the user to make the necessary choices without concerning themselves
with the underlying mechanics of the queries. The forms, reports queries etc
should be in one database file (the front end), with just the tables in
another (the back end) to which the front end links. This not only makes
for easier maintenance but also allows shared use of a single back end on a
server by multiple users, each of whom would have a copy of the front end
(and Access itself) installed on their local machines.
The interface would also include forms for inputting and editing the data,
but they are relatively simple to set up.
You say you are new to Access, so I've probably frightened you enough
already! However, if you take some time initially to understand how a
relational database like Access models real world situations with multiple
related tables, and how the data in those tables can be retrieved and updated
by using queries you should be able to build an application which does what
you require of it. Its very important to spend time considering the correct
'logical model' though; that’s the representation of the real world entities
as a group of tables, the columns (fields) in them and how the tables relate
to each other. Getting the logical model right is the key to a successful
application. If you get it wrong, however, you'll have problems later on, as
an incorrect model is an incorrect reflection of the real world so won't
behave as the entities in the real world do, and you'll end up jumping
through hoops trying to persuade it to do so. The most powerful computer for
getting the model right is the one inside your head, and the most productive
add-in is a good supply of pencils and paper with which you can draw the
model out diagrammatically until you are happy with it.
Ken Sheridan
Stafford, England
