Query to display number of records per value

[choaspes]

Hi All

I would be grateful for some help solving a problem that seems so absurdly, insulting simple, that it shouldn't even be a problem at all.

I have a date field in a table.

I wish to display in a query:

Column A representing the month. Column B representing the number of records in the table corresponding to that month.

Doesn't that sound easy?

 

[CJ_London]

Your SQL should look something like

SELECT Month(fldDate) as Mth, Count(ID) as CountRecs from myTable group by Month(fldDate)

You may need to extend this if your data covers more than one year

 

[choaspes]

Much obliged. I have extended it to include years. Not sure what my mental block was on this one.

 

[choaspes]

How would I go about subdividing that count further on the basis of other fields?

Let's say I now want a three columns: month, number of records that are male, number of records that are female.

I still do most of my queries in design view rather than SQL - which I've found to be very intuitive for all my select/update/append queries thus far, but I can't seem to make the criteria rows work on these totals queries. I can add a "where" columns specifying only men or only women, but I can't seem to incorporate a second "count" column and make their criteria different.

 

[pr2-eugin]

You might need to use Expression.. Something along the lines of..

SELECT Count(IIf([tableName].[genderField]="Male", 1, Null)) As totalMale, Count(IIf([tableName].[genderField]="Female", 1, Null)) As totalFemale
FROM tableName;

 

[choaspes]

Genius. Thanks guys.