CODEWITHSUNDEEP
databasems-accesscount
Daniel
Daniel

Reputation: 35684

multiple count rows based on date ranges (access db)

I can get a single count row from a specified date range like this:

SELECT table.[EVENT NAME], Count(*) AS [Count]
FROM table
WHERE [EVENT]='alphabetical' And table.DATE>=#11/20/2010# And (table.DATE)<=#11/26/2010#
GROUP BY table.[EVENT NAME];

but how could I add multiple rows with different date ranges?

[EVENT NAME],[DATE 11/20-11/26],[DATE 11/27-12/3], etc...

EDIT

the data would look something like this

event1;1/11/2010
event1;1/11/2010
event2;1/11/2010
event2;1/11/2010
event2;1/11/2010
event3;1/11/2010
event1;1/12/2010
event1;1/12/2010
event2;1/12/2010
event2;1/12/2010
event4;1/12/2010
event4;1/12/2010

etc.

and would like something like this (preferably with more columns) :

event1;2;2
event2;3;2
event3;1;0
event4;0;2

Upvotes: 0

Views: 3605

Answers (1)

JonH
JonH

Reputation: 33143

You'd use a group by clause and group by the date.

You didn't provide example records with expected results, that helps us help you :). In other words post more information.. But from what I can tell you want a count based on a date range.

So if you had 1/1/2010 with 10 rows and 1/2/2010 with 20 referenced rows and 1/3/2010 with 6 reference rows...you'd want output like this:

1/1/2010 10

1/2/2010 20

1/3/2010 6

So SELECT COUNT(*), MyDate FROM MyTable GROUP BY MyDate

To answer your question about a date range, think of how group by works, it works by grouping a set of data by combining all sets that match a criteria. So when you say group by date it groups by a single date. You want a date range, so each row should know about or understand a range (Start to End). So you need to include these columns in each of your rows by generating them via SQL.

Edit

For instance

SELECT Count(*), DATEADD(day, -10, GetDate()) AS StartDate, DATEADD(day, 10, GetDate()) AS EndDate FROM MyTable GROUP BY StartDate, EndDate

Access has similiar functions to add days to dates so look that up for MS Access. Then just generate a start and end date for each column.

Upvotes: 2

Related Questions