counting within a record

[Karen]

Hi,

Is it possible to count items within a record? For example: I have 12 items
that are classified according to size (i.e. small, medium, large). This
information is entered into 12 fields (i.e. field1, field2, etc.). I would
like to know for each record how many of the 12 items were classified as big.

Any suggestions would be appreciated. Thanks.

 

[Duane Hookom]

I would normalize the table structure. If you can't do that, consider
creating a normalizing union query [quniItemSizes]:
SELECT PkField, Field1 as TheSize, 1 as TheItem
FROM tblNoName
UNION ALL
SELECT PkField, Field2, 2
FROM tblNoName
UNION ALL
SELECT PkField, Field3, 3
FROM tblNoName
UNION ALL
-- etc
SELECT PkField, Field12, 12
FROM tblNoName;

You can then query this
SELECT PkField, Count(*) as NumOf
FROM quniItemSizes
WHERE TheSize = "big"
GROUP BY PkField;