Here I Have a simple database, with a problem I am trying to resolve, this should be straightforward, but the more I think about it the more my head gets scrambled – Using FM 8.5

The database is this :

I fit Fire Alarms to premises, and they are serviced every 3 months after the installation date, until 12 months has elapsed, the customer then has the option to renew for a further 12 months, again with 3 month servicing and so on

The database I have created is the following

Customer Name

Date of contract Start is a Date field

And 6 calculated fields based on the contract start date

Field 1 (3 Month Service Date) is Contract start date + 90

Field 2 (6 Month Service Date) is Contract start date + 180

Field 3 (9 Month Service Date) is Contract start date + 270

Field 4 (12 Month Service Date ) is Contract start date + 365

Field 5 (Contract renewal letter Date) is contract start date + 330

Field 6 (Next Action due) is the minimum of the above dates

So all works fine for this but the problem I have is the following :

I wish to flag what the minimum date represents as in is it a 3 / 6 /9 / 12 month service or a renewal letter that needs to be produced, I can obviously see the next date of action from the list, but need to label it automaically.

As this is a rolling contract and I have calculated the first 12 months, then apart from defining another load of fields – how do I keep this going on an on-going basis, as in to year 2 / 3 etc as I don’t need to see any completed services from earlier years. I can define a check box that that specifys if a service has been done, but then this date needs to be removed from any printed reports, I also need to specify if a contract has not been renewed to flag a possible follow up a little later.

Any help on this would be appreciated

Thanks David

I think you need at least two tables: Customers and Contracts (related by CustomerID). I would also consider creating a third table for Services (a child of Contracts), but it's possible to manage without it.

Field 6 (Next Action due) is the minimum of the above dates

Surely not - since going by your description, Field6 ALWAYS equals Field1. It seems you need either the next service date that hasn't passed yet, or (preferably, I think) the next service date that hasn't yet been recorded as performed.