Creating Charts from Querys

[iceman_uk]

Hi, I have done a search before posting but cannot find what I am looking for.

At work I use a database to record incidents (work that I do). I have written a query for incidents for 2007 and 2008 and I can do a line graph for each year showing the monthly incidents by doing Insert, Form, Chart Wizard

What I am trying to do is a line graph for 2007 and 2008 together to compare performance but i can only ever choose one query for my graph, either 2007 or 2008.

Is there a way I can get both years on one graph so as i can compare performance.

Any help/guidance appreciated.

 

[KenHigg]

I think if you have two rows of data in the query that the graph is based on the graph engine will figure it out for you (less your formatting of course)...

 

[iceman_uk]

Thanks for your reply.

However, I cannot seem to get a query to give me dates for 2007 and 2008 as two separate rows, I can only do each year separate or as one total.

Is there a way a Query can be written to show dates from 2007 and 2008? I have tried......unsuccessfully

Thanks

 

[KenHigg]

Sorry, I meant two columns.

 

[checker]

Create a crosstab query of the incidents and then make a new table based of the query results (the charts can't read from crosstabs in 2003) then use the crosstab table as the chart source.

You'll have to rerun the make crosstab query whenever incident data changes or whenever you need to run a new report

 

[iceman_uk]

Thanks for the replys.

Will have a go at the crosstab query.

Other than input my incidents I have not done anything else in Access before so I may be back!!

 

[iceman_uk]

I have been trying crosstab queries without success.

I would be grateful if anyone could give any more pointers or guidance.

I only need the date field, no others.

My all allincidents query gives me all dates from Feb07 to today

My 2007 dates query is >#31/01/2007# And <#01/01/2008#

My 2008 dates query is >#31/12/2007# And <#01/01/2009#

Is there anyway of linking/combining these so as i can get all the information in two columns or similar.

Thanks again.

 

[Rich]

Why do you have two separate queries?

 

[checker]

Ok - what you need to arrive at is a table that looks like this:

Month|Year 2007|Year 2008
01 -jan| 100 | 120
02 - feb| 98 | 115

For each year, you want to list out the number of incidents by month.

1) buid a select query that pulls in all your incidents and add two new columns to it: Incident Year: Format([incident date], "YYYY") and Incident Month: Format([incident date],"MM")

Results of this query should look like this example:
Incident # Incident Date Incident Year Incident Month
12345 12/3/2007 2007 12
12346 1/1/2008 2008 01
12347 1/15/2008 2008 01
12348 12/15/2007 2007 12
.
.
.
2) Create a crosstab query of the query from step 1.
You'll have the months as a row heading and Year as column heading, and then just a count of the incident # as the data. this should render the data in a way suitable for your chart.

3) create a table based off of your crosstab query.

 

[iceman_uk]

Thanks for the reply and suggestions. Will have a look at that during the day.