Count values in multiple fields

[Guest]

Hi there, I have a table with 13 fields. Within each field, could be one of
upto 10 values. I need to write something to count how many occurrences of
each value there are across all 13 fields. Easy to do for one value in one
field, but this way, means 130 pieces of query needed. Is there just an easy
way to search all 13 fields for each value, meaning just a return of 10
figures?

 

[John Spencer]

You could try a normalizing UNION query as the source for a totals query.
The problem is that with 13 fields the union query might bw too complicated
for Access to handle..

Union queries can only be constructed in the SQL window.

SELECT [YourFIELD1]
FROM [YourTable]
UNION ALL
SELECT [YourFIELD2]
FROM [YourTable]
UNION ALL
....
SELECT [YourFIELD12]
FROM [YourTable]
UNION ALL
SELECT [YourFIELD13]
FROM [YourTable]

Save that as qNormalData and then use that in a totals query. In SQL window
that would look like

SELECT [YourField1]
, Count([YourField1]) as RecordCount
FROM [qNormalData]
GROUP BY [YourField1]


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

 

[Michel Walsh]

Normalize your data and it becomes easy. In fact, that is what is intended
by 'normalization'.



SELECT pk AS Originalid, f1 As theField, "f1" as originalColumn FROM
myTable
UNION ALL
SELECT pk, f2, "f2" FROM myTable
UNION ALL
SELECT pk, f3, "f3" FROM myTable
UNION ALL
SELECT pk, f4, "f4" FROM myTable
....
UNION ALL
SELECT pk, f13, "f13" FROM myTable




makes the job. Say it is saved as query Qu1, or as a table (if so, add index
on Originalid), then:

TRANSFORM COUNT(*) AS theCell
SELECT Originalid
FROM qu1
GROUP BY Originalid
PIVOT theField

should produce what you asked.



Hoping it may help,
Vanderghast, Access MVP