stacked column chart based on a crosstab query in an Access report

[pietlinden]

A2003, Excel2003

I'm trying to create a stacked column chart based on a crosstab query
in an Access report (didn't I just say that?) Anyway... I can shove
the data into Excel, or tell Excel to query my DB just fine. To make
things easier to follow, I'll give a bit of detail. I'm graphing
demand for a service across several cities, so in my crosstab, cities
are in the first column and then needs go across the rows and the
counts go down...

here's the smallest example I can get

City Dallas Garland
----------------------------------------------------------------
Child Care 1
Respite 1 1
Wheelchair Ramp 1 2


What I wanted was totals demand for each city, like this:

City Dallas Garland
----------------------------------------------------------------
Child Care 1
Respite 1 1
Wheelchair Ramp 1 2
--------------------------------------------------------
3 3
==============================

I can do this part in a report just fine in Access, but what I really
wanted was the graph of total demand by city using a stacked bar chart,
because that shows what I mean/want perfectly. Only problem is I can't
figure out how to change the graph.

If I dump the data to Excel, graphing it is simple. transpose data,
stack the bar chart, and I'm done. the problem there is that if I add
new services, they don't show up.

as I see it my options are:
1. figure out how the graphing engine works (got any online
resources/book recommendations?)
2. dump the data to Excel with a sufficient number of records to get a
good sample. (if possible).

Any ideas how to sort this problem out?

thanks!
Pieter