I have an Access table looking like this:
Date      Activ
1/1/02    work
1/1/02    play
1/1/02    sleep
1/2/02    work
1/2/02    sleep

I want to write a query that will give me a count of activities per
day on a given month (I will pass a month parameter to the query),
with result looking like this:
1/1/02    3
1/2/02    2
...
to end of month of January

---

Request for Question Clarification by secret901-ga on 20 Aug 2002 15:11 PDT

What language are you writing in? C++? Java?

---

Clarification of Question by lochness-ga on 20 Aug 2002 15:15 PDT

Simply sql in MS Access 2000

---

Clarification of Question by lochness-ga on 20 Aug 2002 17:28 PDT

I'm sorry for being so vague.

If there is nothing for a date within the month, it should say have a
zero value; hence,
1/1/02   3
1/2/02   2
1/3/02   0
1/4/02   0
...
to the end of the month

This query will be used in my ASP page with Access 2000 db backend.

Hi Lochness,

Here are the steps required to create the necessary query in Microsoft
Access 2000:

1/ Click on queries from the Access main menu and select "Create Query
in Design view".

2/ On the listbox that appears, select the table which you have
described in your question. Then click "Close"

3/ Select the fields you wish to query, in your example you would
double click on "Date" and "Activ". Beware using the word "Date" as a
field name as in your example, Access will try and interpret the word
"date" as todays actual date. If you have to specify "date" in the
query directly, you must enclose it in square brackets "[]".

4/ Look in the toolbar for the "Sum" icon. It looks like a black "E"
and should be next to the combo-box in the toolbar. If you cant see
the Toolbar select "View>Toolbars>Query Design". Notice that when this
icon is select the words "Group By" appear next to total in the table.

5/ In the column for "Activ" select the word "Count" instead of "Group
By". Next to date, set "Sort" to "Descending" to provide your results
to ASP in chronological order.

You can test your query by clicking the exclamation mark "!" in the
toolbar.

The SQL for this query will look like this (assuming your table name
is "Activities":

SELECT Activities.[Date], Count(Activities.Activ) AS CountOfActiv
FROM Activities
GROUP BY Activities.[Date]
ORDER BY Activities.[Date] DESC;

You can copy and paste this text into Access SQL query design if you
wish.

This will satisfy your requirements EXCEPT for the date showing with 0
activities in the ASP report.

The reason for this is that Access has no real concept of consecutive
dates, It does not know to report 2/AUG/2002 between 1/AUG/2002 and
3/AUG/2002, it will only report for dates that actually exist in your
activities table, there are 2 main ways around this problem.

1/ Create a table that contains all the dates that you will be
reporting on. Join the table to the Activities table with a LEFT join
and select the "Date" field from the second table when perforing your
query.

2/ Make the report generator or front end ASP code generate the report
line by line for each date.

Neither of these solutions are elegant unfortuantely, I have always
gone with the first option.

Good luck,

Molloch-ga

Answer helps a lot. 

The query end up looking like this:
SELECT AllDates.IndividualDate, Count(Activities.Name) AS CountOfActiv
FROM AllDates LEFT JOIN Activities ON AllDates.IndividualDate=Activities.EventDate
WHERE AllDates.IndividualDate between [StartDt] and [EndDt]
GROUP BY AllDates.IndividualDate
ORDER BY AllDates.IndividualDate;

I used Excel to quickly create a range of dates and then paste append into Access.

SELECT Date, COUNT(*) FROM TableName GROUP BY Date

Are you doing this as a class exxcercise, so it must be done using
SQL, or do you want to know the "real world" way of doing it (i.e.,
with the Access 2000 Query Wizard, or VBA?

~omniscientbeing

SELECT Date, COUNT(*) FROM TableName WHERE Date>='1/1/02' AND
Date<'2/1/02' GROUP BY Date

Sorry for my uncomplete first answer ;)
Greets,
Korba