Calculating averages

I put the formula in the reg footer section. When i run the report it asks
for Total Miles / Total Ltrs in a parameter box. Do you know why? I have also
tried using the txtbox control name but it does the same thing. Cheers

"Marshall Barton" wrote:

TinMan wrote:
Can you help me with a problem. I have a calculated field at query level. It
is KPL: Total Miles/Total Fuel Used. I am trying to get an average for each
driver in a report based on the query. But it asks for KPL whenever i run the
report and then obviously returns a blank field with no errors reported. I
have tried going back to the query and click on average in the totals but it
asks me for KPL when i run the query. What am i doing wrong?


Instead of using Avg(KPL), try:
Avg([Total Miles] / [Total Fuel Used])

--
Marsh
MVP [MS Access]
.

Please use Copy/Paste when posting a problem expression.

If you are being prompted to enter something, then that
something is not a field in the report's record source
query. Maybe you have an extra space, have the [ ] in the
wrong place or maybe you just misspelled the something.

If that's the expression you want to average, I would expect
the report text box's expression to be:
=Avg([Total Miles] / [Total Ltrs])
--
Marsh
MVP [MS Access]


TinMan wrote:
I put the formula in the reg footer section. When i run the report it asks
for Total Miles / Total Ltrs in a parameter box. Do you know why? I have also
tried using the txtbox control name but it does the same thing. Cheers

"Marshall Barton" wrote:
Instead of using Avg(KPL), try:
Avg([Total Miles] / [Total Fuel Used])

TinMan wrote:
Can you help me with a problem. I have a calculated field at query level. It
is KPL: Total Miles/Total Fuel Used. I am trying to get an average for each
driver in a report based on the query. But it asks for KPL whenever i run the
report and then obviously returns a blank field with no errors reported. I
have tried going back to the query and click on average in the totals but it
asks me for KPL when i run the query. What am i doing wrong?

Please use Copy/Paste when posting a problem expression.

If you are being prompted to enter something, then that
something is not a field in the report's record source
query. Maybe you have an extra space, have the [ ] in the
wrong place or maybe you just misspelled the something.

If that's the expression you want to average, I would expect
the report text box's expression to be:
=Avg([Total Miles] / [Total Ltrs])
--
Marsh
MVP [MS Access]


TinMan wrote:
I put the formula in the reg footer section. When i run the report it asks
for Total Miles / Total Ltrs in a parameter box. Do you know why? I have also
tried using the txtbox control name but it does the same thing. Cheers

"Marshall Barton" wrote:
Instead of using Avg(KPL), try:
Avg([Total Miles] / [Total Fuel Used])

TinMan wrote:
Can you help me with a problem. I have a calculated field at query level. It
is KPL: Total Miles/Total Fuel Used. I am trying to get an average for each
driver in a report based on the query. But it asks for KPL whenever i run the
report and then obviously returns a blank field with no errors reported. I
have tried going back to the query and click on average in the totals but it
asks me for KPL when i run the query. What am i doing wrong?

Please use Copy/Paste when posting a problem expression.

If you are being prompted to enter something, then that
something is not a field in the report's record source
query. Maybe you have an extra space, have the [ ] in the
wrong place or maybe you just misspelled the something.

If that's the expression you want to average, I would expect
the report text box's expression to be:
=Avg([Total Miles] / [Total Ltrs])
--
Marsh
MVP [MS Access]


TinMan wrote:
I put the formula in the reg footer section. When i run the report it asks
for Total Miles / Total Ltrs in a parameter box. Do you know why? I have also
tried using the txtbox control name but it does the same thing. Cheers

"Marshall Barton" wrote:
Instead of using Avg(KPL), try:
Avg([Total Miles] / [Total Fuel Used])

TinMan wrote:
Can you help me with a problem. I have a calculated field at query level. It
is KPL: Total Miles/Total Fuel Used. I am trying to get an average for each
driver in a report based on the query. But it asks for KPL whenever i run the
report and then obviously returns a blank field with no errors reported. I
have tried going back to the query and click on average in the totals but it
asks me for KPL when i run the query. What am i doing wrong?

Sorry i forgot to post my expression though it the same as yours
=Avg([Total Miles] / [Total Ltrs]). Though, like i say, it throws up the
parameter dialog box asking for a value for [Total Miles] then [Total Ltrs]
or LGP if i put
=Avg([LPG]). Both fields are derived from calculated fields in my query but
each have the record source from the query so dont undertand why it asking
for values when they there. I have created a footer but i dont get an #error
or #Name just a blank text box. Can you help me figure it out Dale???.
Incidently, this not the 1st time you help me. I needed a parameter date
query to find conflicting holiday requests by staff few months ago and you
got it spot on. I Thanked you but got no reply. But Thanx again. It actually
pretty good!!

"Marshall Barton" wrote:

Please use Copy/Paste when posting a problem expression.

If you are being prompted to enter something, then that
something is not a field in the report's record source
query. Maybe you have an extra space, have the [ ] in the
wrong place or maybe you just misspelled the something.

If that's the expression you want to average, I would expect
the report text box's expression to be:
=Avg([Total Miles] / [Total Ltrs])
--
Marsh
MVP [MS Access]


TinMan wrote:
I put the formula in the reg footer section. When i run the report it asks
for Total Miles / Total Ltrs in a parameter box. Do you know why? I have also
tried using the txtbox control name but it does the same thing. Cheers

"Marshall Barton" wrote:
Instead of using Avg(KPL), try:
Avg([Total Miles] / [Total Fuel Used])

TinMan wrote:
Can you help me with a problem. I have a calculated field at query level. It
is KPL: Total Miles/Total Fuel Used. I am trying to get an average for each
driver in a report based on the query. But it asks for KPL whenever i run the
report and then obviously returns a blank field with no errors reported. I
have tried going back to the query and click on average in the totals but it
asks me for KPL when i run the query. What am i doing wrong?

.

TinMan wrote:

Sorry i forgot to post my expression though it the same as yours
=Avg([Total Miles] / [Total Ltrs]). Though, like i say, it throws up the
parameter dialog box asking for a value for [Total Miles] then [Total Ltrs]
or LGP if i put
=Avg([LPG]). Both fields are derived from calculated fields in my query but
each have the record source from the query so dont undertand why it asking
for values when they there. I have created a footer but i dont get an #error
or #Name just a blank text box. Can you help me figure it out Dale???.
Incidently, this not the 1st time you help me. I needed a parameter date
query to find conflicting holiday requests by staff few months ago and you
got it spot on. I Thanked you but got no reply. But Thanx again. It actually
pretty good!!


When you post a simple thanks to a post, it means that you
have solved your problem and no further discussion is
needed. A you're welcome reply does not add any value to
the thread and is considered a waste of bandwidth.

To your real problem. The aggregate functions (Count, Sum,
Avg, etc) are unaware of controls on a form/report (and
calculated fields in the same query) They ony know about
fields in the form/report's record source table/query. In
other words, you can not use things "derived from calculated
fields in my query", they must actually be straight from the
query. Use the expressions that were used to derive the
values.

--
Marsh
MVP [MS Access]

Thank you for replying again, I take your point on bandwidth does that mean i
cant say thanks. How can i source the original values if my form is based on
a query would i need to use a dlookup. Sorry about the Dale bit he helping
with another problem. One last bit of waste bandwidth. How do i keep track of
my posts. I have asked microsoft but they not help. I find a post then click
my name and seach for recent posts but not ideal. Thanz

"Marshall Barton" wrote:

TinMan wrote:

Sorry i forgot to post my expression though it the same as yours
=Avg([Total Miles] / [Total Ltrs]). Though, like i say, it throws up the
parameter dialog box asking for a value for [Total Miles] then [Total Ltrs]
or LGP if i put
=Avg([LPG]). Both fields are derived from calculated fields in my query but
each have the record source from the query so dont undertand why it asking
for values when they there. I have created a footer but i dont get an #error
or #Name just a blank text box. Can you help me figure it out Dale???.
Incidently, this not the 1st time you help me. I needed a parameter date
query to find conflicting holiday requests by staff few months ago and you
got it spot on. I Thanked you but got no reply. But Thanx again. It actually
pretty good!!


When you post a simple thanks to a post, it means that you
have solved your problem and no further discussion is
needed. A you're welcome reply does not add any value to
the thread and is considered a waste of bandwidth.

To your real problem. The aggregate functions (Count, Sum,
Avg, etc) are unaware of controls on a form/report (and
calculated fields in the same query) They ony know about
fields in the form/report's record source table/query. In
other words, you can not use things "derived from calculated
fields in my query", they must actually be straight from the
query. Use the expressions that were used to derive the
values.

--
Marsh
MVP [MS Access]
.