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"));

 

[Pat Hartman]

1. If the criteria is the same, you can do all your aggregates in a single query.
2. Make sure that the criteria is in the correct clause. WHERE is applied to the raw data so it happens BEFORE aggregation. HAVING is applied to the recordset AFTER aggregation. That means that if a field is not included in the final recordset and you want to use it as criteria, you must use a WHERE clause rather than a HAVING clause. I noticed you were checking a date range in a HAVING clause. That won't work since the individual dates are not included in the final recordset.
3. Access always wants to use a HAVING when you create a totals query. You need to switch to SQL view create a WHERE and remove the HAVING. You also need to be aware that switching back to design view might cause Access to change your WHERE back to a HAVING. Once I do this to a query, I never go back to SQL view.

 

[rdjohnso]

OK.... not sure I understand.... maybe If I make it a simpler and shorter ?

2 part question....

I have setup a Query: "rdqry_AvgRate"
screenshot at:
http://www.lnrconsulting.com/misc/Query.jpg

Question 1) in the query I am requiring a parameter "[Org]"
Can I pass this param in my lookup in my cell property.
If I cant, how do I get a param to the query form the Function call?

Question 2) I am trying to figure out if I use DLookup() or
DCount() for each of my cells to get the AvgRate I need.
Which of these do I use and what syntax?

?? =DLookUp("[AvgRate]","rdqry_AvgRate","Org='GWHIS'")
?? =DLookUp("[AvgRate]","rdqry_AvgRate","[Org]='GWHIS'")
?? =DCount("*","rdqry_AvgRate","Org='GWHIS'")

Thx for the help, R

1. If the criteria is the same, you can do all your aggregates in a single query.
2. Make sure that the criteria is in the correct clause. WHERE is applied to the raw data so it happens BEFORE aggregation. HAVING is applied to the recordset AFTER aggregation. That means that if a field is not included in the final recordset and you want to use it as criteria, you must use a WHERE clause rather than a HAVING clause. I noticed you were checking a date range in a HAVING clause. That won't work since the individual dates are not included in the final recordset.
3. Access always wants to use a HAVING when you create a totals query. You need to switch to SQL view create a WHERE and remove the HAVING. You also need to be aware that switching back to design view might cause Access to change your WHERE back to a HAVING. Once I do this to a query, I never go back to SQL view.