Counting in Query

[Rene Hernandez]

Good morning,

I am attempting to use the following in a query:

Score HousCount: Val(IIf(IsNumeric([SPM Housekeeping].[Score A]),Count([SPM
Housekeeping].[Score A]),0)+IIf(IsNumeric([SPM Housekeeping].[Score
B]),Count([SPM Housekeeping].[Score B]),0)+IIf(IsNumeric([SPM
Housekeeping].[Score C]),Count([SPM Housekeeping].[Score
C]),0)+IIf(IsNumeric([SPM Housekeeping].[Score D]),Count([SPM
Housekeeping].[Score D]),0)+IIf(IsNumeric([SPM Housekeeping].[Score
E]),Count([SPM Housekeeping].[Score E]),0)+IIf(IsNumeric([SPM
Housekeeping].[Score F]),Count([SPM Housekeeping].[Score F]),0))

When i use this in a form or report, it works and returns the total count of
records that are not null. For instance, it returns 5 if Scores A thru E
contain values, omitting Score F for being null. It returns 4 if Scores A
thru D contain values, omitting Scores E and F for being null, etc.

When i insert it into a query, it gives me the following message:

You tried to execute a query that does not include the specified expression
'EvaluationID' as part of an aggregate function.

The above are all text fields, i'm using XP, MS Access 2003.

Please help. Thanks.

 

[mscertified]

Evidently your query contains a GROUP BY clause, every column in that clause
must be included in an aggregate function. The alternative is to remove this
column from the query completely. Go into SQL view to see the SQL that Access
generated.

Dorian.

 

[Rene Hernandez]

What do i need to do to obtain the results i described?

Thanks for your help.
--
Rene Lazaro

Evidently your query contains a GROUP BY clause, every column in that clause
must be included in an aggregate function. The alternative is to remove this
column from the query completely. Go into SQL view to see the SQL that Access
generated.

Dorian.

Good morning,

I am attempting to use the following in a query:

Score HousCount: Val(IIf(IsNumeric([SPM Housekeeping].[Score A]),Count([SPM
Housekeeping].[Score A]),0)+IIf(IsNumeric([SPM Housekeeping].[Score
B]),Count([SPM Housekeeping].[Score B]),0)+IIf(IsNumeric([SPM
Housekeeping].[Score C]),Count([SPM Housekeeping].[Score
C]),0)+IIf(IsNumeric([SPM Housekeeping].[Score D]),Count([SPM
Housekeeping].[Score D]),0)+IIf(IsNumeric([SPM Housekeeping].[Score
E]),Count([SPM Housekeeping].[Score E]),0)+IIf(IsNumeric([SPM
Housekeeping].[Score F]),Count([SPM Housekeeping].[Score F]),0))

When i use this in a form or report, it works and returns the total count of
records that are not null. For instance, it returns 5 if Scores A thru E
contain values, omitting Score F for being null. It returns 4 if Scores A
thru D contain values, omitting Scores E and F for being null, etc.

When i insert it into a query, it gives me the following message:

You tried to execute a query that does not include the specified expression
'EvaluationID' as part of an aggregate function.

The above are all text fields, i'm using XP, MS Access 2003.

Please help. Thanks.

 

[Dale Fye]

Try changing the value in the Total row of the query grid to , change the
value to "Expression".

This seems a little complicated, but since you didn't explain what this part
of your query is actually supposed to accomplish, I can only guess. What do
the values in [Score A] - [Score F] look like? What is the data type of
these fields? You might want to try something like:

Score HousCount: SUM(IIF(Isnumeric([Score A], 1, 0)) + IIF(Isnumeric([Score
B], 1, 0)) + IIF(Isnumeric([Score C], 1, 0)) + IIF(Isnumeric([Score D], 1,
0)) + IIF(Isnumeric([Score E], 1, 0)) + IIF(Isnumeric([Score F], 1, 0)

BTW, this looks like you are using Access as a spreadsheet. Any time you
have column headers that look like yours, it is an indication that the table
is not normalized and that you probably need to normalize the table.
Assuming that the table contains some sort of and ID value which applies to a
bunch of other fields as well, the new table you need to normalize your data
would contain fields for the ID value, the ScoreType, and the ScoreValue.
The values in this table might look like:

ID ScoreType ScoreValue
1 A "1"
1 B "a"
1 C "32a"
1 D "4"
1 E "6"
1 F "c"

With this type of structure, your query would look something like:

Score HouseCount: COUNT(IIF(Isnumeric([ScoreValue]), 1, 0))

but this is going to count every record whether it is numeric or not, so I
don't think this is what your are looking for. Maybe you want:

Score HouseCount: SUM(IIF(Isnumeric([ScoreValue]), 1, 0))


HTH
Dale


--
Email address is not valid.
Please reply to newsgroup only.

What do i need to do to obtain the results i described?

Thanks for your help.
--
Rene Lazaro

Evidently your query contains a GROUP BY clause, every column in that clause
must be included in an aggregate function. The alternative is to remove this
column from the query completely. Go into SQL view to see the SQL that Access
generated.

Dorian.

Good morning,

I am attempting to use the following in a query:

Score HousCount: Val(IIf(IsNumeric([SPM Housekeeping].[Score A]),Count([SPM
Housekeeping].[Score A]),0)+IIf(IsNumeric([SPM Housekeeping].[Score
B]),Count([SPM Housekeeping].[Score B]),0)+IIf(IsNumeric([SPM
Housekeeping].[Score C]),Count([SPM Housekeeping].[Score
C]),0)+IIf(IsNumeric([SPM Housekeeping].[Score D]),Count([SPM
Housekeeping].[Score D]),0)+IIf(IsNumeric([SPM Housekeeping].[Score
E]),Count([SPM Housekeeping].[Score E]),0)+IIf(IsNumeric([SPM
Housekeeping].[Score F]),Count([SPM Housekeeping].[Score F]),0))

When i use this in a form or report, it works and returns the total count of
records that are not null. For instance, it returns 5 if Scores A thru E
contain values, omitting Score F for being null. It returns 4 if Scores A
thru D contain values, omitting Scores E and F for being null, etc.

When i insert it into a query, it gives me the following message:

You tried to execute a query that does not include the specified expression
'EvaluationID' as part of an aggregate function.

The above are all text fields, i'm using XP, MS Access 2003.

Please help. Thanks.