Making a new field entry based on a query result

[Guest]

I am a real novice, but making steady progress.

I have created a table of prospect customers (thousands) and written a query
to carve out a subset (hundreds) to use for a mailing. Perhaps I have even
modified the query results to come up with my final mail list. However, I’d
like to indicate somewhere in the data base that I have included them in a
mailing on a specific date and I’m just not sure how to go about it. Perhaps
a new table of mailings that is linked to the prospects that are in the final
query? Could someone please point me in the right direction? Many thanks.

 

[Mike Painter]

I am a real novice, but making steady progress.

I have created a table of prospect customers (thousands) and written
a query to carve out a subset (hundreds) to use for a mailing.
Perhaps I have even modified the query results to come up with my
final mail list. However, I'd like to indicate somewhere in the data
base that I have included them in a mailing on a specific date and
I'm just not sure how to go about it. Perhaps a new table of mailings
that is linked to the prospects that are in the final query? Could
someone please point me in the right direction? Many thanks.

If you want to track just the last mailing add a DateMailed field and run an
update query when done.
If you want to track all the mailings then add a table with ID, CliendID,
DateMailed, and any other fields unique to that mailing (Responded?, made
sale?)

 

[Nikos Yannacopoulos]

Perhaps a new table of mailings that is linked to the prospects that are
in the final query?

Yes, that's the way to go. Assuming there is a ProspCustID field
(Primary Key) in your prospect customers table, you need to create a new
table, say tblMailings, with two fields, ProspCustID (same type as the
field in the existing table) and MailDate (Date/Time). The idea is to
link the two on the ProspCustID field, and each time you do a mailing,
create a record in this table for each customer mailed.

Creating the records can be done very easily through an Append Query like:
INSERT INTO tblMailings ( ProspCustID , MailDate )
SELECT qryMailing.ProspCustID , Date() AS Expr1
FROM qryMailing

where, apart form the name assumptions mentioned abiove, I have also
assumed the query name to be qryMailing (the query through which you get
the prospect customers to mail to). Just change the table/query/field
names to the actual ones, and the query is ready to add the records.
Note: the query assumes you run it on the same day you mail, so the
current date is picked up theorugh the Date() function. If you want to
run it for another date, then simply:

INSERT INTO tblMailings ( ProspCustID , MailDate )
SELECT qryMailing.ProspCustID , #06/18/2005# AS Expr1
FROM qryMailing

With this approach, you keep a record of all your mailings. Furthermore,
you might want to add another table to your design, tblMailers, with a
MailerID and a MailerSubject field (or whatever), and also add a
MailerID field to your tblMailings field. If a given mailer is
definitely sent out only once, then you could add the mailing date field
in tblMailers, and remiove it from tblMailings, reducing the amount of
stored data.

HTH,
Nikos