Help tracking dates of mailings to customers on customer form 2

[rdy4trvl]

This is certainly not a difficult problem (for regular users of Access), but I need suggestions on how to do this efficiently.

We will do mailings to certain customers periodically (using a query to select customers based on specific qualifications). I have a customer form that shows basic customer information (address, phone, etc) for each customer and I would like to show the date of each mail piece sent to each customer on that form. (Mailings only go to certian customers)

The only way I could figure out to track the mailings is to create a append query from the mailing list and create a table of customers for each mailing. I could use that table to feed mailing dates to the customer form - there must be a more effecient way to do this.

Thanks
Doug

 

[dalenz]

Hi Doug,

I would suggest that if you want to track each mailing then you will need a table with the MailingID, CustomerID, and, possibly date unless you keep this in the MailingID table.

What you are creating is a one to many relationship, ie one mailing too many customers.

You could display these details on the customer form with a continuous subform.

Hope this helps,

Dale

 

[rdy4trvl]

Dale,
If I understand, you suggest creating a separate table for each mailing using the append query - correct?
Thanks for the comment on the subforms.
Doug

 

[AlanKW]

I don't think that that's what he's suggesting. Its 1 table that covers all mailings. Storing the at least the Customer ID# and mailing date.

So that way when you do a search for the mailings that Cust# 123 received, you have only 1 table to search and will show all the results.

-alan

 

[rdy4trvl]

Alan,
I think you hit on exactly what my question should have been to start with. How do I convert the customer list query used for the mailing into a table (customer id, mail date 1, mail date 2, etc)? Is the append query the route to take....it would seem there is another step neceesary??
Thanks
Doug

 

[AlanKW]

you would make a seperate query with the same grouping, criteria, etc.. but make it an append to a new MailingHistory table. Then run the query right after you do the mailings.