Count Based on Date

  • Thread starter Thread starter Frolickin
  • Start date Start date
F

Frolickin

I have a query set up to generate a list of events and their dates.

event1, 12/4/2001
event2, 2/4/2004
event3, 10/23/2001
event4, 7/4/2002
etc.

I would like to create nother query of these data to count the number of
events b4 a specific date. I am stuck on how to do this.

Any help will be greatly appreciated.
 
I figured out one way to do this, but it requires two more queries. There
has to be a better way.

Query2
I used the Date and set the criteria to < 1/1/2003 (or whatever date)

Query3
Count of query2

I would think there would be a way to combine these two. Yet, each time I
do, I get odd results, none of which is what I want.

Ideas?
 
Try a query like this:
SELECT Count([EventID] AS HowMany
FROM [EventTable]
WHERE [EventDate] < #1/1/2004#;

(You can type something like that into SQL View of a query).
 
Allen Browne said:
Try a query like this:
SELECT Count([EventID] AS HowMany
FROM [EventTable]
WHERE [EventDate] < #1/1/2004#;

(You can type something like that into SQL View of a query).
Click to expand...

Thank you for your help.

I am confused by the AS HowMany syntax. Do I need to define another field
called "HowMany"? Where does that field come from?

I appreciate the help,
Fro.
 
Works just like that.

HowMany is an alias for the field.
You can use any name you like there (other than reserved words).

Access typically uses:
SELECT Count([EventID] AS CountOfEventID

If SQL view is new for you, open any query you have and choose SQL View from
the View menu.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Frolickin said:
Allen Browne said:
Try a query like this:
SELECT Count([EventID] AS HowMany
FROM [EventTable]
WHERE [EventDate] < #1/1/2004#;

(You can type something like that into SQL View of a query).
Click to expand...

Thank you for your help.

I am confused by the AS HowMany syntax. Do I need to define another field
called "HowMany"? Where does that field come from?

I appreciate the help,
Fro.
Click to expand...
 
Allen Browne said:
Works just like that.

HowMany is an alias for the field.
You can use any name you like there (other than reserved words).

Access typically uses:
SELECT Count([EventID] AS CountOfEventID

If SQL view is new for you, open any query you have and choose SQL View from
the View menu.
Click to expand...
Okay, I finally got it. I had left the brackets in the expression. After
beating my head against the wall for a day, I just got it.

Thank you!

This query opens up an idea that maybe you can help me get to. If I want to
see how many events there were prior to 1/1/2003, I use 1/1/2003 as the
criteria in the query. Likewise, if I want to see how many events took
place prior to 2/1/2003, I would use that as the criteria.

Let's say I wanted to plot the growth of events over time by taking the
result of the query 1/1/2003, 2/1/2003 ..1/1/2004. I could run the query 12
times get the response and plot in Excel without trouble. Where would I
begin to generate this through Access?

This database is growing and I would like plot (via a web page) the events
"automatically" as the data changes without re-running the query each time.

I truly appreciate all the help this newsgroup has provided me. I have been
successful in implementing what I have wanted up to now. I am entering new
territory at this point!

Fro.
 
1. Create a query into your table.

2. Depress the Totals button (upper sigma icon) on toolbar.
Access adds a Total row to the grid.

3. In the Field row of a fresh column, enter:
Format([EventDate], "yyyy\-mm")
and accept Group By in the Total row for this field.

4. Drag EventID into the grid. Choose Count in the Total row.

The query groups by year and date, and counts the number of events in each.

Reports in Access are also capable of grouping by Month.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Frolickin said:
Allen Browne said:
Works just like that.

HowMany is an alias for the field.
You can use any name you like there (other than reserved words).

Access typically uses:
SELECT Count([EventID] AS CountOfEventID

If SQL view is new for you, open any query you have and choose SQL View from
the View menu.
Click to expand...
Okay, I finally got it. I had left the brackets in the expression. After
beating my head against the wall for a day, I just got it.

Thank you!

This query opens up an idea that maybe you can help me get to. If I want to
see how many events there were prior to 1/1/2003, I use 1/1/2003 as the
criteria in the query. Likewise, if I want to see how many events took
place prior to 2/1/2003, I would use that as the criteria.

Let's say I wanted to plot the growth of events over time by taking the
result of the query 1/1/2003, 2/1/2003 ..1/1/2004. I could run the query 12
times get the response and plot in Excel without trouble. Where would I
begin to generate this through Access?

This database is growing and I would like plot (via a web page) the events
"automatically" as the data changes without re-running the query each time.

I truly appreciate all the help this newsgroup has provided me. I have been
successful in implementing what I have wanted up to now. I am entering new
territory at this point!

Fro.
Click to expand...
 
Allen Browne said:
3. In the Field row of a fresh column, enter:
Format([EventDate], "yyyy\-mm")
and accept Group By in the Total row for this field.
Click to expand...

Now that is great! I will use that. I am assuming I could create a running
total too.

Date # that month #total
2000-12 1 1
2001-1 3 4
2001-2 5 9
etc.

Thank you!
 
Running total in a query is very messy and inefficient.
Basically a DSum() or subquery.

In a report, you can do it just by setting the RunningSum property of a text
box.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Frolickin said:
Allen Browne said:
3. In the Field row of a fresh column, enter:
Format([EventDate], "yyyy\-mm")
and accept Group By in the Total row for this field.
Click to expand...

Now that is great! I will use that. I am assuming I could create a running
total too.

Date # that month #total
2000-12 1 1
2001-1 3 4
2001-2 5 9
etc.

Thank you!
Click to expand...
 
Back
Top