Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2010

    Question Unanswered: Need Help Database Table / Function

    I am trying to create a database for my business, I think i have created nearly every table and information i need with the exception of one very imporant feature which is the abilty to set Reminders at given intervals

    I am a plumber and require gas appliances to be able to be serviced once a year, landlord certificates to be carried out once a year, oil appliances to be serviced once evey 6 months and some water softener products every 3 months and varying other interval(s)

    You can see my current table design here minus any reminder stuff as i am not sure how to intergrate it,

    What i need to do, is the following

    Store Reminders preferably once i add a job to the job table i can push a button that will generate the reminder for the interval as selected?

    Date for reminder, For Appliance and Or For Job (I.e a Lanldord Certificate) at an interval and then i will genrate a query/report that will generate a postcard and a calling list

    Can anyone assist me with how to lay this data out as being my first databse i am not sure what to do currently to get this part to work

    Thanks in advance

  2. #2
    Join Date
    Mar 2009
    Provided Answers: 15
    Here is a solutions. In a table (e.g. Tbl_Appliances):
    - Add a column (Date/Time): LastServiced, Default value: Date().
    - Add a column (Integer): Periodicity (this will be the periodicity of service in months).
    - In a query, use a Date/Time comparison function such as:
    SELECT Tbl_Appliances.*
    FROM Tbl_Appliances
    WHERE DateDiff("m",Tbl_Appliances.LastServiced, Date()) >= Tbl_Appliances.Periodicity
    If you prefer to compute the periodicity in weeks, it becomes:
    SELECT Tbl_Appliances.*
    FROM Tbl_Appliances
    WHERE DateDiff("ww",Tbl_Appliances.LastServiced, Date()) >= Tbl_Appliances.Periodicity
    Have a nice day!

Posting Permissions

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