Help with AVG Function

[lucky33]

I am using the AVG function in a report to show the average number of
cases picked per line item over a given period of time. For instance
the user might want the info from 07/16/07 to 07/20/07.

Can you make the AVG function only use the records that have data in
them. For instance there might be one day between 07/16/07 and
07/20/07 that nothing was picked so the number of cases per line item
would be zero and I do not want Access to calculate that zero amount
in the overall average due to the fact that the averages will show
lower than they actually are on the report.

All help is greatly appreciated.

[Steve]

Re: Help with AVG Function

Suggest doing it a different way ---

Create a query that includes your date, line item and cases picked. Put the
following criteria in the date field:
Between [Enter Info Start Date] And [Enter Info End Date]
With the query in design view, click on the Sigma button (looks like capital
E) in the menu at the top of the screen. Then under cases picked, change
Group By to Avg.
When you run the query you will get the average cases picked in the time
period you entered. Access is smart enough to ignore days with no data when
calculating average.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
resource@pcdata sheet.com



"lucky33" <kimball_k@bell south.netwrote in message
news:1185473052 .347988.168780@ q75g2000hsh.goo glegroups.com.. .

>I am using the AVG function in a report to show the average number of
cases picked per line item over a given period of time. For instance
the user might want the info from 07/16/07 to 07/20/07.
>
Can you make the AVG function only use the records that have data in
them. For instance there might be one day between 07/16/07 and
07/20/07 that nothing was picked so the number of cases per line item
would be zero and I do not want Access to calculate that zero amount
in the overall average due to the fact that the averages will show
lower than they actually are on the report.
>
All help is greatly appreciated.
>

[bobh]

Re: Help with AVG Function

If I remember correctly(but I could be wrong) doesn't Access's AVG
function only throw out 'Null' values and so it will count a value of
zero for calculating the average. The original question(to me)
suggested that they had zero's as data values.
bobh.

On Jul 26, 3:12 pm, "Steve" <So...@private. emailaddresswro te:

Suggest doing it a different way ---
>
Create a query that includes your date, line item and cases picked. Put the
following criteria in the date field:
Between [Enter Info Start Date] And [Enter Info End Date]
With the query in design view, click on the Sigma button (looks like capital
E) in the menu at the top of the screen. Then under cases picked, change
Group By to Avg.
When you run the query you will get the average cases picked in the time
period you entered. Access is smart enough to ignore days with no data when
calculating average.
>
PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
resou...@pcdata sheet.com
>
"lucky33" <kimbal...@bell south.netwrote in message
>
news:1185473052 .347988.168780@ q75g2000hsh.goo glegroups.com.. .
>
>
>

I am using the AVG function in a report to show the average number of
cases picked per line item over a given period of time. For instance
the user might want the info from 07/16/07 to 07/20/07.

>

Can you make the AVG function only use the records that have data in
them. For instance there might be one day between 07/16/07 and
07/20/07 that nothing was picked so the number of cases per line item
would be zero and I do not want Access to calculate that zero amount
in the overall average due to the fact that the averages will show
lower than they actually are on the report.

>

All help is greatly appreciated.- Hide quoted text -

>
- Show quoted text -

[Steve]

Re: Help with AVG Function

Yes, that's correct!

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
resource@pcdata sheet.com




"bobh" <b5o4b9h1@veriz on.netwrote in message
news:1185480476 .552477.246220@ w3g2000hsg.goog legroups.com...

If I remember correctly(but I could be wrong) doesn't Access's AVG
function only throw out 'Null' values and so it will count a value of
zero for calculating the average. The original question(to me)
suggested that they had zero's as data values.
bobh.
>
On Jul 26, 3:12 pm, "Steve" <So...@private. emailaddresswro te:

>Suggest doing it a different way ---
>>
>Create a query that includes your date, line item and cases picked. Put
>the
>following criteria in the date field:
>Between [Enter Info Start Date] And [Enter Info End Date]
>With the query in design view, click on the Sigma button (looks like
>capital
>E) in the menu at the top of the screen. Then under cases picked, change
>Group By to Avg.
>When you run the query you will get the average cases picked in the time
>period you entered. Access is smart enough to ignore days with no data
>when
>calculating average.
>>
>PC Datasheet
>Providing Customers A Resource For Help With Access, Excel And Word
>Applications
>resou...@pcdat asheet.com
>>
>"lucky33" <kimbal...@bell south.netwrote in message
>>
>news:118547305 2.347988.168780 @q75g2000hsh.go oglegroups.com. ..
>>
>>
>>

>I am using the AVG function in a report to show the average number of
cases picked per line item over a given period of time. For instance
the user might want the info from 07/16/07 to 07/20/07.

>>

Can you make the AVG function only use the records that have data in
them. For instance there might be one day between 07/16/07 and
07/20/07 that nothing was picked so the number of cases per line item
would be zero and I do not want Access to calculate that zero amount
in the overall average due to the fact that the averages will show
lower than they actually are on the report.

>>

All help is greatly appreciated.- Hide quoted text -

>>
>- Show quoted text -

>
>

[lucky33]

Re: Help with AVG Function

On Jul 26, 4:07 pm, bobh <b5o4b...@veriz on.netwrote:

If I remember correctly(but I could be wrong) doesn't Access's AVG
function only throw out 'Null' values and so it will count a value of
zero for calculating the average. The original question(to me)
suggested that they had zero's as data values.
bobh.
>
On Jul 26, 3:12 pm, "Steve" <So...@private. emailaddresswro te:
>
>
>

Suggest doing it a different way ---

>

Create a query that includes your date, line item and cases picked. Put the
following criteria in the date field:
Between [Enter Info Start Date] And [Enter Info End Date]
With the query in design view, click on the Sigma button (looks like capital
E) in the menu at the top of the screen. Then under cases picked, change
Group By to Avg.
When you run the query you will get the average cases picked in the time
period you entered. Access is smart enough to ignore days with no data when
calculating average.

>

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
resou...@pcdata sheet.com

>

"lucky33" <kimbal...@bell south.netwrote in message

>

news:1185473052 .347988.168780@ q75g2000hsh.goo glegroups.com.. .

>

>I am using the AVG function in a report to show the average number of
cases picked per line item over a given period of time. For instance
the user might want the info from 07/16/07 to 07/20/07.

>

Can you make the AVG function only use the records that have data in
them. For instance there might be one day between 07/16/07 and
07/20/07 that nothing was picked so the number of cases per line item
would be zero and I do not want Access to calculate that zero amount
in the overall average due to the fact that the averages will show
lower than they actually are on the report.

>

All help is greatly appreciated.- Hide quoted text -

>

- Show quoted text -- Hide quoted text -

>
- Show quoted text -

that is correct we do have zero's as the data values

[bobh]

Re: Help with AVG Function

Hi,
Just insure that your report's source has Null's in place of zero's in
the field your averaging and the AVG function should produce the
correct results.
bobh.

On Jul 26, 2:04 pm, lucky33 <kimbal...@bell south.netwrote:

I am using the AVG function in a report to show the average number of
cases picked per line item over a given period of time. For instance
the user might want the info from 07/16/07 to 07/20/07.
>
Can you make the AVG function only use the records that have data in
them. For instance there might be one day between 07/16/07 and
07/20/07 that nothing was picked so the number of cases per line item
would be zero and I do not want Access to calculate that zero amount
in the overall average due to the fact that the averages will show
lower than they actually are on the report.
>
All help is greatly appreciated.