Counting the number times a record appears within a date period

[Ric404]

I have 2 columns of data, Date Column (short date) (from 10/2/02 to 9/30/05)
& 2nd column of say 26 categories (A,B,C,...Z). Total records 500,000.

I need to count the records for each category within a date period. In the
Query Date column, Total = Count and criteria for date: Between #10/1/2003#
And #9/30/2004#
the 2nd column (Categories" Total is Group by
When I run this I don't get anything. What is odd is that if I use the
following criteria: <#9/30/2003# I get a good query that makes sense.

FYI I'm using Access 2000

 

[Seth Schwarm]

Ric404:

You are so close -
You need 3 columns on the grid - Category, Category (yes, a second time) and
Date

Convert to a Total query, then select Group By under the first column, Count
under the second column and Where under the third column.

In the Criteria row of the third column enter your date criteria - for
example you used a Between . . . And operator.

This should get you what you are looking for.

Seth Schwarm

 

[John Spencer]

SELECT Categories, Count(Categories) as CountThem
FROM YourTable
WHERE YourTable.DateField Between #10/1/2003# and #9/30/2004#
GROUP BY Categories

In the query grid (design view)
-- Add Categories twice to the fields and Datefield once
-- Set Totals to Group and Count under categories and WHERE under datefield
-- enter your date range in critieria under the Datefield.



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Ric404]

Thanks Seth. Tried that and didn't seem to work correctly. though it may be
what I had for Total in the 3rd Date Column (Group by). several of the
answers had repeat counts for the same category, or several of the categories
were listed several times with similar numbers

 

[Ric404]

Thanks, John & Seth that worked. I should have known to use "where" as I do
use sql for oracle (UNIX based).

Thanks again to you both!