Multiple count - single table, fixed criteria [RESOLVED]

[Br1an_g]

hi,

i need to look at a number of fields within a table, and count where it equals Zero.

e.g.:

MyTable1
               Field1     Field2     Field3     Field4
Record1       5            0           5           1
Record1       0            0           5           1
Record3       1            5           0           0
Record4       0            1           1           5

so my query should retun:

Field1 2
Field2 2
Field3 1
Field4 1

I had done this previously using Dcount, but its really slow.

Done a search on here, and there are a couple of suggestions, but they dont seem to work for my situation.

any suggestions appreciated

TIA

[llkhoutx]

The SQL for such a query is

SELECT Count(MyTable1.Field1) AS CountOfField1, Count(MyTable1.Field2) AS CountOfField2, Count(MyTable1.Field3) AS CountOfField3, Count(MyTable1.Field4) AS CountOfField4, Count(MyTable1.Field5) AS CountOfField5
FROM MyTable1;

[RobDog888]

I think the poster wants to return only where the fields are zero.

SELECT Count(MyTable1.Field1) AS CountOfField1, Count(MyTable1.Field2) AS CountOfField2, Count(MyTable1.Field3) AS CountOfField3, Count(MyTable1.Field4) AS CountOfField4, Count(MyTable1.Field5) AS CountOfField5
FROM MyTable1
WHERE Count(MyTable1.Field1) <> 0 AND Count(MyTable1.Field2) <> 0 AND Count(MyTable1.Field3) <> 0;

[Br1an_g]

I think the poster wants to return only where the fields are zero.

SELECT Count(MyTable1.Field1) AS CountOfField1, Count(MyTable1.Field2) AS CountOfField2, Count(MyTable1.Field3) AS CountOfField3, Count(MyTable1.Field4) AS CountOfField4, Count(MyTable1.Field5) AS CountOfField5
FROM MyTable1
WHERE Count(MyTable1.Field1) <> 0 AND Count(MyTable1.Field2) <> 0 AND Count(MyTable1.Field3) <> 0;

Yes, where the field itself is equal to zero.
wont your statment above return the counts, where it has counted more than 0.

e.g.:

SELECT Count(MyTable1.Field1) AS CountOfField1
FROM MyTable1
WHERE MyTable1.Field1 = 0;

the above works, but it means having to repeat this statement for each of the fields in the table (about 20)

is it not possible to do this for multiple fields in the one statement?

also, once i have ran the SQL statement, whats the best way to get the value into the control on the form?
e.g.

dim strSQL as String
dim dbs as Database
dim rst as Recordset
dim VarRecords as Variant

set dbs = CurrentDb

strSQL = "SELECT Count(MyTable1.Field1) AS CountOfField1 " _
& " FROM MyTable1 " _
& " WHERE MyTable1.Field1 = 0;"

set rst = dbs.OpenRecordset(strSQL)

'the next bit im not sure of
varRecords = rst.GetRows(rst.RecordCount)

txtTextBox = varRecords(0, 0)

is there a better way? and can i reference the 'CountOfField1' directly?

thanks

[RobDog888]

Didnt you see my sql code statement? It has a where clause to restrict the records to the ones ... oops I did it backwards.
Here is the reversed statement.

SELECT Count(MyTable1.Field1) AS CountOfField1, Count(MyTable1.Field2) AS CountOfField2, Count(MyTable1.Field3) AS CountOfField3, Count(MyTable1.Field4) AS CountOfField4, Count(MyTable1.Field5) AS CountOfField5
FROM MyTable1
WHERE Count(MyTable1.Field1) = 0 AND Count(MyTable1.Field2) = 0 AND Count(MyTable1.Field3) = 0;

[Br1an_g]

RobDogg,

thanks for your reply.

doesnt your SQL statement return where the count of the field equals zero?

i want it to return where the actual field value = 0, not the count of that field.

[Ecniv]

Select 
  Count(qrySubZero.zF1) as ZeroF1
  Count(qrySubZero.zF2) as ZeroF2
  Count(qrySubZero.zF3) as ZeroF3
  Count(qrySubZero.zF4) as ZeroF4
  Count(qrySubZero.zF5) as ZeroF5
FROM
(
SELECT 
  MyTable1.Field1=0 AS zF1,
  MyTable1.Field2=0 AS zF2,
  MyTable1.Field3=0 AS zF3,
  MyTable1.Field4=0 AS zF4,
  MyTable1.Field5=0 AS zF5
FROM 
  MyTable1
) As qrySubZero

Might be slightly different but thats the basic idea.

[RobDog888]

Oops, sorry about that. I was thinking that count is an aggregate function so I had to use the same
in the where clause. Doh! Just take the count out in the where clause.

[Br1an_g]

Select 
  Count(qrySubZero.zF1) as ZeroF1
  Count(qrySubZero.zF2) as ZeroF2
  Count(qrySubZero.zF3) as ZeroF3
  Count(qrySubZero.zF4) as ZeroF4
  Count(qrySubZero.zF5) as ZeroF5
FROM
(
SELECT 
  MyTable1.Field1=0 AS zF1,
  MyTable1.Field2=0 AS zF2,
  MyTable1.Field3=0 AS zF3,
  MyTable1.Field4=0 AS zF4,
  MyTable1.Field5=0 AS zF5
FROM 
  MyTable1
) As qrySubZero

Might be slightly different but thats the basic idea.

Ecniv,

does this type of statement work in VBA?

im getting an error when i try to implement it within my app:

strSQL = "Select Count(qryCountFails.CF1) As CountF1" _
        & " FROM (SELECT ServicingInputTable.[Correct Greeting]=0 AS CF1 " _
        & " FROM ServicingInputTable) As qryCountFails;"

ive only used a single field to get it to work first.

im getting:

Run-time error '3131'

Syntax error in FROM clause

RobDogg: thanks for your input, but if i remove the count from your criteria section, it counts where all of the fields = 0 together, not on an individual basis.

i was thinking this would be a relatively simple task and now im thinking of leaving the Dcounts in place and letting them wait 5 minutes for the form to load...lol

[Ecniv]

Select T1, count(T1) as cT1 From
(Select Table1.Test1='aa' as T1 FROM Table1) as arySubT
Group By T1

This is a test one run in the querybuilder of Access... Appears to work are required. I think I forgot that the options of True/false would need to be represented... Perhaps.

As to your code the Sql statement looks fine.

strSQL = "Select CF1, Count(qryCountFails.CF1) As CountF1 FROM"
strSql = strSql & " (SELECT ServicingInputTable.[Correct Greeting]=0 AS"
strSql = strSql & " CF1 FROM ServicingInputTable) As qryCountFails"
strSql = strSql & " GROUP BY CF1"
debug.print strSql

So I'm not sure why it would error. So if you put the above code in it should print the sql statement to the immediates window. Copy this, and go to Access query builder (new query). Don't add tables and change the top left drop down to sql view. Paste in the statement and see if there is an error or it runs.

If it runs then there is a problem between your program and the db (ADO connection perhaps?) If it fails, then there is a problem with the table/sql statement.

Post up what happens please?

[Br1an_g]

Select CF1, Count(qryCountFails.CF1) As CountF1 FROM
(SELECT ServicingInputTable.[Correct Greeting]=0 AS
CF1 FROM ServicingInputTable) As qryCountFails
GROUP BY CF1;

the above fails with the same error. This is in the access query builder SQL view.

Syntax error in FROM clause, and highlights the select in bold

[Br1an_g]

after a bit of testing it seems that the above Sub-query does not work as it is an Access 97 database.

i have tested the exact statement in an access 2002 front end and it works fine.

thanks for all of your feedback.

I will post the complete SQL when i get a chance to finish it..just for information.

[Br1an_g]

ok, spoke too soon

Select
Count(qryCountFails.CF1) As CountF1,
Count(qryCountFails.CF2) as CountF2,
Count(qryCountFails.CF2) as CountF3
FROM
(
SELECT 
ServicingInputTable.[Correct Greeting]=0 AS CF1,
ServicingInputTable.[Bank Jargon Avoided]=0 AS CF2,
ServicingInputTable.[Positive Words]=0 AS CF3
FROM
ServicingInputTable
) As qryCountFails
GROUP BY 
CF1,
CF2,
CF3;

ok, the sub query works fine, as long as each record does not have 0 in more than one of the criteria field. if it has 0 in more than one field, it counts these seperately.
e.g.:

                    Field1          Field2          Field3
Record1            0                0                 1
Record2            1                0                 1
Record3            0                1                 1
Record4            0                1                 1
Record5            1                0                 0

returns:

                    CountF1      CountF2       CountF3
Record1            2                2                 2
Record2            1                1                 1
Record3            1                1                 1
Record4            1                1                 1

its kind of hard to explain...it sort of works, but doesnt at the same time..

[Ecniv]

Yup hi sorry.
(in that order)

I thought a little more on it (it is working sort of, just not quite the way you want it to...)


This is what I think you need (revised from the earlier try )
For all records you need a count of:
- field 1 is 0
- field 2 is 0
- field 3 is 0
It doesn't matter if they are on the same line (which would be caught in the previous attempt).
Is the following more like what you need?

Select
   qryCFails1.CF1,
   qryCFails2.CF2,
   qryCFails3.CF3
FROM
((
SELECT
   ServicingInputTable.[IDfield],
   ServicingInputTable.[Correct Greeting],
   Count(ServicingInputTable.[Correct Greeting]) AS CF1
FROM
   ServicingInputTable.
GROUP BY
   ServicingInputTable.[Correct Greeting]
HAVING
   ServicingInputTable.[IDfield],
   ServicingInputTable.[Correct Greeting]=0
) AS qryCFails1 LEFT JOIN
(
SELECT
   ServicingInputTable.[IDfield],
   ServicingInputTable.[Bank Jargon Avoided],
   Count(ServicingInputTable.[Bank Jargon Avoided]) AS CF2
FROM
   ServicingInputTable.
GROUP BY
   ServicingInputTable.[IDfield],
   ServicingInputTable.[Bank Jargon Avoided]
HAVING
   ServicingInputTable.[Bank Jargon Avoided]=0
) AS qryCFails2 ON qryCFails1.[IDfield]=qryCFails2.[IDfield])
LEFT JOIN (
SELECT
   ServicingInputTable.[IDfield],
   ServicingInputTable.[Positive Words],
   Count(ServicingInputTable.[Positive Words]) AS CF2
FROM
   ServicingInputTable.
GROUP BY
   ServicingInputTable.[IDfield],
   ServicingInputTable.[Positive Words]
HAVING
   ServicingInputTable.[Positive Words]=0
) AS qryCFails3 ON qryCFails1.[IDField]=qryCFails3.[IDField]

Thinking some more on it - not sure if that is going to give the right results either.. Perhaps gives you more of an idea to experiment with?

[Ecniv]

Try the attached. As I should have made an example to start with.
The queries you will be interested in are 4 and 5, but the others may help to see how I got there.

Enjoy!

[Br1an_g]

thanks ecniv.

the download isnt working for me, but will try it when i get home, could just be work ePolicy.

will let you know how i get on

[Br1an_g]

Ecniv,

query4 was just what i was looking for.

all implemented and working correctly. and much quicker than the DCounts i had before.

thanks