max record across multiply fields........?

[iain]

Hi,

This is quite a straight forward request, but for the life
of me I can't get my head around it. Essentially I am
trying to find the max value across 3 fields.

table example:

month apples pears grapes

Jan 5 8 3
Feb 1 2 8
Mar 5 3 2
Apr 9 1 5

I have 4 fields and would like to find the max amount of
fruit per month.

result example.

month fruit
Jan 8
Feb 8
Mar 5
Apr 9

After much fuddling around I am no wiser as to how to
solve this problem. Could anyone point me in the right
direction?

Thanks
Iain

 

[Michel Walsh]

Hi,


The tools supplied with the database are for vertical work. In fact, we say
that your data, actually, is not "normalized", meaning, among other stuff,
it is not presented in the "spirit" of the developers who deliver the tool
you use. With Access, by comparison with Excel, we have a different "room"
for the data and how to work on data (tables and queries) and for its
representation to the user (form). Anyhow, we can easily "normalize" your
data:


SELECT [month] As when, apple As qty, "apple" As fruit FROM notNormalized
UNION ALL
SELECT [month], pear, "pear" FROM notNormalized
UNION ALL
SELECT [month], grape, "grape" FROM notNormalized
....


and save that query (or make it a table, you can add index to a table to
accelerate the operations that comes ).


Next, if you just want the maximum per month, that is now a piece of cake:

Bring the query you just saved in the query designer, click on the summation
button on the toolbar to get the extra line Total in the grid, drag When in
the grid, keep the proposed GroupBy, drag qty field in the grid, change its
GroupBy to Max, that's all. Note that the solution is independent of the
number of fruits you have, and it is "vertically" oriented.


A vertical presentation is poor, for consultation, but terrific, for work.
To "un-normalize" a "vertical" presentation, use a crosstab. The result of
the crosstab would look like your initial data... very nice for
consultation, ... but too often painful to "work" with.


With Access, you have the opportunity to split the data from its view,
make that works for you, rather than against you.

Vanderghast, Access MVP