Count/Query/Incorrect result

[Lensmeister]

I have attached a screen dump of my problem.

I am trying to count the number of cards in a game in a query. The check boxes are ticked when a card is issued, but when I run the query (on the right) it shows a minus figure .... How come.

Please can someone advise me what's wrong.

Using Access 2003 and Windows XP

Thanks

 

[DCrake]

You cannot sum/count a yes/no field. Create two new columns

Red:IIf([RedCard]=True,1,0)

Yellow:IIF([YellowCard]=True,1,0)

Then sum the Red and Yellow columns

IF you count you will always get the same total because a record will be counted whether it is red or yellow.

 

[Lensmeister]

Thanks I will try that now.

EDIT:

Thanks it worked ...

 

[highandwild]

You could also use the Abs function which will return a 1 for True and a 0 for False.

Red: Abs([redcard])

Abs is a very useful function, I use it a lot.

 

[Lensmeister]

Thanks.

I have also the following problem similar.:

tbl_Matches
qry_OppoPerf

The query looked at the tbl_Matches
I want it to look at the table and where the team name is count the number of W in the column "R".

I used this SQL:

SELECT COUNT(*) AS [R] FROM tbl_Matches WHERE R = "W"

in the Field section of the Query ...

What am I doing wrong ?

 

[highandwild]

Create a query and then look at the SQL.

 

[Lensmeister]

Create a query and then look at the SQL.

I did sadly it didn't work.

I seem to be missing something somewhere.


in the query builder I have

Field: R
Table: tbl_Matches
Total: Count
Criteia: "W"

this returns:

data type mismatch in Criteria expression

 

[boblarson]

I did sadly it didn't work.

I seem to be missing something somewhere.


in the query builder I have

Field: R
Table: tbl_Matches
Total: Count
Criteia: "W"

this returns:

data type mismatch in Criteria expression

You can't use a WHERE clause like that on a count field. You need the Actual field to put the criteria on.

 

[Lensmeister]

The SQL from the above gave me this:

SELECT tbl_Matches.Opposition
FROM tbl_Matches
GROUP BY tbl_Matches.Opposition, tbl_Matches.R
HAVING (((tbl_Matches.R)="W"));

 

[Lensmeister]

now I have done this and got this (see images)