Count records 2

[misuser2k7]

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.

 

[dhookom]

You can create a totals query with SQL like:

SELECT ID, Count(ID) AS CountOfID
FROM tblMisUser
WHERE STNum<>0 AND TNum<>0
GROUP BY ID;

Also, please be considerate of those who might try to help you by using TGML to format your postings.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[SkipVought]

Hi,

SELECT Table1.ID, Count(Table1.ID) AS CountOfID 
FROM Table1 
WHERE Table1.TNum + Table1.STNum <> 0
GROUP BY Table1.ID;

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[dhookom]

Skip,
You are assuming the numeric fields can't store negative numbers.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[SkipVought]

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[misuser2k7]

Duane And Skip,

Thank you both!

Your queries worked perfectly.

 

[misuser2k7]

Skip,

I tried to give you a 'great post' star but it won't work.

I would like you to know I really appreciate your help, thank you!

 

[SkipVought]

But it did work and I thank you.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]