Need to Sum Across Categories for a Rport

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello All:

This has got to be much easier than I'm making it, but for the life of me I
just can't see it.

I need to create a report that summarizes data by month across years; I have
data for 2000-2004 and can run crosstab queries to summarize by month and
individual year for the four weapons categories. I used those to create
subreports and embedded them in the main report. No problem. Got that far.

Now, I want to sum all of the month 1's, 2's, 3's, etc. across years, going
down. the report looks like this:

Firearms Seized by DPD, 2000-200
January February March April May June July August September October November December
2004 Firearms
Handguns 54 41 61 38 44 56 78 32 48 43 43
Other firearms 1 2 3 2 4 2 3 2
Rifles 11 15 14 7 9 6 7 7 9 3 9
Shotguns 10 14 8 10 8 9 11 7 8 6 7
2003 Firearms
Handguns 64 43 52 49 46 33 55 43 37 29 37 48
Other firearms 1 5 3 4 4 3 1
Rifles 12 5 2 12 7 6 19 8 10 6 9 8
Shotguns 5 6 6 11 11 6 12 12 5 15 8 9


etc.

I can generate a crosstab for all years, but can't figure how to sum all the
wea[pons categories, instead of breaking out by categories.

Hope this post makes sense; i've been at this sucker for a while and am
slightly punchdrunk.

Many thanks for all assistance.
 
Create a query that includes Category, MonthSeized and NumberSeized. Change
the query to a totals query by clicking on the Sigma(looks like E) button on
the top of the screen. Under NumberSeized, change Group By to Sum. When you
run this query you will get TotalNumberSeized by Category and Month. Create
a crosstab query based on this query and you will get what you want.
 
Thanks. Unfortunately, the table isn't structured like that. Each field has
one of four weapons categories, where the data is text, not numeric
("Handgun", "Rifle", etc.) So I need tocount instances of "Handgun" by month.

What I've finally done is to create 12 individual qrys, for each month and
embed those in the main report. That's really sloppy, but it does work.

Thanks again.

PC Datasheet said:
Create a query that includes Category, MonthSeized and NumberSeized. Change
the query to a totals query by clicking on the Sigma(looks like E) button on
the top of the screen. Under NumberSeized, change Group By to Sum. When you
run this query you will get TotalNumberSeized by Category and Month. Create
a crosstab query based on this query and you will get what you want.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com


dave roth said:
Hello All:

This has got to be much easier than I'm making it, but for the life of me I
just can't see it.

I need to create a report that summarizes data by month across years; I have
data for 2000-2004 and can run crosstab queries to summarize by month and
individual year for the four weapons categories. I used those to create
subreports and embedded them in the main report. No problem. Got that far.

Now, I want to sum all of the month 1's, 2's, 3's, etc. across years, going
down. the report looks like this:

Firearms Seized by DPD, 2000-2004
January February March April May June July August September October November December
2004 Firearms
Handguns 54 41 61 38 44 56 78 32 48 43 43
Other firearms 1 2 3 2 4 2 3 2
Rifles 11 15 14 7 9 6 7 7 9 3 9
Shotguns 10 14 8 10 8 9 11 7 8 6 7
2003 Firearms
Handguns 64 43 52 49 46 33 55 43 37 29 37 48
Other firearms 1 5 3 4 4 3 1
Rifles 12 5 2 12 7 6 19 8 10 6 9 8
Shotguns 5 6 6 11 11 6 12 12 5 15 8 9


etc.

I can generate a crosstab for all years, but can't figure how to sum all the
wea[pons categories, instead of breaking out by categories.

Hope this post makes sense; i've been at this sucker for a while and am
slightly punchdrunk.

Many thanks for all assistance.
Click to expand...
Click to expand...
 
It's good to hear you got a workaround. The root cause of the problem is
that the table structure is incorrectly designed. You were lucky to be able
to come up with a "sloppy" solution. Many times, a solution is not possible
when the tables are not normalized.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com


dave roth said:
Thanks. Unfortunately, the table isn't structured like that. Each field has
one of four weapons categories, where the data is text, not numeric
("Handgun", "Rifle", etc.) So I need tocount instances of "Handgun" by month.

What I've finally done is to create 12 individual qrys, for each month and
embed those in the main report. That's really sloppy, but it does work.

Thanks again.

PC Datasheet said:
Create a query that includes Category, MonthSeized and NumberSeized. Change
the query to a totals query by clicking on the Sigma(looks like E) button on
the top of the screen. Under NumberSeized, change Group By to Sum. When you
run this query you will get TotalNumberSeized by Category and Month. Create
a crosstab query based on this query and you will get what you want.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com


dave roth said:
Hello All:

This has got to be much easier than I'm making it, but for the life of
Click to expand...
me
I
just can't see it.

I need to create a report that summarizes data by month across years;
Click to expand...
I
have
data for 2000-2004 and can run crosstab queries to summarize by month and
individual year for the four weapons categories. I used those to create
subreports and embedded them in the main report. No problem. Got
Click to expand...
that
far.
Now, I want to sum all of the month 1's, 2's, 3's, etc. across years, going
down. the report looks like this:

Firearms Seized by DPD, 2000-2004
January February March April May June July August September October November December
2004 Firearms
Handguns 54 41 61 38 44 56 78 32 48 43 43
Other firearms 1 2 3 2 4 2 3 2
Rifles 11 15 14 7 9 6 7 7 9 3 9
Shotguns 10 14 8 10 8 9 11 7 8 6 7
2003 Firearms
Handguns 64 43 52 49 46 33 55 43 37 29 37 48
Other firearms 1 5 3 4 4 3 1
Rifles 12 5 2 12 7 6 19 8 10 6 9 8
Shotguns 5 6 6 11 11 6 12 12 5 15 8 9


etc.

I can generate a crosstab for all years, but can't figure how to sum
Click to expand...
all
the
wea[pons categories, instead of breaking out by categories.

Hope this post makes sense; i've been at this sucker for a while and am
slightly punchdrunk.

Many thanks for all assistance.
Click to expand...
Click to expand...
Click to expand...
 
Yeah, I'm importing a report generated from an AS-400 into Access; it's easy
enough to do what I want in Excel from the report as it comes in from the
AS-400.

Thanks again. i may take a look at redesigning the dbase tables.

PC Datasheet said:
It's good to hear you got a workaround. The root cause of the problem is
that the table structure is incorrectly designed. You were lucky to be able
to come up with a "sloppy" solution. Many times, a solution is not possible
when the tables are not normalized.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com


dave roth said:
Thanks. Unfortunately, the table isn't structured like that. Each field has
one of four weapons categories, where the data is text, not numeric
("Handgun", "Rifle", etc.) So I need tocount instances of "Handgun" by month.

What I've finally done is to create 12 individual qrys, for each month and
embed those in the main report. That's really sloppy, but it does work.

Thanks again.

PC Datasheet said:
Create a query that includes Category, MonthSeized and NumberSeized. Change
the query to a totals query by clicking on the Sigma(looks like E) button on
the top of the screen. Under NumberSeized, change Group By to Sum. When you
run this query you will get TotalNumberSeized by Category and Month. Create
a crosstab query based on this query and you will get what you want.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com


Hello All:

This has got to be much easier than I'm making it, but for the life of me
I
just can't see it.

I need to create a report that summarizes data by month across years; I
have
data for 2000-2004 and can run crosstab queries to summarize by month and
individual year for the four weapons categories. I used those to create
subreports and embedded them in the main report. No problem. Got that
far.

Now, I want to sum all of the month 1's, 2's, 3's, etc. across years,
going
down. the report looks like this:

Firearms Seized by DPD, 2000-2004
January February March April May June July August September October
November December
2004 Firearms
Handguns 54 41 61 38 44 56 78 32 48 43 43
Other firearms 1 2 3 2 4 2 3 2
Rifles 11 15 14 7 9 6 7 7 9 3 9
Shotguns 10 14 8 10 8 9 11 7 8 6 7
2003 Firearms
Handguns 64 43 52 49 46 33 55 43 37 29 37 48
Other firearms 1 5 3 4 4 3 1
Rifles 12 5 2 12 7 6 19 8 10 6 9 8
Shotguns 5 6 6 11 11 6 12 12 5 15 8 9


etc.

I can generate a crosstab for all years, but can't figure how to sum all
the
wea[pons categories, instead of breaking out by categories.

Hope this post makes sense; i've been at this sucker for a while and am
slightly punchdrunk.

Many thanks for all assistance.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top