Count Based on Date

[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.

 

[Frolickin]

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?

 

[Allen Browne]

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).

 

[Frolickin]

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).

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.

 

[Allen Browne]

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.

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).

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.

 

[Frolickin]

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.

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.

 

[Allen Browne]

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.

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.

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.

 

[Frolickin]

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.

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!

 

[Allen Browne]

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.

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.

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!