summarizing and finding the max value within a row over 52 column

[Guest]

In an Access database query I have a table/ query with values per week (52
weeks), shown in 52 columns.

I can't find the right way to find to the summary; average and Max -value
within a row.

As I do have next to none knowledge of visual basic, and I can't find the
solution within the design grid, I'm looking for some help on this subject.

Thanks in advance.

 

[Guest]

Hans,

You need to seriously rethink your table structure. Each of your weekly
values should actually be in a single colum, with an additional column to
indicate the week that the value is for, so instead of:

ID Wk1 Wk2 Wk3
X 23 32 19

You would have:

ID SalesWeek Qty
X 1 23
X 2 32
X 3 19

With this structure, you could use Access built-in functions to get the Max,
Min, Average, .... values for across all the weeks, or some subset of weeks.

There is no simple way to do what you want to do. I do have a function that
you could use for the Max and Min across multiple fields, but have to run to
a meeting. I'll try to post back later today with the function.

 

[Marshall Barton]

In an Access database query I have a table/ query with values per week (52
weeks), shown in 52 columns.

I can't find the right way to find to the summary; average and Max -value
within a row.

As I do have next to none knowledge of visual basic, and I can't find the
solution within the design grid, I'm looking for some help on this subject.

The reason you can not find a convenient way to do that is
because your table is designed to look like a spreadsheet.
A relational database like Access needs to be designed
following the rules of database Normalization that has that
kind of data in rows instead of columns. See
http://support.microsoft.com/kb/289533/en-us

At this point, I can only think of four choices you have to
dealing with your problem (in order of decreasing
effectiveness):

1) Normalize your data tables
This will allow the standard database functions to
calculate the desired values in a very straightforward
way. It will also avoid all kinds of other problems you
have not run into yet.

2) Simulate a normalized table by creating a UNION query.
This kind of query will put the weekly values in a
single column:
SELECT pk, 1 As Week, wk1 As WkVal FROM table
UNION ALL
SELECT pk, 2, wk2 FROM table
UNION ALL
. . .
. . .
Then you use that query as the source for a standard
query to calculate the aggregate values:
SELECT pk, Sum(WkVal) As YrTotal,
Avg(WkVal) As YrAvg,
Max(WkVal) As YrMax
FROM qryNorm
GROUP BY pk

Even if you don't exceed the limits on Union queries,
this approach may get you past the current problem,
it may be too slow to be useful and it may not help with
future problems.

3) Move your data to Excel
While this will make other data operations more
difficult, at least you can perform those column
operations using spreadsheet functions.

4) Use some very messy expressions in your query.
For example, the sum of the columns would be:
Nz(wk1,0) + Nz(wk2,0) + . . .
The Max will be especially complex and will probably
exceed several limits for expressions.

 

[John Spencer]

As noted elsewhere in this thread, the problem is your structure. If at
all possible you need to restructure your data. Sometimes it is not
possible to do this. In those cases, you can try the following VBA
function. Copy the code and paste it into a VBA module and save the
module with a name that is NOT fRowMax

Since you need to do this over so many fields (query only allows 29?
parameters in a function call), you will have to nest the calls. That
will look something like the following.

Field: fRowMax([Wk1],[Wk2],...,[wk26],fRowMax([Wk27],...[Wk52]) )

If you need a count of the fields that have a value or the sum or the
average, I do have other functions that can work across rows. I can't
stress too much that you are better off changing your date structure.

'================= Code starts ==============
Public Function fRowMax(ParamArray Values()) As Variant
'John Spencer UMBC CHPDM
'Last Update: April 5, 2000
'Returns the Maximum Number of a group of values passed to it.
'Sample call: myMax = GetMaxNumber("-21","TEST","2", "3",4,5,6,"7",0)
returns 7
'Ignores values that cannot be treated as numbers.

Dim i As Integer, vMax As Variant, tfFound As Boolean, dblCompare As Double

vMax = -1E+308 'very large negative number
For i = LBound(Values) To UBound(Values)
If IsNumeric(Values(i)) Then
dblCompare = CDbl(Values(i))
If dblCompare > vMax Then
vMax = dblCompare
tfFound = True
End If
End If
Next

If tfFound Then
fRowMax = vMax
Else
fRowMax = Null
End If

'================= Code Ends ==============

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================