IF, Then, Else Dilema (1 Viewer)

[FranD]

I currently have a query that calculates each Department's "Daily Average Charge as follows ([SumOfCharges] / 365). Here's the problem; 2 of the departments are new to the system and don't have 12 months of charges - so we want to divide their total charges by the number of days they've been in the system. One Department has been in the system for 4 months and the other - 2 months. When there was only one new department, I used a simple "If, Then" expression in the query to obtain the average daily charge. But what do I do now that I have 2 exceptions - and therefore 3 scenarios. The calculation needs to take place at the beginning of each month.

Here is the expression currently in my query; =IIf([Div_ID] = "Surg18",[Charges]/123, [Charges]/365).

I'm assuming I'll need a public function, but I don't know how to refer to fields within a table in a VBA function....... I'm at a loss...

Fran

 

[Rich]

Either a nested Iif, or the Switch function will give the results you require, however after twelve months you will have to alter the query criteria, and any if any more departments are added.
It makes sense therefore to calculate from some defined starting point, ie. the first entry date for the current year for each dept. and use a totals query to group by dept.
HTH

 

[Pat Hartman]

Are charges actually posted separately by day for each day? Can some days have no charges and others have multiple charges? A more precise calculation method would be a totals query that summed the charges by division by day for some period. And then a query that computes the average per day using query1 as the recordsource.

Query1:
Select Sum(Charges) As SumOfCharges, ChargeDate, Div_ID
From YourTable
Where ChargeDate Between [Enter Start Date] AND [Enter End Date]
Group By Div_Id, ChargeDate;

Query2:
Select Div_ID, Avg(SumOfCharges) As DailyAvg
From Query1
Group By Div_ID;

Using this method, if there are 5 days with charges in the period for one division and 100 for another. Each average is calculated using the appropriate divisor.