By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,145 Members | 1,485 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,145 IT Pros & Developers. It's quick & easy.

Count total occurrences in different fields

P: 3
Fairly new to Access and really doesn't know much about SQL except some basic commands. Currently I'm working with a database that I've already sorted by some queries, and here's a simplified result.

Query1:
Name A B
Smith_A Ya [null]
Smith_R Yb Ya
Smith_C [null] E
Smith_B E Y

So far I can count occurrences of "Y" in single fields by using

Expand|Select|Wrap|Line Numbers
  1. SELECT Count(*) AS NumA
  2. FROM [Query1]
  3. WHERE [Query1].A like 'Y*';
  4.  
And get result:
NumA
2

What I want as final result is to tally up total number of names and total occurences of "Y" in the 2 fields, so the final query (or report) will show:

NumPersons NumA NumB
4 2 2

Writing single query for each field then report them together is really dirty since the original database has about 70 fields and 65 records. I just wonder if there's a more elegant way of doing it.

I suppose I can always export the query to Excel and use COUNTIF to do this, but I would like to know if there's a way to write the query so I can keep it all in Access without having to write formulas in reports.

Sorry for the newbie question and thanks in advance for the help.
Nov 29 '06 #1
Share this Question
Share on Google+
1 Reply


NeoPa
Expert Mod 15k+
P: 31,660
Expand|Select|Wrap|Line Numbers
  1. SELECT Count([Name]) AS NumName,
  2.    Sum(IIf([A] Like 'Y*',1,0)) AS NumA,
  3.    Sum(IIf([b] Like 'Y*',1,0)) AS NumB
  4. FROM [Query1];
PS. Someone that cares about the elegance of code doesn't ask newbie questions!
Nov 29 '06 #2

Post your reply

Sign in to post your reply or Sign up for a free account.