I have created a db to act as a CRM software. What I am looking to do is to create email campaigns to assign to a contact. For example, I might have a campaign called "Past Clients" where there would be a series of emails sent periodically to the email address listed for that contact. The letter content would be different for each one, and the time interval would be different (1st letter may be sent 7 days after transaction, 2nd letter will be sent 90 days, etc.)

At this point, I am brainstorming on how to best build this within my db. I have some thoughts on how to make this work, but I need some help...my way seems too cumbersome:

tblLetterContent = Table to hold the letter name, and letter content.
tblCampaignNames = Table to list all the email marketing campaign names.
tblCampaignLetters = Table to connect the correct letters to the appropriate campaigns, and to specify an interval for sending the letters within that campaign.

Here's where I need the help. I need some ideas on the best way to:

1.) Associate what clients are assigned to what campaigns, and/or,
2.) how to "store" all the info on the letters that need to be sent (For example, if I have assigned a contact to a campaign that has 10 letters, I need to somehow automate by db to recognize when a letter needs to be sent in this campaign.),
3.) Also need by db to recognize when a letter within the campaign has already been sent so it will not send it again.

Any suggestions on how to best enact my idea would be great!

First, I'm not sure why you would need tblCampaignnames. if each letter is unique to a campaign then just store that with the letter. If you have standard letters that apply to different campaigsn, then, yes, you eed both and tblCampaignLetters.

You need another table, call it tblCampaignTracker.

CampaignTrackerID (PK Autonumber)
ClientID
CampaignLetterID
DateSent

That will cover 1.)

2.) is done by using the interval against the Date Sent in the above table.

3.) is done by setting a unique index on the combination campaignLetterID and ClientID in the above table.

I guess I was thinking I needed the tblCampaignNames table to make it easy to "assign" a contact to a whole group of letters at one time, rather than having to assign them to each letter.

To be honest, it has been a good while since I've worked with Access to this extent, and I'm probaby missing something obvious here.