×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Count records
2

Count records

Count records

(OP)

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.

RE: Count records

You can create a totals query with SQL like:

CODE --> 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

RE: Count records

Hi,

CODE

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Count records

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

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

RE: Count records

blush

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Count records

(OP)

Duane And Skip,

Thank you both!

Your queries worked perfectly.

RE: Count records

(OP)

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!

RE: Count records

But it did work and I thank you.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close