Correspondence tracking

[Lee]

Hi

Sorry if this sounds like a dumb question but I'm pretty
new to Access (2000).

I just want to create a small system that will let me:

1. Enter client address details
2. Have three or four standard report letters
3. Do mailshots to people in certain postcodes
*4. Keep a record of WHICH letters have been sent to
WHICH people.

As you've probably guessed - it's step four that I'm
falling over on....

Each report letter is based on a query of the client
details table and retrieves (via a parameter query) the
right people in the right postcodes - which I can then
print out. I just need some way of recording the fact
that each of these people has been sent a letter. I have
a seperate table called 'correspondence_tracking' which
allows me to keep records of all letters sent out but I
don't want to have to update this manually...

Anyone done this kind of thing before or know of where I
can get info ?

Best, Lee (UK)

 

[Gary Miller]

Lee,

The easiest and simplest way is to use either an Update or an Append query
using the same criteria you used to generate who got the letters to run.
Which you use will depend on how you setup your correspondence tracking
table.

Gary Miller

 

[John Nurick]

Hi Lee,

I'd do this with three tables in addition to the client details table
(which I'll call tblClients):

tblLetters
LetterID - Autonumber, primary key
ReportName - Text, the name of the report
maybe some other fields to help you keep track
of changes to the reports

tblMailshots
MailshotID - Autonumber, primary key
LetterID - Long, foreign key into tblLetters
MailshotDate - Date, the date of the mailing
maybe some other fields relating to this particular
mailing

Each letter-report may be used in multiple mailshots, hence the need for
two tables with a one-to-many relationship.

tblMailshotItems
ItemID - Autonumber, primary key
MailshotID - Long, foreign key into tblMailshots
ClientID - foreign key into tblClients.

Each record in tblMailshotItems stores the fact that one client was
included in one mailing. So all you need to do after you've printed off
the letters for a mailshot is (1) record the fact by adding a record to
tblMailshots and (2) add new records to tblMailshotItems with an append
query something like this:

INSERT INTO tblMailshotItems (MailshotID, ClientID)
SELECT XXX AS MailshotID, ClientID
FROM tblClients
WHERE YYY;

where XXX is the ID of the new record in tblMailshots and YYY is the
same criterion you used to select the clients when you printed the
letters.

Hi

Sorry if this sounds like a dumb question but I'm pretty
new to Access (2000).

I just want to create a small system that will let me:

1. Enter client address details
2. Have three or four standard report letters
3. Do mailshots to people in certain postcodes
*4. Keep a record of WHICH letters have been sent to
WHICH people.

As you've probably guessed - it's step four that I'm
falling over on....

Each report letter is based on a query of the client
details table and retrieves (via a parameter query) the
right people in the right postcodes - which I can then
print out. I just need some way of recording the fact
that each of these people has been sent a letter. I have
a seperate table called 'correspondence_tracking' which
allows me to keep records of all letters sent out but I
don't want to have to update this manually...

Anyone done this kind of thing before or know of where I
can get info ?

Best, Lee (UK)

John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

 

[Lee]

Many, many thanks for going to so much trouble to detail
things for me Joh. I'll be sure to follow your steps
through !

Best, Lee

-----Original Message-----
Hi Lee,

I'd do this with three tables in addition to the client details table
(which I'll call tblClients):

tblLetters
LetterID - Autonumber, primary key
ReportName - Text, the name of the report
maybe some other fields to help you keep track
of changes to the reports

tblMailshots
MailshotID - Autonumber, primary key
LetterID - Long, foreign key into tblLetters
MailshotDate - Date, the date of the mailing
maybe some other fields relating to this particular
mailing

Each letter-report may be used in multiple mailshots, hence the need for
two tables with a one-to-many relationship.

tblMailshotItems
ItemID - Autonumber, primary key
MailshotID - Long, foreign key into tblMailshots
ClientID - foreign key into tblClients.

Each record in tblMailshotItems stores the fact that one client was
included in one mailing. So all you need to do after you've printed off
the letters for a mailshot is (1) record the fact by adding a record to
tblMailshots and (2) add new records to tblMailshotItems with an append
query something like this:

INSERT INTO tblMailshotItems (MailshotID, ClientID)
SELECT XXX AS MailshotID, ClientID
FROM tblClients
WHERE YYY;

where XXX is the ID of the new record in tblMailshots and YYY is the
same criterion you used to select the clients when you printed the
letters.

Hi

Sorry if this sounds like a dumb question but I'm pretty
new to Access (2000).

I just want to create a small system that will let me:

1. Enter client address details
2. Have three or four standard report letters
3. Do mailshots to people in certain postcodes
*4. Keep a record of WHICH letters have been sent to
WHICH people.

As you've probably guessed - it's step four that I'm
falling over on....

Each report letter is based on a query of the client
details table and retrieves (via a parameter query) the
right people in the right postcodes - which I can then
print out. I just need some way of recording the fact
that each of these people has been sent a letter. I have
a seperate table called 'correspondence_tracking' which
allows me to keep records of all letters sent out but I
don't want to have to update this manually...

Anyone done this kind of thing before or know of where I
can get info ?

Best, Lee (UK)

John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.