Another Complex? DCount Question....

[rdjohnso]

I have a complex situation I need help on....


My Goal: Avg Rate = RateSum / PersonCnt
...based on given Org.. like (GWHIS)
and make it work in cell of a report....
will be switching out the Org's

And here are the queries that make up the parts.....


RateSum: Sum of Rate where Date() betwen Rate StDt and EndDt

SELECT Sum(tbl_PersonRate.Rate) AS SumOfRate
FROM tbl_Person INNER JOIN tbl_PersonRate ON tbl_Person.PersonID = tbl_PersonRate.PersonID
HAVING (((Date()) Between [tbl_PersonRate.RateStartDate] And [tbl_PersonRate.RateEndDate]) AND ((tbl_Person.Organization)="GWHIS"));

PersonCnt: Count of Active Persons where Date() betwen Rate StDt and EndDt

SELECT Count([tbl_Person.personID]) AS vCnt2
FROM tbl_Person INNER JOIN tbl_PersonRate ON tbl_Person.PersonID = tbl_PersonRate.PersonID
WHERE (((Date()) Between [tbl_PersonRate.RateStartDate] And [tbl_PersonRate.RateEndDate])
AND ((tbl_Person.Organization)="GWHIS"));


Will provide snapshot of report if needed....


*** *** *** *** *** *** *** *** *** *** *** *** *** ***

Here are a couple of Expressions I have used in other cells that work but not sure how I can modify something like these to get my Avg Sum...

=DCount("*","tbl_Person","Now() Between StartDate And EndDate And Organization='ITS'")


=DLookUp("[BudgetAmount]","rtbl_Budget","[BudgetTitle] = 'TriZetto Maintenance' AND Month([BudgetTimePeriod]) = '2' AND Year([BudgetTimePeriod]) = " & Year(Date()))

*** *** *** *** *** *** *** *** *** *** *** *** *** ***

 

[rdjohnso]

Better yet....

Heres One Query.....
With TOTAL as my needed value to display for the cell
in my report


SELECT Count([tbl_Person.personID]) AS PersonCnt, Sum(tbl_PersonRate.Rate) AS RateSum, [RateSum]/[PersonCnt] AS Total FROM tbl_Person INNER JOIN tbl_PersonRate ON tbl_Person.PersonID = tbl_PersonRate.PersonID
HAVING (((Date()) Between [tbl_PersonRate.RateStartDate] And [tbl_PersonRate.RateEndDate]) AND ((tbl_Person.Organization)="GWHIS"));