Return Minimum value between 5 fields

[SamuelOu]

Hello,

I have a table contains data like following:

Qty1 Qty2 Qty3 Qty4 Qty5
Item1 1 1 4
Item2 3
Item3 2 -1 0 4
Item4 4 2 -2 3 5

Is it possible using query to return the minimum value for each item, as:
Qty
Item1 1
Item2 3
Item3 -1
Item4 -2



I Google this question before posting, and seems the only answer is here: http://support.microsoft.com/kb/209857

However, I don't understand it at all since I am lack of knowledge of SQL.

Any help is appreciated. Thank you.

[June7]

That is not a normalized data structure.

The VBA custom function is one approach.

Could rebuild the db into a normalized structure. Table would be like:

ItemID	Category	Quantity
1	1	1
1	2	1
1	3	4
2	1	3
3	1	2
3	2	-1
3	3
3	4	4
4	1	4
4	2	2
4	3	-2
4	4	3
4	5	5

A UNION query could rearrange the data into normalized structure to emulate that table:

SELECT ItemID, 1 AS Category, Qty1 AS Quantity FROM tablename
UNION SELECT ItemID, 2, Qty2 FROM tablename
UNION SELECT ItemID, 3, Qty3 FROM tablename
UNION SELECT ItemID, 4, Qty4 FROM tablename
UNION SELECT ItemID, 5, Qty5 FROM tablename;

There is no query builder or wizard for UNION, must type into SQL view of query builder.

Then use the table or UNION query in an aggregate (GROUP BY) Totals query

[lfpm062010]

One way,

http://allenbrowne.com/func-09.html

[SamuelOu]

Works perfect. Thanks.