Calculate a daily rate

[nybaseball22]

Hello. I have a query that I am trying to use to calculate a daily
cost rate for something. I am trying to figure out how to take the
date of the first occurance each type of cost (there a 5 different
ones) and divide the total cost to date by the number of days from the
first occurance to today. I have a "Date" field, a "CostType" field,
an "IndividualCost" field, and a "TotalCost" field. I am using a
SumQuery that Groups by CostType. I just don't know how to handle the
date portion to get a daily cost rate.

Thanks in advance for the help.

 

[Michel Walsh]

If there is only five possible occurrences for the cost types, make a query
like:


SELECT costType, MIN(date) AS firstDate
FROM myDataTable
GROUP BY costType



save it under some name, say firstDatesPerCostTypes.


Then, in your working query, bring the previously saved query, and make a
JOIN between your working table and fistDatesPerCostTypes on the their
common field costType. You can compute the desired expression with
something like:

DailyCost: TotalCost / ( Today() - firstDatesPerCostTypes.firstDate)


In SQL view, that may look like:

SELECT ...
TotalCost / ( Today() - firstDatesPerCostTypes.firstDate) AS DailyCost,
...
FROM workingTable INNER JOIN firstDatesPerCostTypes
ON workingTable.costType = firstDatesPerCostTypes.costType





You mentioned making groups, if you do so in the last query, change the
proposed SELECT to:

SELECT ...
TotalCost / ( Today() - LAST(firstDatesPerCostTypes.firstDate)) AS
DailyCost,
...
FROM workingTable INNER JOIN firstDatesPerCostTypes
ON workingTable.costType = firstDatesPerCostTypes.costType
...
GROUP BY workingTable.costType




(where I added a LAST aggregate around firstDatesPerCostTypes.firstDate, in
the SELECT clause )





Hoping it may help,
Vanderghast, Access MVP