Help with feature design...

[magmike]

I currently have a Yes/No field for each contact titled "newsletter"
that determines whether a contact receives our newsletter or not (we
also have two other periodically sent out items that have check marks
as well that this applies to). However, I want to be able to send a
custom newsletter to people who get it for the first time. It is not
going to be custom for each individual, just to new recievers. In
order to do that I need to capture the date the check box is selected
so I can run a query each month of the "newbies". I thought about
creating a table for this, and then using an OnClick event to enter a
new record in the table with the contact id and the date and time
clicked on. However, I also realize, that if I uncheck it so they
don't receive it, I need to be able to remove them from this table, or
add a new record that shows them as not to send.

As a hack, I thought I would ask for advice on the best design to
accomplish this goal.

Thanks in advance for your help!

Mike

 

[Clif McIrvin]

I currently have a Yes/No field for each contact titled "newsletter"
that determines whether a contact receives our newsletter or not (we
also have two other periodically sent out items that have check marks
as well that this applies to). However, I want to be able to send a
custom newsletter to people who get it for the first time. It is not
going to be custom for each individual, just to new recievers. In
order to do that I need to capture the date the check box is selected
so I can run a query each month of the "newbies". I thought about
creating a table for this, and then using an OnClick event to enter a
new record in the table with the contact id and the date and time
clicked on. However, I also realize, that if I uncheck it so they
don't receive it, I need to be able to remove them from this table, or
add a new record that shows them as not to send.

As a hack, I thought I would ask for advice on the best design to
accomplish this goal.

Thanks in advance for your help!

Mike

You're likely to get more comprehensive answers than this one, but here
goes:

Lose the checkbox (data in the fieldname is poor database design) and
replace it with a linked child table. The child table could have a
many-to-one relationship back to your contacts table:

NewsLetter child table

newsletterID, autonumber (no-one ever needs to see this)
contactID, long integer, FK to contacts table
newsletter type, text? (identifies which newsletter this defines)
dateadded, date (date this newsletter added to this contact)

you could add other fields that you find useful, perhaps an end date to
automatically prevent this newsletter being sent after a given date,
etc.

Now when you are scanning contacts to generate newsletters if the
corresponding newsletter record exists the newsletter is sent, if not,
not.

HTH