Using Count in a query

[aziz rasul]

I am trying to use the Count function in a select query (without having to use the Totals toolbar button).

I have the following table as an example: -

Year Genre
1923 comedy
1923 comedy
1925 war
1927
1925 comedy
1921
1924 comedy
1920 drama

I am trying to count the number of records that have a value in the Genre field. When using an expression like

Expr1: Count("Genre") I get the value of 8. I expected to get the value of 6 because two of the records had Null values in the [Genre] field. According to online help, the Count function DOES NOT COUNT NULL VALUES?

Hence what does the online help for Count mean when it says

"The Count function doesn't count records that have Null fields unless expr is the asterisk (*) wildcard character."

Any ideas, anyone.

 

[Nouba]

here is a query which might give you a clue on how to use aggregate functions. I you only want the summary you can take out on of the subqueries.

SELECT
  G.YR
, G.Genre
, (SELECT
    Count(*)
  FROM tblGenre
  WHERE
    Genre=G.Genre) AS GCnt
, (SELECT
    Count(*)
  FROM tblGenre
  WHERE
    Genre Is Not Null) AS AbsCnt
FROM tblGenre AS G
ORDER BY
  G.Genre
, G.YR

You have to change the name tblGenre to the name of your table.

Good look

 

[pono1]

Aziz,

To supplement Norbert's example, you can also try this.

Create a test table called TblTapes and add a couple of fields -- one called Type, the other called Year. Add five or six records to the table, leaving the Year blank for a couple of the records.

Go to the query grid, adding the tapes table, and then, in SQL view, paste in one of the two SQL statements below.

SELECT TblTapes.Type, Count(TblTapes.Year) AS CountOfYear
FROM TblTapes
GROUP BY TblTapes.Type;

SELECT TblTapes.Type, Count(TblTapes.Year) AS CountOfYear
FROM TblTapes
GROUP BY TblTapes.Type
HAVING Count(TblTapes.Year)>0;

Toggle between Design, SQl, and datasheet view. While not explaining the help file explanation, hopefully this will still move you closer to a solution.

Regards,
Tim

 

[aziz rasul]

Thanks all.