multiple count rows based on date ranges (access db)

Question

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

JonH · Accepted Answer · 2011-01-05 17:23:57Z

2

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.

edited Jan 5, 2011 at 17:23

answered Jan 5, 2011 at 17:12

JonH

33.2k13 gold badges94 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Daniel Over a year ago

how would I group by date ranges?

JonH Over a year ago

@Daniel, You would group by date ranges see this sqlteam.com/article/group-by-using-weeks

Daniel Over a year ago

the part I'm confused about is how do I get two separate groupings, one for event type and another for the date. Or would it be best to build a table that groups events/day and query that for date ranges second?

JonH Over a year ago

@Daniel - When you say event type, do you mean like BirthdayParty, New Years Eve, etc? If so just add it to your groupings list. Remember you can group by as many fields as you want as long as it makes sense. You know your data more then I do. So first you group by your event type then by your date range. But remember order matters here, if you group by your date range and then by your event type you will have the same event come up maybe more then once depending on the data in the table. For instance an event can come up more then once. If on the othr hand you group by event continued

JonH Over a year ago

first then you will get a unique amount of events...so you know your data, test it in a query get the results and look at the results. If they make sense you are good to go. My incliniation is you want to first group by your daterange then by the event. SELECT YourFields FROM YourTable GROUP BY YourStart, YourEndDate, YourEvent

|

Collectives™ on Stack Overflow

multiple count rows based on date ranges (access db)

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related