Average Column Values

  • Thread starter mattc66 via AccessMonster.com
  • Start date
M

mattc66 via AccessMonster.com

Hi All,

I have 12 columns representing 12 months usage. I wanted to create a 13
column and use the AVG function.

AVG_SOLD: AVG([QTY_SOLD_P1]+[QTY_SOLD_P2]+[QTY_SOLD_P3]+[QTY_SOLD_P4]+
[QTY_SOLD_P5]+[QTY_SOLD_P6]+[QTY_USED_P7]+[QTY_USED_P8]+[QTY_USED_P9]+
[QTY_USED_P10]+[QTY_USED_P11]+[QTY_USED_P12])

I get an error message saying that 'ITEM' was not included in my expression.
ITEM is another column in the query.

How can I do an average. The issue we have is we don't always have 12 months
of usage on an ITEM it may be a new part and we only have 3-6month. I would
like to keep this in Access and not have to export to Excel.

Does anyone have a recommendation on how I can handle this issue?
 
R

Rob Parker

Hi Matt,

Your problem arises because your data is not normalised. You are
"committing spreadsheet" in your database; and, because it is NOT a
spreadsheet, you cannot do calculations in the same manner as you would in a
spreadsheet.

Your table structure should contain fields such as:
Item
QtySold
DateSold
....

You can then set up a query (a "Totals" query) as follows:
SELECT Item, Avg(QtySold) AS AvgSold
FROM TableName
GROUP BY Item, Year([DateSold]);

This will give you the average quantity sold for each item for each year.
You could use a WHERE clause to limit the year to a particular year, if
desired, eg:
SELECT Item, Avg(QtySold) AS AvgSold
FROM TableName
GROUP BY Item, Year([DateSold])
HAVING Year([DateSold]) = 2007;

This will also automatically take care of null values in any given month -
there will not be any record in the table for that month.

Trying to greate an expression for an average on your non-normalised data
will be extremely difficult, and I'm not prepared to try. I doubt if anyone
else will bother either.

HTH,

Rob
 
M

mattc66 via AccessMonster.com

I wish it was normal, but the data comes from our Inventory software and that
is how I get it.

Rob said:
Hi Matt,

Your problem arises because your data is not normalised. You are
"committing spreadsheet" in your database; and, because it is NOT a
spreadsheet, you cannot do calculations in the same manner as you would in a
spreadsheet.

Your table structure should contain fields such as:
Item
QtySold
DateSold
...

You can then set up a query (a "Totals" query) as follows:
SELECT Item, Avg(QtySold) AS AvgSold
FROM TableName
GROUP BY Item, Year([DateSold]);

This will give you the average quantity sold for each item for each year.
You could use a WHERE clause to limit the year to a particular year, if
desired, eg:
SELECT Item, Avg(QtySold) AS AvgSold
FROM TableName
GROUP BY Item, Year([DateSold])
HAVING Year([DateSold]) = 2007;

This will also automatically take care of null values in any given month -
there will not be any record in the table for that month.

Trying to greate an expression for an average on your non-normalised data
will be extremely difficult, and I'm not prepared to try. I doubt if anyone
else will bother either.

HTH,

Rob
Hi All,
Click to expand...
[quoted text clipped - 16 lines]
Does anyone have a recommendation on how I can handle this issue?
Click to expand...
Click to expand...
 
R

Rob Parker

In that case, I'd suggest doing one of two things:

1. Set up some append queries to transfer the data to a normalised table
and proceed as I suggested.
or
2. Don't bother importing into Access; import into Excel and use Excel
formulae to get what you want.

Again, HTH,

Rob


mattc66 via AccessMonster.com said:
I wish it was normal, but the data comes from our Inventory software and
that
is how I get it.

Rob said:
Hi Matt,

Your problem arises because your data is not normalised. You are
"committing spreadsheet" in your database; and, because it is NOT a
spreadsheet, you cannot do calculations in the same manner as you would in
a
spreadsheet.

Your table structure should contain fields such as:
Item
QtySold
DateSold
...

You can then set up a query (a "Totals" query) as follows:
SELECT Item, Avg(QtySold) AS AvgSold
FROM TableName
GROUP BY Item, Year([DateSold]);

This will give you the average quantity sold for each item for each year.
You could use a WHERE clause to limit the year to a particular year, if
desired, eg:
SELECT Item, Avg(QtySold) AS AvgSold
FROM TableName
GROUP BY Item, Year([DateSold])
HAVING Year([DateSold]) = 2007;

This will also automatically take care of null values in any given month -
there will not be any record in the table for that month.

Trying to greate an expression for an average on your non-normalised data
will be extremely difficult, and I'm not prepared to try. I doubt if
anyone
else will bother either.

HTH,

Rob
Hi All,
Click to expand...
[quoted text clipped - 16 lines]
Does anyone have a recommendation on how I can handle this issue?
Click to expand...
Click to expand...
Click to expand...
 
R

Rob Parker

Or a third possibility:

Investigate whether the inventory software can dump the data in a normalised
form, and use that. The format you appear to have could well have come from
a crosstab query on normalised data in the Inventory system

Rob

Rob Parker said:
In that case, I'd suggest doing one of two things:

1. Set up some append queries to transfer the data to a normalised
table and proceed as I suggested.
or
2. Don't bother importing into Access; import into Excel and use Excel
formulae to get what you want.

Again, HTH,

Rob


mattc66 via AccessMonster.com said:
I wish it was normal, but the data comes from our Inventory software and
that
is how I get it.

Rob said:
Hi Matt,

Your problem arises because your data is not normalised. You are
"committing spreadsheet" in your database; and, because it is NOT a
spreadsheet, you cannot do calculations in the same manner as you would
in a
spreadsheet.

Your table structure should contain fields such as:
Item
QtySold
DateSold
...

You can then set up a query (a "Totals" query) as follows:
SELECT Item, Avg(QtySold) AS AvgSold
FROM TableName
GROUP BY Item, Year([DateSold]);

This will give you the average quantity sold for each item for each year.
You could use a WHERE clause to limit the year to a particular year, if
desired, eg:
SELECT Item, Avg(QtySold) AS AvgSold
FROM TableName
GROUP BY Item, Year([DateSold])
HAVING Year([DateSold]) = 2007;

This will also automatically take care of null values in any given
month -
there will not be any record in the table for that month.

Trying to greate an expression for an average on your non-normalised data
will be extremely difficult, and I'm not prepared to try. I doubt if
anyone
else will bother either.

HTH,

Rob

Hi All,

[quoted text clipped - 16 lines]

Does anyone have a recommendation on how I can handle this issue?
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

You could always use a VBA function to get the average. The following
would work in a query as long as you didn't pass it more than 29
arguments at one time.

AVG_SOLD: fRowAverage([QTY_SOLD_P1],[QTY_SOLD_P2],[QTY_SOLD_P3],
[QTY_SOLD_P4],[QTY_SOLD_P5],[QTY_SOLD_P6],[QTY_USED_P7],
[QTY_USED_P8],[QTY_USED_P9],[QTY_USED_P10],[QTY_USED_P11],[QTY_USED_P12])



Paste this function into a VBA module and save it. Do NOT save the
module with the same name as the function.
Public Function fRowAverage(ParamArray Values())
'John Spencer UMBC CHPDM
'Last Update: April 5, 2000
'Calculates the arithmetic average (mean) of a group of values passed to it.
'Sample call: myAvg = GetMeanAverage("1","TEST","2", "3",4,5,6,0)
returns 3 (21/7)
'Ignores values that cannot be treated as numbers.
'
' Max of 29 arguments can be passed to a function in Access SQL

Dim i As Integer, intElementCount As Integer, dblSum As Double
intElementCount = 0
dblSum = 0

For i = LBound(Values) To UBound(Values)
If IsNumeric(Values(i)) Then 'Ignore Non-numeric values
dblSum = dblSum + Values(i)
intElementCount = intElementCount + 1
End If
Next i

If intElementCount > 0 Then
fRowAverage = dblSum / intElementCount 'At least one number in
the ' group of values
Else
fRowAverage = Null 'No number in the group of values
End If

End Function
 
M

mattc66 via AccessMonster.com

Thanks I will give that a shot.

John said:
You could always use a VBA function to get the average. The following
would work in a query as long as you didn't pass it more than 29
arguments at one time.

AVG_SOLD: fRowAverage([QTY_SOLD_P1],[QTY_SOLD_P2],[QTY_SOLD_P3],
[QTY_SOLD_P4],[QTY_SOLD_P5],[QTY_SOLD_P6],[QTY_USED_P7],
[QTY_USED_P8],[QTY_USED_P9],[QTY_USED_P10],[QTY_USED_P11],[QTY_USED_P12])

Paste this function into a VBA module and save it. Do NOT save the
module with the same name as the function.
Public Function fRowAverage(ParamArray Values())
'John Spencer UMBC CHPDM
'Last Update: April 5, 2000
'Calculates the arithmetic average (mean) of a group of values passed to it.
'Sample call: myAvg = GetMeanAverage("1","TEST","2", "3",4,5,6,0)
returns 3 (21/7)
'Ignores values that cannot be treated as numbers.
'
' Max of 29 arguments can be passed to a function in Access SQL

Dim i As Integer, intElementCount As Integer, dblSum As Double
intElementCount = 0
dblSum = 0

For i = LBound(Values) To UBound(Values)
If IsNumeric(Values(i)) Then 'Ignore Non-numeric values
dblSum = dblSum + Values(i)
intElementCount = intElementCount + 1
End If
Next i

If intElementCount > 0 Then
fRowAverage = dblSum / intElementCount 'At least one number in
the ' group of values
Else
fRowAverage = Null 'No number in the group of values
End If

End Function
Click to expand...
 
Top