Bar charts in Access comparing a specific detail item to a group.

B

Brad

I have been fighting with charting in access for a while and have read
the charting posts in this group, but still can't seem to figure it
out.

I have data that is built dynamically into a table for the sole
purpose of charting the result in various ways (bar, stacked bar,
etc.)

The data is broken down by year, state, county and provider and we
want to show a charted breakdown of demographics within the county.
The ultimate goal is to show a bar chart for each county showing a
selected provider against the totals for the county.

To do this I originally thought I'd need to do a union query to seelct
just the provider and then the aggregate for each county.

I couldn't get that to work so I just tried to get the county bar in a
group at the top and the provider bars seaprately in the detail.

I can get the group for teh county to properly show the breakdown for
each county, but I can't get the provider gorup or the detail to show
anything meaningful. However I cahnge my row source the data never
seems to change.

The data is below(Sorry for the formatting, I couldn't figure out how
to get teh columns aligned.)

FileYear StateCode CountyCode Provider_Number
Provider_Name State County zWhite zBlack zHispanic zAmer_Ind
zOriental
2007 32 000 000000 ZZ_Other NM BERNALILLO 819
17 77 3 18
2007 32 000 321601 HH1 NM BERNALILLO 511 6
31 1 11
2007 32 000 321602 HH2 NM BERNALILLO 458 11
41 2 16
2007 32 000 321603 HH3 NM BERNALILLO 276 8
18 2 9
2007 32 060 000000 ZZ_Other NM DONA ANA 371 4
65 1 7
2007 32 060 321603 HH3 NM DONA ANA 32 0
12 0 1
2007 32 070 000000 ZZ_Other NM EDDY 308 5
36 0 7
2007 32 070 321601 HH1 NM EDDY 1 0 0 0
0
2007 32 070 321602 HH2 NM EDDY 1 0 0 0
0
2007 32 070 321603 HH3 NM EDDY 1 0 0 0
0

Ideally, for each county, there should be a bar chart that has two
bars: one for the county totals and one for the selected provider.
which could be any of the three shown.

At one point I had a row source of:
SELECT Min(tbl_Grph_HP01.Provider_Name) AS Provider_Nam,
Sum(tbl_Grph_HP01.zwhite) AS White, Sum(tbl_Grph_HP01.zblack) AS
Black, Sum(tbl_Grph_HP01.zhispanic) AS Hispanic,
Sum(tbl_Grph_HP01.zAmer_Ind) AS [Amer Ind],
Sum(tbl_Grph_HP01.zOriental) AS Asian FROM tbl_Grph_HP01 GROUP BY
tbl_Grph_HP01.Provider_Number
HAVING
(((tbl_Grph_HP01.Provider_Number)=[Forms]![frmMainInput]![FrmProvider1]
) )

UNION SELECT Min(county) as Provider_Nam, Sum([zwhite]) AS White, sum(
[zblack]) AS Black, sum( [zhispanic]) AS Hispanic, sum( [zAmer_Ind])
AS [Amer Ind], sum( [zOriental]) AS Asian FROM tbl_Grph_HP01
HAVING (((tbl_Grph_HP01.County)=[reports]![rptGraphtest1]![rptCounty]
));
With that above row source, I get the appropriate legend, but no data.

Any ideas would be most helpful.
Thanks,
Brad
 
D

duanehookom

I generally create a report with a record source the contains the
unique records and fields that I can use to Link Master/Child to the
chart control on the report. The chart control has an option in design
view to display Rows or Columns. I'm not sure which you want to select
but my data is generally more normalized so the "race" becomes a field
value rather than a name.

I'm not exactly sure what you want to display regarding the two bars
but I don't think this would be too difficult. You may need to use a
standard join rather than a union query.

Duane Hookom
MS Access MVP

I have been fighting with charting in access for a while and have read
the charting posts in this group, but still can't seem to figure it
out.

I have data that is built dynamically into a table for the sole
purpose of charting the result in various ways (bar, stacked bar,
etc.)

The data is broken down by year, state, county and provider and we
want to show a charted breakdown of demographics within the county.
The ultimate goal is to show a bar chart for each county showing a
selected provider against the totals for the county.

To do this I originally thought I'd need to do a union query to seelct
just the provider and then the aggregate for each county.

I couldn't get that to work so I just tried to get the county bar in a
group at the top and the provider bars seaprately in the detail.

I can get the group for teh county to properly show the breakdown for
each county, but I can't get the provider gorup or the detail to show
anything meaningful.  However I cahnge my row source the data never
seems to change.

The data is below(Sorry for the formatting, I couldn't figure out how
to get teh columns aligned.)

FileYear        StateCode       CountyCode      Provider_Number
Provider_Name   State   County  zWhite  zBlack  zHispanic zAmer_Ind
zOriental
2007    32      000     000000  ZZ_Other        NM      BERNALILLO 819
17      77      3       18
2007    32      000     321601  HH1     NM      BERNALILLO      511 6
31      1       11
2007    32      000     321602  HH2     NM      BERNALILLO      458 11
41      2       16
2007    32      000     321603  HH3     NM      BERNALILLO      276 8
18      2       9
2007    32      060     000000  ZZ_Other        NM      DONA ANA 371 4
65      1       7
2007    32      060     321603  HH3     NM      DONA ANA        32 0
12      0       1
2007    32      070     000000  ZZ_Other        NM      EDDY    308 5
36      0       7
2007    32      070     321601  HH1     NM      EDDY    1       0 0 0
0
2007    32      070     321602  HH2     NM      EDDY    1       0 0 0
0
2007    32      070     321603  HH3     NM      EDDY    1       0 0 0
0

Ideally, for each county, there should be a bar chart that has two
bars: one for the county totals and one for the selected provider.
which could be any of the three shown.

At one point I had a row source of:
SELECT Min(tbl_Grph_HP01.Provider_Name) AS Provider_Nam,
Sum(tbl_Grph_HP01.zwhite) AS White, Sum(tbl_Grph_HP01.zblack) AS
Black, Sum(tbl_Grph_HP01.zhispanic) AS Hispanic,
Sum(tbl_Grph_HP01.zAmer_Ind) AS [Amer Ind],
Sum(tbl_Grph_HP01.zOriental) AS Asian FROM tbl_Grph_HP01 GROUP BY
tbl_Grph_HP01.Provider_Number
HAVING
(((tbl_Grph_HP01.Provider_Number)=[Forms]![frmMainInput]![FrmProvider1]
) )

UNION SELECT Min(county) as Provider_Nam, Sum([zwhite]) AS White, sum(
[zblack]) AS Black, sum( [zhispanic]) AS Hispanic, sum( [zAmer_Ind])
AS [Amer Ind], sum( [zOriental]) AS Asian FROM tbl_Grph_HP01  
HAVING (((tbl_Grph_HP01.County)=[reports]![rptGraphtest1]![rptCounty]
));
With that above row source, I get the appropriate legend, but no data.

Any ideas would be most helpful.
Thanks,
Brad
Click to expand...
 
Top