Mode Function in Access?

[Guest]

I would like to find the mode of seven different fields that contain a value
of 1 to 3. In other words, each of the seven fields will contain a 1, 2, or
3. I want to create an expression in an eighth field that would give me the
value that occurs the most.

Dave

 

[Michel Walsh]

Hi,


Normalize, normalize, normalize....




SELECT TOP 1 thevalue
FROM

( SELECT f1 As the value FROM mytable
UNION ALL
SELECT f2 FROM mytable
UNION ALL
SELECT f3 FROM mytable
...

UNION ALL
SELECT f7 FROM mytable ) As x

GROUP BY theValue
ORDER BY COUNT(*) DESC





The sub select query just normalize your data, nothing else. Once
normalized, see how "standard" the query may look like. Note that you can
still continue to work with not normalized data, that is your problem after
all, not mine, but in general, it is much easier to work with normalized
data.


Hoping it may help,
Vanderghast, Access MVP