Query to display number count

[sunilkeswani]

Hi

I am still new to access. I want to know how i can build a query which
can display results from 4 different columns/fields

Like.

Field1 Field2 Field3 Field4
1 2 1 0
1 1 0 0
2 0 0 0
3 0 0 0

The query should output

Number Count
0 9
1 4
2 2
3 1
(i.e) Query checks for the number of occurences in each of the 4
columns and returns the count.

Please advise

Thanks !!

[jimfortune]

su**********@gmail.com wrote:

Hi

I am still new to access. I want to know how i can build a query which
can display results from 4 different columns/fields

Like.

Field1 Field2 Field3 Field4
1 2 1 0
1 1 0 0
2 0 0 0
3 0 0 0

The query should output

Number Count
0 9
1 4
2 2
3 1
(i.e) Query checks for the number of occurences in each of the 4
columns and returns the count.

Please advise

Thanks !!

A two query approach assuming your table is called tblMyTable (edit
text if otherwise):

qryAllNumbers:
SELECT Field1 AS AllNumbers FROM tblMyTable WHERE Field1 IS NOT NULL
UNION SELECT Field2 AS AllNumbers FROM tblMyTable WHERE Field2 IS NOT
NULL UNION SELECT Field3 AS AllNumbers FROM tblMyTable WHERE Field3 IS
NOT NULL UNION SELECT Field4 AS AllNumbers FROM tblMyTable WHERE Field4
IS NOT NULL;

!qryAllNumbers:
AllNumbers
0
1
2
3

qryCountAcrossFields:
SELECT qryAllNumbers.AllNumbers, (SELECT Count(A.Field1) FROM
tblMyTable AS A WHERE A.Field1 = qryAllNumbers.AllNumbers) + (SELECT
Count(A.Field2) FROM tblMyTable AS A WHERE A.Field2 =
qryAllNumbers.AllNumbers) + (SELECT Count(A.Field3) FROM tblMyTable AS
A WHERE A.Field3 = qryAllNumbers.AllNumbers) + (SELECT Count(A.Field4)
FROM tblMyTable AS A WHERE A.Field4 = qryAllNumbers.AllNumbers) AS
theCount FROM qryAllNumbers;

!qryCountAcrossFields:
AllNumbers theCount
0 9
1 4
2 2
3 1

The union query will return all the numbers contained in the four
fields uniquely, sorted by value. The first subquery in
qryCountAcrossFields counts the number of times the unique number from
qryAllNumbers appears in Field1 in tblMyTable. The second subquery
counts the number of times the unique number from qryAllNumbers appears
in Field2 in tblMyTable, etc. 'Count' is not a good field name since
it's probably a reserved name in SQL so I changed it to theCount. Also
consider whether or not the data in Field1, ..., Field4 should be
separated out into a separate table to obviate counting across fields.
The use of field names like Field1, Field2, etc. often indicates a
non-normalized structure so perhaps your info should be stored like:

tblMyNumbers
IDMN IDMT theNumber theKind
1 1 1 1
2 1 2 2
3 1 1 3
4 1 0 4
5 2 1 1
6 2 1 2
7 2 0 3
8 2 0 4
9 3 2 1
10 3 0 2
11 3 0 3
12 3 0 4
13 4 3 1
14 4 0 2
15 4 0 3
16 4 0 4

Then the data for a given record in tblMyTable, perhaps for a report,
can be found by using the foreign key IDMT. The counting query
simplifies to:

qryCountDown:
SELECT theNumber, Count(theNumber) AS theCount FROM tblMyNumbers GROUP
BY theNumber HAVING theNumber Is Not Null;

!qryCountDown:
theNumber theCount
0 9
1 4
2 2
3 1

The fact that the fields are similar enough to have their counts
combined seems to indicate that this normalization should be done.
Because of normalization many queries will need more joins and a few
queries will be simpler but if normalization is not done where it
should you will likely encounter some nasty consequences later.

I hope this helps,

James A. Fortune