Creating a query that creates rows based off a formula

  • Thread starter Thread starter WAstarita
  • Start date Start date
W

WAstarita

I have a simple query setup.

1 table with service contracts.
Each contract comes with a set amount of maintenances.

I want to be able to divide the length of the contract (Usually a year)
by the amount of maintenances.

Then I want it to return a row for each date returned.

Lets say the format of the table is:

ContractID*
DateStart
DateEnd
Maintenances

This may be a Make-Table query, I am not to familiar with them.

Thanks in advance.
 
To do this I would add another table to the database. It is a simple table
consisting of one field that contains the numbers from 1 to an upper limit
that is determined by the maximum number you need. That maximum number
could be the highest number of maintenances.

Table Name: tblNumbers
Field: Counter (key field, numbers from 1 to N with no missing numbers and
no duplicates)

The query then would look something like
SELECT ContractID
, DateDiff("d",DateStart,DateEnd) as ContractLength
, DateDiff("d",DateStart,DateEnd)\Maintenances as PeriodLength
, DateAdd("d",tblNumbers.Counter *
DateDiff("d",DateStart,DateEnd)\Maintenances ,DateStart) as MaintenanceDate
FROM YourTable INNER JOIN tblNumbers
On YourTable.Maintenances <= tblNumbers.Counter
 
Back
Top