year-to-date count for multiple years

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

 

[mscertified]

Just use the Date() builtin function in your query.

Dorian

 

[Duane Hookom]

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;

 

[HomiDb]

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

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!

 

[Duane Hookom]

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

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!

 

[HomiDb]

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?

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

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!

 

[Duane Hookom]

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?

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!

 

[HomiDb]

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

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?

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!

 

[Duane Hookom]

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

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

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!

 

[HomiDb]

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

Best,
Laurie

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

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

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!

 

[Duane Hookom]

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

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

Best,
Laurie

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

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!