Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2004
    Posts
    54

    Unanswered: 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,
    Cherish

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    Try looking here or here or here.

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    use the DATEADD function
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Wink

    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.


    PS: Ooops, just noticed you mentioned Access...
    I beleive you can e-mail the notices by having Access 'write' to Outlook.
    Good Luck.
    Last edited by LKBrwn_DBA; 01-11-05 at 09:42.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    LKBrwn_DBA, Access doesn't support stored procs
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Thumbs down

    Quote Originally Posted by r937
    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.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  7. #7
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Thumbs up

    PS: Check these links:
    MS Email FAQ and MS ACCESS VB script
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  8. #8
    Join Date
    Aug 2004
    Posts
    54

    Thanks!

    She, and thanks for all your help, I'll try out the suggestions today

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •