misuser2k7
MIS
I am trying to build an Access query which counts number of records based which have two fields with no zeros in them. The query result/output is supposed to show records by their IDs and the number of rows that have no zeros in them.
The table looks like .......
ID TNum STNum TName TGender TAge
187771 0 0 Alex Male 21
187771 1 1 Alex Male 21
187771 1 2 Alex Male 21
187771 1 3 Alex Male 21
187772 0 0 Beth Female 22
187772 1 1 Beth Female 22
187772 1 2 Beth Female 22
187773 0 0 Charles Male 20
187774 0 0 Deena Female 24
187774 1 1 Deena Female 24
187774 1 2 Deena Female 24
187774 1 3 Deena Female 24
187774 1 4 Deena Female 24
The table has a compound key (ID, TNum, STNum)
The output needs to look like ......
ID CountOfID
187771 3
187772 2
187774 4
But looks like .........
ID CountOfID
187771 1
187771 1
187771 1
187772 1
187772 1
187774 1
187774 1
187774 1
187774 1
The query doing the latter (what output shouldn’t look like) …….
SELECT Table1.ID, Count(Table1.ID) AS CountOfID
FROM Table1
GROUP BY Table1.ID, Table1.TNum, Table1.STNum
HAVING (((Table1.TNum)>0) AND ((Table1.STNum)>0));
Please suggest a solution to this problem.
Many thanks.