We are compiling feedback data from a web site. We track general categories
of feedback or questions, and have used Access queries to group them by
calendar month and by category or sub-category (i.e. "general inquiry",
"password problem", "can't locate document")

We want to graphically represent these in charts, but some of the queries
have more than six categories or columns, so we can't use the tool from
within Access.

The solution is to link Excel worksheets with charts to dynamic queries in
the Access database.

This worked fine for the categories (18 of them). When a new month has
elapsed, the charts are automatically expanding. Fine.

The problem comes when working with sub-categories. Some months there are
no feedbacks for a particular family of comments, so on the crosstab queries
in Access, it simply doesn't create the row. Same if a particular colum has
no entries so far - the column disappears.

We want our charts to be consistent, so we want all columns and all rows to
show up in charts, even if the chart shows a drop to zero. To make sure the
Access crosstab query reflects this, I use the "Nz" function to fill with
zeros when a null value exists, and the queries look great in Access.

When I go to grab the "external data" in Excel, I get an error for
"unrecognized function 'Nz'" and can't creat the link to the Access data. It
does if there is either the Nz call in the actual query, or if it's in any of
the queries feeding into the query.

I don't want my end user to have to manually change the chart definitions,
so cutting and pasting is out.

Does anyone know how to work around this problem, and does anyone fathom why
Excel wouldn't recognize a function from a "sister" application?
Frustrating, but I'm hoping my frustration is born from ignorance. Any help
is appreciated.

T

I worked around the "Nz" issue by using "IIf" and "IsNull()" statements.
Also, I had to make sure it wasn't just in the crosstabs, but in the primary
queries upon which the crosstabs were built.

"aemAndy" wrote:

We are compiling feedback data from a web site. We track general categories
of feedback or questions, and have used Access queries to group them by
calendar month and by category or sub-category (i.e. "general inquiry",
"password problem", "can't locate document")

We want to graphically represent these in charts, but some of the queries
have more than six categories or columns, so we can't use the tool from
within Access.

The solution is to link Excel worksheets with charts to dynamic queries in
the Access database.

This worked fine for the categories (18 of them). When a new month has
elapsed, the charts are automatically expanding. Fine.

The problem comes when working with sub-categories. Some months there are
no feedbacks for a particular family of comments, so on the crosstab queries
in Access, it simply doesn't create the row. Same if a particular colum has
no entries so far - the column disappears.

We want our charts to be consistent, so we want all columns and all rows to
show up in charts, even if the chart shows a drop to zero. To make sure the
Access crosstab query reflects this, I use the "Nz" function to fill with
zeros when a null value exists, and the queries look great in Access.

When I go to grab the "external data" in Excel, I get an error for
"unrecognized function 'Nz'" and can't creat the link to the Access data. It
does if there is either the Nz call in the actual query, or if it's in any of
the queries feeding into the query.

I don't want my end user to have to manually change the chart definitions,
so cutting and pasting is out.

Does anyone know how to work around this problem, and does anyone fathom why
Excel wouldn't recognize a function from a "sister" application?
Frustrating, but I'm hoping my frustration is born from ignorance. Any help
is appreciated.

T