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

how to count value from multiple columns in access query

P: 62
i have table which contains eight columns
In all eight columns there are two values either "Personal" and "Professional"
I want to create query where i can count only personal from all eight column
how do i write query for that
thank you
Nov 14 '13 #1

✓ answered by NeoPa

If you want records where each column is set to 'Personal' then :
Expand|Select|Wrap|Line Numbers
  1. SELECT Count(*)
  2. FROM   [myTable]
  3. WHERE  ([Col1]='Personal')
  4.   AND  ([Col2]='Personal')
  5.   AND  ([Col3]='Personal')
  6.   AND  ([Col4]='Personal')
  7.   AND  ([Col5]='Personal')
  8.   AND  ([Col6]='Personal')
  9.   AND  ([Col7]='Personal')
  10.   AND  ([Col8]='Personal')
If you want records where any one of the columns is set to 'Personal' then :
Expand|Select|Wrap|Line Numbers
  1. SELECT Count(*)
  2. FROM   [myTable]
  3. WHERE  ('Personal' In([Col1],[Col2],[Col3],[Col4],[Col5],[Col6],[Col7],[Col8]))
It may be a good idea at this point to clarify exactly what it is that you do want.

Share this Question
Share on Google+
6 Replies


P: 16
Try

Expand|Select|Wrap|Line Numbers
  1. SELECT Count(*)
  2. FROM myTable
  3. WHERE Col1="Personal" OR Col2="Personal" OR...Col8="Personal"
change table and field names to suit
Nov 14 '13 #2

NeoPa
Expert Mod 15k+
P: 31,487
If you want records where each column is set to 'Personal' then :
Expand|Select|Wrap|Line Numbers
  1. SELECT Count(*)
  2. FROM   [myTable]
  3. WHERE  ([Col1]='Personal')
  4.   AND  ([Col2]='Personal')
  5.   AND  ([Col3]='Personal')
  6.   AND  ([Col4]='Personal')
  7.   AND  ([Col5]='Personal')
  8.   AND  ([Col6]='Personal')
  9.   AND  ([Col7]='Personal')
  10.   AND  ([Col8]='Personal')
If you want records where any one of the columns is set to 'Personal' then :
Expand|Select|Wrap|Line Numbers
  1. SELECT Count(*)
  2. FROM   [myTable]
  3. WHERE  ('Personal' In([Col1],[Col2],[Col3],[Col4],[Col5],[Col6],[Col7],[Col8]))
It may be a good idea at this point to clarify exactly what it is that you do want.
Nov 14 '13 #3

Rabbit
Expert Mod 10K+
P: 12,365
And if you want a count of each row for each colum that has personal, then use a sum of an IIf or Switch function.
Nov 14 '13 #4

P: 62
Column Names are item 1,item 2 till item 8
In these column there are two entries either personal or behaviour i want to count only personal from these columns
Dec 10 '13 #5

P: 10
You can count all fields in one count. It depends on your requirements and what they contain.
In my count version 1 you will find that The Paid Field contains a total of 15, The Use Field contains a total of 23 out of 26 records.
I use similar counts like this routinely.
In count version 2 I Included both fields to Count twice and Both as Group By to see the individual Break Downs and how they add up to their Totals. Once again the numbers differentiate.

So yes it is entirely possible depending on what you have and what you want to do.
As I did read the post, seeing as how that would be required to answer on topic, it lacks complete information. Given that, I made a guess as to what the OP wanted.

Thanks
Jeff Jones
Dec 10 '13 #6

zmbd
Expert Mod 5K+
P: 5,397
rahul2310
one of the methods offered should do the trick.

From this table sounds a little bit like the database isn't normalized... > Database Normalization and Table Structures. which might make it easier to query.
Dec 10 '13 #7

Post your reply

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