Hello.  I have a simple "Carpet Cleaning" Access database that I am 

working on.  It has a Billing Address, Site (of carpet cleaning), 

Service Date and Details of Cleaning tables.  My friend wants to be 

able to send mailings to his customers.  He also wants to be able to 

have a way to control monthly, every 3 months, every 6 months, 9  

month and yearly.  (He asks the customer when they would like a 

reminder for the next cleaning to be done.

 

I have "some" experience with VBA.  I assume there must be some kind 

of query I could use.

 

Thanks for your help.

 

Fred

jujutsu101@h...

Fred,



Here's my suggestions



1. Create a table to keep track of mailings.

        Table Name - tblMailings

                Fields: ClientID - Customer's ID No. - This ClientID

would be the same as that in the table where the client's address is

stored.

                          MailingCycle - Numeric - It can only accept

1,3,6,9,12

                          NextMailingDate - Date - Holds the date for

the next mailing



2. When you add a new customer and enter the service date, if the

NextMailingDate is empty, determine if the customer is on the 1,3,6,9,or

12 month mailing cycle and use the function

DateAdd("m",MailingCycle,ServiceDate) to create the initial

NextMailingDate.



3. You can create a MakeTable Query that finds any NextMailingDate that

is less than or equal to today's date.  You could call this table

tblSendMailing.  It would only contain the ClientID in the tblMailings

table.



4. Create a Select Query and join the tblSendMailing table with the

table that is storing the addresses.  Join the tables via the ClientID

fields.



5. Have the newsletter's recordsource be based on the query in step 4.



6. After the newsletters have been printed, create an Update Query.

This query will include the tblSendMailing table and the Select Query

from step 4.  The ClientID field from both tables will be the joined

field.  Update the NextMailingDate for each client based on the Mailing

Cycle.  Your formula would be this: DateAdd("m",MailingCycle,Date()) 



 

I hope this helps

John Ruff - The Eternal Optimist :-) 





-----Original Message-----

From: Fred Crivello [mailto:jujutsu101@h...]

Sent: Tuesday, October 30, 2001 2:02 AM

To: Access

Subject: [access] Record in form deletes but not in 1 of the tables









Hello.  I have a simple "Carpet Cleaning" Access database that I am

working on.  It has a Billing Address, Site (of carpet cleaning),

Service Date and Details of Cleaning tables.  My friend wants to be

able to send mailings to his customers.  He also wants to be able to

have a way to control monthly, every 3 months, every 6 months, 9 

month and yearly.  (He asks the customer when they would like a

reminder for the next cleaning to be done.



I have "some" experience with VBA.  I assume there must be some kind

of query I could use.



Thanks for your help.



Fred

jujutsu101@h...

I will give it a try.  Thanks for your suggestion and help.

Take care.  Fred

You could setup two more fields, ie., Frequency and LastReminder. 

Frequency would hold contact frequency and LastReminder would hold date 

the last remainder was sent to this client.  Then you could design you 

query/report to select all clients whose last remainder has passed or is 

at the period in Frequency field by using DateDiff in the criteria.  For 

instance, >=3D DateDiff("m", [Frequency], Date).  You could have the 

report run at start up once a day or/and have a menu system to print the 

report on demand.



Example:

ClientID:             1111

Frequency:        6   'six monthly remainder

LastReminder:  30/4/2001 'date last remainder was sent

Today's Date:   30/10/2001



Criteria for client 1111will be 6 therefor client 1111 will be selected. 





However, if you ran the report on 29/10/2001, criteria for client 1111 

would be 5 so the client would not be selected.



If by any chance you went on holidays and nobody ran the application and 

you comeback on 1/12/2001, the client 1111 would be also selected then 

because criteria would return 7, greater number then requested Frequency 

(=3D> equal or greater symbol). 





Hope that helps...



____________________________________________________



John Fejsa

Systems Analyst/Computer Programmer

Hunter Centre for Health Advancement

Locked Bag 10

WALLSEND NSW 2287

Phone: (02) 49246 336 Fax: (02) 49246 209

____________________________________________________





>>> jujutsu101@h... 30/10/2001 13:02:18 >>>





Hello.  I have a simple "Carpet Cleaning" Access database that I am

working on.  It has a Billing Address, Site (of carpet cleaning),

Service Date and Details of Cleaning tables.  My friend wants to be

able to send mailings to his customers.  He also wants to be able to

have a way to control monthly, every 3 months, every 6 months, 9 

month and yearly.  (He asks the customer when they would like a

reminder for the next cleaning to be done.



I have "some" experience with VBA.  I assume there must be some kind

of query I could use.



Thanks for your help.



Fred

jujutsu101@h...