how to count value from multiple columns in access query

[rahul2310]

i have table which contains eight columns
In all eight columns there are two values either "Personal" and "Profession al"
I want to create query where i can count only personal from all eight column
how do i write query for that
thank you

[CJ_London]

Try

SELECT Count(*)
FROM myTable
WHERE Col1="Personal" OR Col2="Personal" OR...Col8="Personal"

change table and field names to suit

[NeoPa]

If you want records where each column is set to 'Personal' then :

SELECT Count(*)
FROM   [myTable]
WHERE  ([Col1]='Personal')
  AND  ([Col2]='Personal')
  AND  ([Col3]='Personal')
  AND  ([Col4]='Personal')
  AND  ([Col5]='Personal')
  AND  ([Col6]='Personal')
  AND  ([Col7]='Personal')
  AND  ([Col8]='Personal')

If you want records where any one of the columns is set to 'Personal' then :

SELECT Count(*)
FROM   [myTable]
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.

[Rabbit]

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.

[rahul2310]

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

[gutts009]

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

[zmbd]

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.