year-to-date count for multiple years

  • Thread starter Thread starter HomiDb
  • Start date Start date
H

HomiDb

Hello. I am hoping someone out there can help me. I track homicide
data and run reports for police administrators when they request it. I
have data from 2005 and 2006. I want to be able to give them the count
of homicides by district to-date. So, today's report would give a
count of 2005 homicides by district from 1/1/05 to 8/28/05 and also of
2006 homicides by district from 1/1/06 to 8/28/06. I know I could run
separate queries and enter the current date each time, but I would like
to have a query that I can just run and it calculates it by the current
date automatically. Is there any way to do this? Thank you for your
help in advance!
 
Since you haven't provided any information about your tables and/or fields
try something like:

SELECT Year([CrimeDate]) as Yr, District,
Sum(Abs([Crime] = "Homicide" AND Format([CrimeDate],"mmdd")
<=Format(Date(),"mmdd")) as HomicideCount
FROM tblCrimes
GROUP BY Year([CrimeDate]) as Yr, District;
 
Thank you! I should add that I am an access novice and do all of my
queries and reports in design view... :) I get what you are doing, but
I am not certain how to enter that into the columns in design view
(Field/Table/Total/Sort/Show/Criteria/Or).

My variable names for this query are:

FileNo = file number
DetHomi = date
IncDist = district
Table name is Homicide Table

Can you tell me what in this string goes where? Sorry for the
ignorance...
SELECT Year([DetHomi]) as Yr, District, Sum(Abs([FileNo] = "Homicide"
AND Format([DetHomi],"mmdd")<=Format(Date(),"mmdd")) as HomicideCount

Duane said:
Since you haven't provided any information about your tables and/or fields
try something like:

SELECT Year([CrimeDate]) as Yr, District,
Sum(Abs([Crime] = "Homicide" AND Format([CrimeDate],"mmdd")
<=Format(Date(),"mmdd")) as HomicideCount
FROM tblCrimes
GROUP BY Year([CrimeDate]) as Yr, District;


--
Duane Hookom
MS Access MVP


HomiDb said:
Hello. I am hoping someone out there can help me. I track homicide
data and run reports for police administrators when they request it. I
have data from 2005 and 2006. I want to be able to give them the count
of homicides by district to-date. So, today's report would give a
count of 2005 homicides by district from 1/1/05 to 8/28/05 and also of
2006 homicides by district from 1/1/06 to 8/28/06. I know I could run
separate queries and enter the current date each time, but I would like
to have a query that I can just run and it calculates it by the current
date automatically. Is there any way to do this? Thank you for your
help in advance!
Click to expand...
Click to expand...
 
Try view the sql of your query and enter:

SELECT Year([DetHomi]) as Yr, IncDist,
Sum(Abs(Format([DetHomi],"mmdd")<=Format(Date(),"mmdd")) as HomicideCount
FROM [Homicide Table]
GROUP BY Year([DetHomi]), IncDist;

This assumes every record in the table is a homicide.
--
Duane Hookom
MS Access MVP

HomiDb said:
Thank you! I should add that I am an access novice and do all of my
queries and reports in design view... :) I get what you are doing, but
I am not certain how to enter that into the columns in design view
(Field/Table/Total/Sort/Show/Criteria/Or).

My variable names for this query are:

FileNo = file number
DetHomi = date
IncDist = district
Table name is Homicide Table

Can you tell me what in this string goes where? Sorry for the
ignorance...
SELECT Year([DetHomi]) as Yr, District, Sum(Abs([FileNo] = "Homicide"
AND Format([DetHomi],"mmdd")<=Format(Date(),"mmdd")) as HomicideCount

Duane said:
Since you haven't provided any information about your tables and/or
fields
try something like:

SELECT Year([CrimeDate]) as Yr, District,
Sum(Abs([Crime] = "Homicide" AND Format([CrimeDate],"mmdd")
<=Format(Date(),"mmdd")) as HomicideCount
FROM tblCrimes
GROUP BY Year([CrimeDate]) as Yr, District;


--
Duane Hookom
MS Access MVP


HomiDb said:
Hello. I am hoping someone out there can help me. I track homicide
data and run reports for police administrators when they request it. I
have data from 2005 and 2006. I want to be able to give them the count
of homicides by district to-date. So, today's report would give a
count of 2005 homicides by district from 1/1/05 to 8/28/05 and also of
2006 homicides by district from 1/1/06 to 8/28/06. I know I could run
separate queries and enter the current date each time, but I would like
to have a query that I can just run and it calculates it by the current
date automatically. Is there any way to do this? Thank you for your
help in advance!
Click to expand...
Click to expand...
Click to expand...
 
Okay - did that and I get a zero count in the Homicide Count column for
all districts in both years. Somehow it needs to give me counts for
1/1/05-8/28/05 in each district and 1/1/06-8/28/06 in same districts...
Any other expressions to add?

Duane said:
Try view the sql of your query and enter:

SELECT Year([DetHomi]) as Yr, IncDist,
Sum(Abs(Format([DetHomi],"mmdd"))<=Format(Date(),"mmdd")) as HomicideCount
FROM [Homicide Table]
GROUP BY Year([DetHomi]), IncDist;

This assumes every record in the table is a homicide.
--
Duane Hookom
MS Access MVP

HomiDb said:
Thank you! I should add that I am an access novice and do all of my
queries and reports in design view... :) I get what you are doing, but
I am not certain how to enter that into the columns in design view
(Field/Table/Total/Sort/Show/Criteria/Or).

My variable names for this query are:

FileNo = file number
DetHomi = date
IncDist = district
Table name is Homicide Table

Can you tell me what in this string goes where? Sorry for the
ignorance...
SELECT Year([DetHomi]) as Yr, District, Sum(Abs([FileNo] = "Homicide"
AND Format([DetHomi],"mmdd")<=Format(Date(),"mmdd")) as HomicideCount

Duane said:
Since you haven't provided any information about your tables and/or
fields
try something like:

SELECT Year([CrimeDate]) as Yr, District,
Sum(Abs([Crime] = "Homicide" AND Format([CrimeDate],"mmdd")
<=Format(Date(),"mmdd")) as HomicideCount
FROM tblCrimes
GROUP BY Year([CrimeDate]) as Yr, District;


--
Duane Hookom
MS Access MVP


Hello. I am hoping someone out there can help me. I track homicide
data and run reports for police administrators when they request it. I
have data from 2005 and 2006. I want to be able to give them the count
of homicides by district to-date. So, today's report would give a
count of 2005 homicides by district from 1/1/05 to 8/28/05 and also of
2006 homicides by district from 1/1/06 to 8/28/06. I know I could run
separate queries and enter the current date each time, but I would like
to have a query that I can just run and it calculates it by the current
date automatically. Is there any way to do this? Thank you for your
help in advance!
Click to expand...
Click to expand...
Click to expand...
 
I can see that I missed a parens. You should have gotten an error message.
Try:

SELECT Year([DetHomi]) as Yr, IncDist,
Sum(Abs(Format([DetHomi],"mmdd")<=Format(Date(),"mmdd"))) as HomicideCount
FROM [Homicide Table]
GROUP BY Year([DetHomi]), IncDist;

You could also try:
SELECT Year([DetHomi]) as Yr, IncDist,
Count(IncDist) as HomicideCount
FROM [Homicide Table]
WHERE Format([DetHomi],"mmdd")<=Format(Date(),"mmdd")
GROUP BY Year([DetHomi]), IncDist;

--
Duane Hookom
MS Access MVP

HomiDb said:
Okay - did that and I get a zero count in the Homicide Count column for
all districts in both years. Somehow it needs to give me counts for
1/1/05-8/28/05 in each district and 1/1/06-8/28/06 in same districts...
Any other expressions to add?

Duane said:
Try view the sql of your query and enter:

SELECT Year([DetHomi]) as Yr, IncDist,
Sum(Abs(Format([DetHomi],"mmdd"))<=Format(Date(),"mmdd")) as
HomicideCount
FROM [Homicide Table]
GROUP BY Year([DetHomi]), IncDist;

This assumes every record in the table is a homicide.
--
Duane Hookom
MS Access MVP

HomiDb said:
Thank you! I should add that I am an access novice and do all of my
queries and reports in design view... :) I get what you are doing, but
I am not certain how to enter that into the columns in design view
(Field/Table/Total/Sort/Show/Criteria/Or).

My variable names for this query are:

FileNo = file number
DetHomi = date
IncDist = district
Table name is Homicide Table

Can you tell me what in this string goes where? Sorry for the
ignorance...
SELECT Year([DetHomi]) as Yr, District, Sum(Abs([FileNo] = "Homicide"
AND Format([DetHomi],"mmdd")<=Format(Date(),"mmdd")) as HomicideCount

Duane Hookom wrote:
Since you haven't provided any information about your tables and/or
fields
try something like:

SELECT Year([CrimeDate]) as Yr, District,
Sum(Abs([Crime] = "Homicide" AND Format([CrimeDate],"mmdd")
<=Format(Date(),"mmdd")) as HomicideCount
FROM tblCrimes
GROUP BY Year([CrimeDate]) as Yr, District;


--
Duane Hookom
MS Access MVP


Hello. I am hoping someone out there can help me. I track homicide
data and run reports for police administrators when they request it.
I
have data from 2005 and 2006. I want to be able to give them the
count
of homicides by district to-date. So, today's report would give a
count of 2005 homicides by district from 1/1/05 to 8/28/05 and also
of
2006 homicides by district from 1/1/06 to 8/28/06. I know I could
run
separate queries and enter the current date each time, but I would
like
to have a query that I can just run and it calculates it by the
current
date automatically. Is there any way to do this? Thank you for
your
help in advance!
Click to expand...
Click to expand...
Click to expand...
 
You are a God. Thank you - the first scenario of the two below works
great. So do you work for Google or do you just do this for fun as a
good samaritan??

Much thanks
Laurie

Duane said:
I can see that I missed a parens. You should have gotten an error message.
Try:

SELECT Year([DetHomi]) as Yr, IncDist,
Sum(Abs(Format([DetHomi],"mmdd")<=Format(Date(),"mmdd"))) as HomicideCount
FROM [Homicide Table]
GROUP BY Year([DetHomi]), IncDist;

You could also try:
SELECT Year([DetHomi]) as Yr, IncDist,
Count(IncDist) as HomicideCount
FROM [Homicide Table]
WHERE Format([DetHomi],"mmdd")<=Format(Date(),"mmdd")
GROUP BY Year([DetHomi]), IncDist;

--
Duane Hookom
MS Access MVP

HomiDb said:
Okay - did that and I get a zero count in the Homicide Count column for
all districts in both years. Somehow it needs to give me counts for
1/1/05-8/28/05 in each district and 1/1/06-8/28/06 in same districts...
Any other expressions to add?

Duane said:
Try view the sql of your query and enter:

SELECT Year([DetHomi]) as Yr, IncDist,
Sum(Abs(Format([DetHomi],"mmdd"))<=Format(Date(),"mmdd")) as
HomicideCount
FROM [Homicide Table]
GROUP BY Year([DetHomi]), IncDist;

This assumes every record in the table is a homicide.
--
Duane Hookom
MS Access MVP

Thank you! I should add that I am an access novice and do all of my
queries and reports in design view... :) I get what you are doing, but
I am not certain how to enter that into the columns in design view
(Field/Table/Total/Sort/Show/Criteria/Or).

My variable names for this query are:

FileNo = file number
DetHomi = date
IncDist = district
Table name is Homicide Table

Can you tell me what in this string goes where? Sorry for the
ignorance...
SELECT Year([DetHomi]) as Yr, District, Sum(Abs([FileNo] = "Homicide"
AND Format([DetHomi],"mmdd")<=Format(Date(),"mmdd")) as HomicideCount

Duane Hookom wrote:
Since you haven't provided any information about your tables and/or
fields
try something like:

SELECT Year([CrimeDate]) as Yr, District,
Sum(Abs([Crime] = "Homicide" AND Format([CrimeDate],"mmdd")
<=Format(Date(),"mmdd")) as HomicideCount
FROM tblCrimes
GROUP BY Year([CrimeDate]) as Yr, District;


--
Duane Hookom
MS Access MVP


Hello. I am hoping someone out there can help me. I track homicide
data and run reports for police administrators when they request it.
I
have data from 2005 and 2006. I want to be able to give them the
count
of homicides by district to-date. So, today's report would give a
count of 2005 homicides by district from 1/1/05 to 8/28/05 and also
of
2006 homicides by district from 1/1/06 to 8/28/06. I know I could
run
separate queries and enter the current date each time, but I would
like
to have a query that I can just run and it calculates it by the
current
date automatically. Is there any way to do this? Thank you for
your
help in advance!
Click to expand...
Click to expand...
Click to expand...
 
I guess I "do this for fun", but also the intrinsic rewards of helping
others, camaraderie among others who help (mostly MS MVPs), and I admit
some "fame". While fellow workers take breaks in the lunch room, I head to
the news groups.

Microsoft provides the means and resources to help support their users while
Google provides better than average searching. I am not on the payroll of
either of these companies.

--
Duane Hookom
MS Access MVP



HomiDb said:
You are a God. Thank you - the first scenario of the two below works
great. So do you work for Google or do you just do this for fun as a
good samaritan??

Much thanks
Laurie

Duane said:
I can see that I missed a parens. You should have gotten an error
message.
Try:

SELECT Year([DetHomi]) as Yr, IncDist,
Sum(Abs(Format([DetHomi],"mmdd")<=Format(Date(),"mmdd"))) as
HomicideCount
FROM [Homicide Table]
GROUP BY Year([DetHomi]), IncDist;

You could also try:
SELECT Year([DetHomi]) as Yr, IncDist,
Count(IncDist) as HomicideCount
FROM [Homicide Table]
WHERE Format([DetHomi],"mmdd")<=Format(Date(),"mmdd")
GROUP BY Year([DetHomi]), IncDist;

--
Duane Hookom
MS Access MVP

HomiDb said:
Okay - did that and I get a zero count in the Homicide Count column for
all districts in both years. Somehow it needs to give me counts for
1/1/05-8/28/05 in each district and 1/1/06-8/28/06 in same districts...
Any other expressions to add?

Duane Hookom wrote:
Try view the sql of your query and enter:

SELECT Year([DetHomi]) as Yr, IncDist,
Sum(Abs(Format([DetHomi],"mmdd"))<=Format(Date(),"mmdd")) as
HomicideCount
FROM [Homicide Table]
GROUP BY Year([DetHomi]), IncDist;

This assumes every record in the table is a homicide.
--
Duane Hookom
MS Access MVP

Thank you! I should add that I am an access novice and do all of my
queries and reports in design view... :) I get what you are doing,
but
I am not certain how to enter that into the columns in design view
(Field/Table/Total/Sort/Show/Criteria/Or).

My variable names for this query are:

FileNo = file number
DetHomi = date
IncDist = district
Table name is Homicide Table

Can you tell me what in this string goes where? Sorry for the
ignorance...
SELECT Year([DetHomi]) as Yr, District, Sum(Abs([FileNo] =
"Homicide"
AND Format([DetHomi],"mmdd")<=Format(Date(),"mmdd")) as
HomicideCount

Duane Hookom wrote:
Since you haven't provided any information about your tables and/or
fields
try something like:

SELECT Year([CrimeDate]) as Yr, District,
Sum(Abs([Crime] = "Homicide" AND Format([CrimeDate],"mmdd")
<=Format(Date(),"mmdd")) as HomicideCount
FROM tblCrimes
GROUP BY Year([CrimeDate]) as Yr, District;


--
Duane Hookom
MS Access MVP


Hello. I am hoping someone out there can help me. I track
homicide
data and run reports for police administrators when they request
it.
I
have data from 2005 and 2006. I want to be able to give them the
count
of homicides by district to-date. So, today's report would give
a
count of 2005 homicides by district from 1/1/05 to 8/28/05 and
also
of
2006 homicides by district from 1/1/06 to 8/28/06. I know I
could
run
separate queries and enter the current date each time, but I
would
like
to have a query that I can just run and it calculates it by the
current
date automatically. Is there any way to do this? Thank you for
your
help in advance!
Click to expand...
Click to expand...
Click to expand...
 
You must really like what you do, then. The homicide Captain who
needed that data thanks you, too.

Best,
Laurie

Duane said:
I guess I "do this for fun", but also the intrinsic rewards of helping
others, camaraderie among others who help (mostly MS MVPs), and I admit
some "fame". While fellow workers take breaks in the lunch room, I head to
the news groups.

Microsoft provides the means and resources to help support their users while
Google provides better than average searching. I am not on the payroll of
either of these companies.

--
Duane Hookom
MS Access MVP



HomiDb said:
You are a God. Thank you - the first scenario of the two below works
great. So do you work for Google or do you just do this for fun as a
good samaritan??

Much thanks
Laurie

Duane said:
I can see that I missed a parens. You should have gotten an error
message.
Try:

SELECT Year([DetHomi]) as Yr, IncDist,
Sum(Abs(Format([DetHomi],"mmdd")<=Format(Date(),"mmdd"))) as
HomicideCount
FROM [Homicide Table]
GROUP BY Year([DetHomi]), IncDist;

You could also try:
SELECT Year([DetHomi]) as Yr, IncDist,
Count(IncDist) as HomicideCount
FROM [Homicide Table]
WHERE Format([DetHomi],"mmdd")<=Format(Date(),"mmdd")
GROUP BY Year([DetHomi]), IncDist;

--
Duane Hookom
MS Access MVP

Okay - did that and I get a zero count in the Homicide Count column for
all districts in both years. Somehow it needs to give me counts for
1/1/05-8/28/05 in each district and 1/1/06-8/28/06 in same districts...
Any other expressions to add?

Duane Hookom wrote:
Try view the sql of your query and enter:

SELECT Year([DetHomi]) as Yr, IncDist,
Sum(Abs(Format([DetHomi],"mmdd"))<=Format(Date(),"mmdd")) as
HomicideCount
FROM [Homicide Table]
GROUP BY Year([DetHomi]), IncDist;

This assumes every record in the table is a homicide.
--
Duane Hookom
MS Access MVP

Thank you! I should add that I am an access novice and do all of my
queries and reports in design view... :) I get what you are doing,
but
I am not certain how to enter that into the columns in design view
(Field/Table/Total/Sort/Show/Criteria/Or).

My variable names for this query are:

FileNo = file number
DetHomi = date
IncDist = district
Table name is Homicide Table

Can you tell me what in this string goes where? Sorry for the
ignorance...
SELECT Year([DetHomi]) as Yr, District, Sum(Abs([FileNo] =
"Homicide"
AND Format([DetHomi],"mmdd")<=Format(Date(),"mmdd")) as
HomicideCount

Duane Hookom wrote:
Since you haven't provided any information about your tables and/or
fields
try something like:

SELECT Year([CrimeDate]) as Yr, District,
Sum(Abs([Crime] = "Homicide" AND Format([CrimeDate],"mmdd")
<=Format(Date(),"mmdd")) as HomicideCount
FROM tblCrimes
GROUP BY Year([CrimeDate]) as Yr, District;


--
Duane Hookom
MS Access MVP


Hello. I am hoping someone out there can help me. I track
homicide
data and run reports for police administrators when they request
it.
I
have data from 2005 and 2006. I want to be able to give them the
count
of homicides by district to-date. So, today's report would give
a
count of 2005 homicides by district from 1/1/05 to 8/28/05 and
also
of
2006 homicides by district from 1/1/06 to 8/28/06. I know I
could
run
separate queries and enter the current date each time, but I
would
like
to have a query that I can just run and it calculates it by the
current
date automatically. Is there any way to do this? Thank you for
your
help in advance!
Click to expand...
Click to expand...
Click to expand...
 
My pleasure to be of assistance.

BTW: you shouldn't ever use your real email address in public forums. At the
very least, you should use something like:
Laurie_Woods1 AT yahoo
or similar.

I expect you don't want your email inbox to fill with spam.
--
Duane Hookom
MS Access MVP

HomiDb said:
You must really like what you do, then. The homicide Captain who
needed that data thanks you, too.

Best,
Laurie

Duane said:
I guess I "do this for fun", but also the intrinsic rewards of helping
others, camaraderie among others who help (mostly MS MVPs), and I admit
some "fame". While fellow workers take breaks in the lunch room, I head
to
the news groups.

Microsoft provides the means and resources to help support their users
while
Google provides better than average searching. I am not on the payroll of
either of these companies.

--
Duane Hookom
MS Access MVP



HomiDb said:
You are a God. Thank you - the first scenario of the two below works
great. So do you work for Google or do you just do this for fun as a
good samaritan??

Much thanks
Laurie

Duane Hookom wrote:
I can see that I missed a parens. You should have gotten an error
message.
Try:

SELECT Year([DetHomi]) as Yr, IncDist,
Sum(Abs(Format([DetHomi],"mmdd")<=Format(Date(),"mmdd"))) as
HomicideCount
FROM [Homicide Table]
GROUP BY Year([DetHomi]), IncDist;

You could also try:
SELECT Year([DetHomi]) as Yr, IncDist,
Count(IncDist) as HomicideCount
FROM [Homicide Table]
WHERE Format([DetHomi],"mmdd")<=Format(Date(),"mmdd")
GROUP BY Year([DetHomi]), IncDist;

--
Duane Hookom
MS Access MVP

Okay - did that and I get a zero count in the Homicide Count column
for
all districts in both years. Somehow it needs to give me counts for
1/1/05-8/28/05 in each district and 1/1/06-8/28/06 in same
districts...
Any other expressions to add?

Duane Hookom wrote:
Try view the sql of your query and enter:

SELECT Year([DetHomi]) as Yr, IncDist,
Sum(Abs(Format([DetHomi],"mmdd"))<=Format(Date(),"mmdd")) as
HomicideCount
FROM [Homicide Table]
GROUP BY Year([DetHomi]), IncDist;

This assumes every record in the table is a homicide.
--
Duane Hookom
MS Access MVP

Thank you! I should add that I am an access novice and do all of
my
queries and reports in design view... :) I get what you are
doing,
but
I am not certain how to enter that into the columns in design
view
(Field/Table/Total/Sort/Show/Criteria/Or).

My variable names for this query are:

FileNo = file number
DetHomi = date
IncDist = district
Table name is Homicide Table

Can you tell me what in this string goes where? Sorry for the
ignorance...
SELECT Year([DetHomi]) as Yr, District, Sum(Abs([FileNo] =
"Homicide"
AND Format([DetHomi],"mmdd")<=Format(Date(),"mmdd")) as
HomicideCount

Duane Hookom wrote:
Since you haven't provided any information about your tables
and/or
fields
try something like:

SELECT Year([CrimeDate]) as Yr, District,
Sum(Abs([Crime] = "Homicide" AND Format([CrimeDate],"mmdd")
<=Format(Date(),"mmdd")) as HomicideCount
FROM tblCrimes
GROUP BY Year([CrimeDate]) as Yr, District;


--
Duane Hookom
MS Access MVP


Hello. I am hoping someone out there can help me. I track
homicide
data and run reports for police administrators when they
request
it.
I
have data from 2005 and 2006. I want to be able to give them
the
count
of homicides by district to-date. So, today's report would
give
a
count of 2005 homicides by district from 1/1/05 to 8/28/05 and
also
of
2006 homicides by district from 1/1/06 to 8/28/06. I know I
could
run
separate queries and enter the current date each time, but I
would
like
to have a query that I can just run and it calculates it by
the
current
date automatically. Is there any way to do this? Thank you
for
your
help in advance!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top