automatically send a notice after 6 months (was "Date Question")

Hello,

I'm trying to write a query that will do the following... I'll give a quick background:

When a machine is installed, I record an installation date. We would like to automatically send a notice to our customers 6 months past this install date regarding scheduled maintenance.

Right now all I have is the notice, which is a report in Access with a query attached that pulls all machine sales that have install dates.

I'm not sure how to reference this situation... or how to start addressing the problem.

Basically:

Customer(CustomerID, CustomerName, CustomerEmail)
MachineDetail(DetailID, CustomerID, ModelID)
MachineModel(ModelID,ModelName)
InstallInfo(InstallID, DetailID, InstallComplete)

Please help :( Thanks,
CherishTry looking here (http://www.1keydata.com/sql/sql.html) or here (http://www.geocities.com/SiliconValley/Vista/2207/sql1.html) or here (http://www.w3schools.com/sql/default.asp).

;)|||use the DATEADD function
select ...
from ...
where InstallComplete
between dateadd("m",-6,date())
and dateadd("m",-5,date()) this will pull all installs between 5 and 6 months old

that way you can prepare the notices manually

sorry, no idea how to set up access to do this automatically

perhaps ask in the access forum|||Create a stored procedure starting with the code posted by r937, that selects the customers you want to send a notice to and either send an e-mail or write the notices to a file.

Create a batch script to execute this procedure ONCE per month, depending on your OS use:
1) Dos cmd and Win2K(XP) task scheduler or 2) Unix KSH and crontab or 3) Oracle jobs.

There have been many posts in this and other forums on how to send e-mail from either Oracle or MS SQL.
:rolleyes:

PS: Ooops, just noticed you mentioned Access...
I beleive you can e-mail the notices by having Access 'write' to Outlook.
Good Luck.|||LKBrwn_DBA, Access doesn't support stored procs|||LKBrwn_DBA, Access doesn't support stored procs
Yeah, I just noticed it was Access, but he can create a VB 'Procedure' or something like that. :(|||PS: Check these links:
MS Email FAQ (http://www.granite.ab.ca/access/email.htm) and MS ACCESS VB script (http://www.febooti.com/products/command-line-email/online-help/send-email-ms-access.html)
;)|||She, and thanks for all your help, I'll try out the suggestions today :)