OK... simpler question..... 1

[rdjohnso]

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

 

[snyperx3]

1) Where does this Org come from? looks like from your DLookups below that its a field in the query rdqry_AvgRate. If so, you can add that query to the tables included in the query.

2) Is AvgRate a field in the query rdqry_avgrate? If so then ?? =DLookUp("[AvgRate]","rdqry_AvgRate","[Org]='GWHIS'") looks the most correct.

-Pete

 

[rdjohnso]

hey snyperx3... thanks for responding.....

if you look at the screenshot... the view of the query
shows that [Org] is simply a param that the query (rdqry_AvgRate) is expecting before it can run... I figure I could somehow pass that param from my DLookup Function....
iterestingly enough.... the actual field in the db table is called 'Organization'... Somone mentioned I could possibly call the actual field in my DLookup and the query might interpret it.... whatcha think?

Ron

 

[snyperx3]

Yeah. Remove the [Org] from the criteria, and then you can use the DLookUp as such:

=DLookUp("[AvgRate]","rdqry_AvgRate","[Organization]='GWHIS'")

This will return the average rate from rdqry_AvgRate where the organization is GQWHIS.

After looking at the query closer...

Another approach would be to build a query with tblPerson and tblPersonRate linked as in this one, and include the fields PersonID, Organization, Rate, StartDate, and EndDate without any calculations, and then you could use DAvg:

=DAvg("[Rate]","rdqry_QueryName","[Organization]='GWHIS' And [StartDate] < Date() And [EndDate] > Date()")

That way you wouldnt have calculations in your query. But thats just a preference I have, i guess its not important Your way works just fine.

-Pete

 

[rdjohnso]

thx Pete.. will give that a try....

Star for you my man...

R

 

[rdjohnso]

hey Pete... that didnt quite work... any other ideas?...

The DAvg doent work because need to calculated parts to make up the equation for the AvgRate

Here's the DLookup call I am using:
=DLookUp("[AvgRate]","rdqry_AvgRate","[Organization]='GWHIS'")

Here's my SQL now that I took out the Org Criteria...

ELECT [RateSum]/[PersonCnt] AS AvgRate, Count([tbl_Person.personID]) AS PersonCnt, Sum(tbl_PersonRate.Rate) AS RateSum
FROM tbl_Person INNER JOIN tbl_PersonRate ON tbl_Person.PersonID = tbl_PersonRate.PersonID
HAVING (((Date()) Between [tbl_PersonRate.RateStartDate] And [tbl_PersonRate.RateEndDate]));

Any ideas?

Ron

 

[snyperx3]

Don't remove the Organization field, just the criteria. Us this:

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

Then retry the DLookUp the same way as before.

=DLookUp("[AvgRate]","rdqry_AvgRate","[Organization]='GWHIS'")

-------------------------------------------------------

Your dlookup is searching for the average rate when organization = GWHIS. If there isnt a field to compare that against how can it find anything? =]

-Pete

 

[rdjohnso]

snyper...

I get the dreaded ''Organization is not part of an aggregaite function error' using the SQL Statement you provided... I playeed with it for awhile but to no avail...

ideas?

 

[snyperx3]

And how about this?

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

-Pete

 

[rdjohnso]

here's the Db in case u want to look at it...

http://207.195.226.110/lnrconsultingCom/htdocs/misc/BlueWhite.mdb

 

[rdjohnso]

Little variation .. but it finally worked....

Thanks a bunch

Ron

 

[snyperx3]

Back to the DAvg.

I made a query in your database with the SQL and named it qryTest:

SELECT tbl_Person.Organization, tbl_Person.PersonID, tbl_PersonRate.Rate, tbl_PersonRate.RateStartDate, tbl_PersonRate.RateEndDate
FROM tbl_Person INNER JOIN tbl_PersonRate ON tbl_Person.PersonID = tbl_PersonRate.PersonID
WHERE (((tbl_PersonRate.RateStartDate)<Date()) AND ((tbl_PersonRate.RateEndDate)>Date()));

And I used the DAvg as Such:

DAvg("[Rate]","qryTest","[Organization]='GWHIS'")

It returned 124.393939

---------------------------------------------------------

If this is what you want great, otherwise, the problem with your original was that Date was your Field and your criteria was the actual table fields. I made the table fields the fields, and >Date and <Date the criteria.

-Pete

 

[rdjohnso]

very interesting....

the value I get running against my piece is the same value....
what I dont get.... ia how the DAvg() function knows to calculate the Rate / PersonCount

 

[snyperx3]

Its finding the average of [Rate], based upon how many records it finds matching the criteria.

-Pete

 

[rdjohnso]

ah.. that makes sense....

one half dozen or the other... guess your way makes more sense.

Thx,

Ron

 

[snyperx3]

np

-Pete