Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Count records 2

Status
Not open for further replies.
Sep 12, 2007
45
US

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.
 
You can create a totals query with SQL like:

SQL:
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
 
Hi,
Code:
SELECT Table1.ID, Count(Table1.ID) AS CountOfID 
FROM Table1 
WHERE Table1.TNum + Table1.STNum <> 0
GROUP BY Table1.ID;


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

Duane And Skip,

Thank you both!

Your queries worked perfectly.
 

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!
 
But it did work and I thank you.

Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top