Counting number of events per month...?

[thebigpahoot]

Good morning,

I'm running into some trouble writing a query (and a subsequent report)
in Access 2000. In my DB, all events are sorted by individual days;
however, I am looking to count the number of events per month.

My situation:

Mr. XXXXX has 400 cases listed individually by day in a specific time
period.
In those 400 cases, there are several events that need tracking.

I'm looking to find a way to search per month, how many events occur
per person. I can't figure out a way to write an expression to
partition the results and count them per month, rather than otherwise.
I'm sure I could just run the query and them manually count how many
cases per month per event, buuuuuut if there's a way to do this in a
semi-automated fashion, I'm going to jump all over it.

I'm pretty new to the Microsoft Access field, so any suggestions would
be incredibly helpful.

Thanks very much!

-TheBigPahoot

 

[Douglas J. Steele]

Let's assume your table has a Person field in it, and an EventDate field
that's a complete date.

You want something like the following to get all of the events per person
per month for all of 2006:

SELECT Person, Month(EventDate), Count(*)
FROM MyTable
GROUP BY Person, Month(EventDate)
WHERE EventDate BETWEEN #01/01/2006# AND #12/31/2006#

To build such a query in the graphical interface, you'd drag Person,
EventDate and some other field into the grid.

Convert the table into a Totals query (there's an icon with a Sigma on it on
the button bar, or you can look under the View menu while the query's open
in Design mode). Change what's in the Total row under the "other field" to
Count.

To only get the details for Mr. X, you'd use

SELECT Month(EventDate), Count(*)
FROM MyTable
GROUP BY Month(EventDate)
WHERE EventDate BETWEEN #01/01/2006# AND #12/31/2006#
AND Person = "Mr. X"

 

[Jason Lepack]

What is your query that you have so far? You can copy the SQL fro SQL
View in the Query Designer and paste it here and then we'll help you
along from there.

Also, please list how your tables are designed as well.

Cheers,
Jason Lepack

 

[thebigpahoot]

Absolutely!

So, for perspective, and so I don't have to screw around with
anonymity, here's the situation:

Work at a hospital. Trying to come up with a report of the count of
closure device use ("*angio*" or "*perclose*" or "*starclose*") by
month by attending physicians (Cath_Attending). I figured that I would
write a query for, let's say, two years and then have Access sort it
out and count the number of cases with each closure device per month. I
know that I could do this much easier by querying month by month, but I
was hoping that there'd be a way around that. Ultimately, I'm trying to
come up with the data and dump it into Excel to create a graph of
closure device usage over time per physician.

SELECT dbo_Event_Cath.Date_of_Cath, dbo_Event_Cath.Cath_Attending,
dbo_Cath_ACCClosureDev.ClosureDevName,
Count(dbo_Cath_ACCClosureDev.ClosureDevName) AS CountOfClosureDevName,
dbo_Cath_ACC.PCIProcedure
FROM (dbo_Event_Cath INNER JOIN dbo_Cath_ACCClosureDev ON
dbo_Event_Cath.SS_Event_Cath_ID =
dbo_Cath_ACCClosureDev.SS_Event_Cath_ID) INNER JOIN dbo_Cath_ACC ON
dbo_Event_Cath.SS_Event_Cath_ID = dbo_Cath_ACC.SS_Event_Cath_ID
GROUP BY dbo_Event_Cath.Date_of_Cath, dbo_Event_Cath.Cath_Attending,
dbo_Cath_ACCClosureDev.ClosureDevName, dbo_Cath_ACC.PCIProcedure
HAVING (((dbo_Event_Cath.Date_of_Cath) Between #1/1/2003# And
#12/31/2006#) AND ((dbo_Cath_ACCClosureDev.ClosureDevName) Like
"*angio*" Or (dbo_Cath_ACCClosureDev.ClosureDevName) Like "*perclose*"
Or (dbo_Cath_ACCClosureDev.ClosureDevName) Like "*starclose*") AND
((dbo_Cath_ACC.PCIProcedure) Like "0"));

Thanks! I don't know if there's a different way to do this, or if I
should be on a different forum to attempt this. Any direction you could
give me would be greatly appreciated!

Regards,

-KP

 

[thebigpahoot]

Douglas,

Thank you for responding. I tried this, but the query won't come up
with any results. It seems that there is a conflict between having
Month(Date_of_Cath) and having the dates be BETWEEN #1/1/2006# and
#12/31/2006#. I'm not sure if I'm not inputting it correctly or if
searching for three specific different types of values in the 'Other
Field'.

I'll keep plugging away, though. Let me know if anything comes to mind!

Thanks again!

-KP

 

[Jason Lepack]

I assumed that Cath_Attending was the "Person" you were speaking of.

SELECT dbo_Event_Cath.Cath_Attending,
Count(dbo_Event_Cath.SS_Event_Cath_ID) AS Events, Month([date_of_cath])
AS DateMonth
FROM (dbo_Cath_ACC INNER JOIN dbo_Cath_ACCClosureDev ON
dbo_Cath_ACC.SS_Event_Cath_ID =
dbo_Cath_ACCClosureDev.SS_Event_Cath_ID) INNER JOIN dbo_Event_Cath ON
dbo_Cath_ACCClosureDev.SS_Event_Cath_ID =
dbo_Event_Cath.SS_Event_Cath_ID
WHERE (((dbo_Cath_ACC.PCIProcedure)="0") AND
((dbo_Cath_ACCClosureDev.ClosureDevName) Like "*angio*" Or
(dbo_Cath_ACCClosureDev.ClosureDevName) Like "*perclose*" Or
(dbo_Cath_ACCClosureDev.ClosureDevName) Like "*starclose*"))
GROUP BY dbo_Event_Cath.Cath_Attending, Month([date_of_cath])
HAVING (((Month([date_of_cath])) Between #1/1/2006# And #12/31/2006#));

Cheers,
Jason Lepack

 

[John Vinson]

HAVING (((Month([date_of_cath])) Between #1/1/2006# And #12/31/2006#));

This won't work correctly, Jason - the Month() function returns an
integer between 1 (January) and 12 (December). Your criterion treats
it as a Date/Time and won't return any records.

John W. Vinson[MVP]

 

[John Vinson]

I'm running into some trouble writing a query (and a subsequent report)
in Access 2000. In my DB, all events are sorted by individual days;
however, I am looking to count the number of events per month.

To group by month you need to include a calculated field in the query.
To allow for the possibility that you might want to span over the end
of a year, the most flexible way is to create a text string yyyymm
(e.g. with values 200612 followed by 200701):

SELECT dbo_Event_Cath.Date_of_Cath, dbo_Event_Cath.Cath_Attending,
dbo_Cath_ACCClosureDev.ClosureDevName,
Count(dbo_Cath_ACCClosureDev.ClosureDevName) AS CountOfClosureDevName,
dbo_Cath_ACC.PCIProcedure, Format(dbo_Event_Cath.Date_OfCath,
"yyyymm") AS TheMonth
FROM (dbo_Event_Cath
INNER JOIN dbo_Cath_ACCClosureDev ON
dbo_Event_Cath.SS_Event_Cath_ID=dbo_Cath_ACCClosureDev.SS_Event_Cath_ID)
INNER JOIN dbo_Cath_ACC ON
dbo_Event_Cath.SS_Event_Cath_ID = dbo_Cath_ACC.SS_Event_Cath_ID
GROUP BY dbo_Event_Cath.Date_of_Cath, dbo_Event_Cath.Cath_Attending,
dbo_Cath_ACCClosureDev.ClosureDevName, dbo_Cath_ACC.PCIProcedure,
TheMonth
WHERE (((dbo_Event_Cath.Date_of_Cath) Between #1/1/2003# And
#12/31/2006#) AND ((dbo_Cath_ACCClosureDev.ClosureDevName) Like
"*angio*" Or (dbo_Cath_ACCClosureDev.ClosureDevName) Like "*perclose*"
Or (dbo_Cath_ACCClosureDev.ClosureDevName) Like "*starclose*") AND
((dbo_Cath_ACC.PCIProcedure) Like "0"));

Note that I'm also suggesting changing the HAVING clause (which is
applied AFTER all the totals and groups are done) with WHERE (which is
done first).

John W. Vinson[MVP]

 

[Jason Lepack]

Whoops! I knew I should have actually tested that....

HAVING (((Month([date_of_cath])) Between #1/1/2006# And #12/31/2006#));

This won't work correctly, Jason - the Month() function returns an
integer between 1 (January) and 12 (December). Your criterion treats
it as a Date/Time and won't return any records.

John W. Vinson[MVP]

 

[thebigpahoot]

Hello again! Thank you both for all of your help. I still can't get it,
though...

"Syntax error (missing operator) in query expression 'TheMonth
WHERE (((dbo_Event_Cath.Date_of_Cath) Between #1/1/2006# And
#12/31/2006#) AND ((dbo_Cath_ACCClosureDev.ClosureDevName) Like
"*angio*" Or (dbo_Cath_ACCClosureDev.ClosureDevName) Like "*perclose*"
Or (dbo_Cath_ACCClosureDev.ClosureDevName) Lik'

Would you have any idea what I should do to try to get around this?

Thanks!

-KP

 

[John Vinson]

Hello again! Thank you both for all of your help. I still can't get it,
though...

"Syntax error (missing operator) in query expression 'TheMonth
WHERE (((dbo_Event_Cath.Date_of_Cath) Between #1/1/2006# And
#12/31/2006#) AND ((dbo_Cath_ACCClosureDev.ClosureDevName) Like
"*angio*" Or (dbo_Cath_ACCClosureDev.ClosureDevName) Like "*perclose*"
Or (dbo_Cath_ACCClosureDev.ClosureDevName) Lik'

Would you have any idea what I should do to try to get around this?

Sorry - I made some changes in one part of the query and overlooked
others. YOu need to take the Date_Of_Cath out of the SELECT and GROUP
BY clauses, since you don't want to see individual dates. I'm also
getting rid of some of Access' excessive parentheses:

SELECT dbo_Event_Cath.Cath_Attending,
dbo_Cath_ACCClosureDev.ClosureDevName,
Count(dbo_Cath_ACCClosureDev.ClosureDevName) AS CountOfClosureDevName,
dbo_Cath_ACC.PCIProcedure,
Format(dbo_Event_Cath.Date_OfCath,"yyyymm") AS TheMonth
FROM (dbo_Event_Cath
INNER JOIN dbo_Cath_ACCClosureDev ON
dbo_Event_Cath.SS_Event_Cath_ID=dbo_Cath_ACCClosureDev.SS_Event_Cath_ID)
INNER JOIN dbo_Cath_ACC ON
dbo_Event_Cath.SS_Event_Cath_ID = dbo_Cath_ACC.SS_Event_Cath_ID
GROUP BY Format(dbo_Event_Cath.Date_OfCath,"yyyymm"),
dbo_Event_Cath.Cath_Attending,
dbo_Cath_ACCClosureDev.ClosureDevName, dbo_Cath_ACC.PCIProcedure
WHERE dbo_Event_Cath.Date_of_Cath Between #1/1/2003# And #12/31/2006#
AND ((dbo_Cath_ACCClosureDev.ClosureDevName) Like "*angio*"
Or (dbo_Cath_ACCClosureDev.ClosureDevName) Like "*perclose*"
Or (dbo_Cath_ACCClosureDev.ClosureDevName) Like "*starclose*")
AND (dbo_Cath_ACC.PCIProcedure) = "0";

This assumes that PCIProcedure is of Text type - not sure why you were
using LIKE as the operator for that field since you weren't using any
wildcards. This will find all records within the date range where
PCIProcedure contains nothing but a single character 0; if
PCIProcedure is a Number, change "0" to just 0.

John W. Vinson[MVP]