Combi Chart in 2007

[agibsonsw]

Hello. Access 2007.
For a particular ProjectID I have Costs over a four month period as well as Forecast costs.
Is it possible to create a combi-chart based on these values (and their cumulative figures)?
That is, I would like to use a clustered column chart for the original figures and at the
same time lines for the cumulative figures.
Should I add a Chart object to a form or use a Pivot Chart? I suppose I need to use a
PivotChart in order to work with cumulative figures?
Thanks in advance for any guidance. Andy.

[HansV]

You could use a pivot chart but I can't help you with that - I thoroughly dislike the implementation of pivot tables and pivot charts in Access.

You could create a pivot table and pivot chart in Excel based on an Access table or query. I find the Excel versions much easier to work with than their Access counterparts.

Another possibility is to create a query in Access (perhaps a crosstab query) that returns the data you need, and use this as row source for a chart object on a form. This has the advantage of keeping it entirely within Access, but it could be more complicated to set up.

[agibsonsw]

Hello, and thank you.
I agree - Access Pivot tools are v bad.
I managed to create the chart I need as a chart object on a form, but I'm currently
manually entering the cumulative figures.
Is it possible to create cumulative columns in a query? Ta, Andy.

[HansV]

Yes, there are ways to calculate cumulative figures in a query, but how exactly depends on the structure of the data.
You could use something like this (it's "air code", of course):

CumulativeTotal: DSum("FieldName", "TableOrQuery", "MonthDate<=#" & Format([MonthDate], "mm/dd/yyyy") & "#")

[agibsonsw]

Can't quite get this to work.. Should I be trying to do this in a Totals query (using 'Expression')
or in a simple select query?
More info: I have a table with fields ProjID, ProjMonth (which are just numbers 1 through 4),
Actual and Forecast.
Ta again, And.

[HansV]

Could you attach a small sample database (compacted and zipped)?

[agibsonsw]

Hello. Attached.
I referred to a field called Actual, but this is named FIF.
I think I need to create a Totals query, and then perhaps a second query
based on this that uses DSUM()?
Ta, Andy.

Hey, no worries!
I got it to work using a Totals query and then a second query using the expression:
DSum("FIF","tblProjectCosts","ProjID=" & [ProjID] & " AND ProjMonth<=" & [ProjMonth])
It displays as a text field, but I set the format of this calculated field to 0 to treat
it as a number.
Thanks for your assistance, Andy.

[HansV]

See the attached version. I created a select query qryProjectCosts that uses DSum to calculate the cumulative totals, and used this query in the row source of the chart. There's no totals query involved.

Project1.zip