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]

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
re******@pcdatasheet.com

"lucky33" <ki*******@bellsouth.netwrote in message
news:11**********************@q75g2000hsh.googlegr oups.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]

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" <[e-mail]:

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
[e-mail]

"lucky33" <[e-mail] in message

news:11**********************@q75g2000hsh.googlegr oups.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]

Yes, that's correct!

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
re******@pcdatasheet.com


"bobh" <b5******@verizon.netwrote in message
news:11**********************@w3g2000hsg.googlegro ups.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" <[e-mail]:

>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
[e-mail]

"lucky33" <[e-mail] in message

news:11**********************@q75g2000hsh.googleg roups.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]

On Jul 26, 4:07 pm, bobh <[e-mail]:

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" <[e-mail]:

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
[e-mail]

"lucky33" <[e-mail] in message

news:11**********************@q75g2000hsh.googlegr oups.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]

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 <[e-mail]:

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.