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,
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:
WHERE DateDiff("m",Tbl_Appliances.LastServiced, Date()) >= Tbl_Appliances.Periodicity
If you prefer to compute the periodicity in weeks, it becomes:
WHERE DateDiff("ww",Tbl_Appliances.LastServiced, Date()) >= Tbl_Appliances.Periodicity