If anyone could help this would save me a *ton* of work every month, and score me big points with my boss!

Basically every month we need to produce a chart showing lost time on jobs - it shows lost time categories, by the Cost Centre that booked the lost hours, and the total hours lost.

At present we have to manually put the figures into Excel each month, from a printed report, and chart it that way. If I can get Access to do it automatically I'd cry with joy!

I have a query which extracts the data needed from the ODBC table of our time recording system's tables. So it has Year, week, cost centre, category and total hours.

Access charts only seem to handle it where you have an X-axis, Y axis, and value.

I need total hours as my Y-axis, lost time category as the X-axis, and the value will be the hours, but one bar per category for each cost centre.

So against Ship's Staff (category for lost time) I could, say, have a blue bar for 5110, red for 5121, etc, same for Waiting on welders, same for Weather, etc.

Hope I make sense, thanks.

I very much prefer using Excel to make my charts, to the point where I would EXPORT the query to an Excel workbook, make the chart, and then Embed the chart into the form or report where I actually want it to live. I think you can handle this situation with a Clustered Bar Chart.

I thought about that, but the trouble is the data is "dynamic", in the sense that when I export the data from Access, it won't always go into the same cell ranges in Excel, obviously depending how many entries there are, etc, so I can't really set up the chart source range in advance.

You might be able to handle that with an AutoOpen macro in the Excel workbook. You would need to know one cell in the area where the data was landing when the export happens, the macro could then select the data and assign a range name to it. The chart could then use the range name.

Ok, thanks - can I be cheeky and ask for some pointers on how to go about this?

Ok, I'm having a little success - I wrote a query that extracts the info I need (with relevant prompts for Between <week> And <week> and makes a table with it. I then have another query (cross-tab) based on the new table that displays the info and exports it to Excel (this whole process is automated in a macro) - as it's then in cross-tab form I can then chart it in Excel easily - this has made my life easier no end.

My one annoyance is that there are Null values in some of the "cells" in my cross-tab, which obviously appear as blank cells when I export to Excel - the annoyance is I have to manually fill each blank in Excel with a 0 before the chart will work.

Any ideas?

You might try using the Nz function in the query that extracts the data for the Crosstab. Nz converts a Null value in a field to whatever else you might want it to be.

Expr1: Nz([FieldNameGoesHere], 0), would convert a null in the field to a 0. Then obviously base the Crosstab on the fields that you have used the Nz function on.

Thanks.

Here's what happens:

The field I need to convert the nulls to 0 currently reads:

Reg: Sum(CDbl([Routine]))

Making it:

Reg: Nz(Sum(CDbl([Routine])),0)

Produces the error "invalid data type"

This, from here, seems to work in the Field: row:

Expr1: IIF(Sum([Fieldname]) Is Null,0,Sum([Fieldname]))

Nz doens't seem to work in crosstab queries.