Pivot Chart/Crosstab Query/Other?

[Tarnish]

Hi all,

How can I get years to appear as separate lines in a line graph in a pivot chart?

I have a date in records that is the date the record was opened. I have multiple forms displayed as pivot charts in separate subreport controls on a report.

One of the pivot charts currently charts the files that are opened over the last 90 days for a user selected facility (each main file has a field that represents the facility).

What I would like is to have months displayed on the x-axis, but instead of having the x-axis span multiple years, I'd like records from different years to appear as different lines in the graph.

So, for my 'last 90 days' chart, what I'd really like one line for the last 90 days, one for the same period last year, and one for the same period the year before that (assuming three years worth of records). That would mean that the graph would only display at most 4 months in the x-axis.

For the 'all time' chart, 12 months would be displayed (Jan-Dec)but no duplicate months (since records from different years would have their own lines).

Is that possible? Do I need to pull out the years separate from the months in the date field? Would a crosstab query make this possible?

Thanks for any feedback,
T

 

[SkipVought]

Hi,

Query your data. Then in the PivotTable, GROUP the date field on MONTH and YEAR. (At least it would work that way in an Excel PT)

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Tarnish]

Thanks for the reply, Skip.

I've sort of been sidetracked, but I just wanted to acknowledge your suggestion and I'll post back when I have a chance to fully investigate it.

I'm a little skeptical because I'm using a pivot chart which seems to operate a little differently, but without a chance to kick around some more I'm not sure.

I do appreciate the suggestion. I've found that getting real help with charts in Access is about the biggest challenge I've faced in terms of getting help on boards or from books. I have a small library of access books, including the two-book developer's handbook set, and I still haven't been able to figure out how to get what I want. I've posted three different questions over the last six months and I think your reply was the first ever!

Thanks,
T