Comparing two different years' data with a line chart

[craigfr]

I am looking to compare this year's defects to last year's defects (by month) with a two-line graph. So I will need a line chart with two different lines, one that shows YTD # of defects and another for last year's # of defects. The X axis is Months (no years because of comparison), and the Y axis is # of defects.

Right now I have one query which outputs # of defects by day for all years.

I also have two other querys, one which shows last year's defect data and another YTD defect data.

Is there a way to graph these on a line graph with a common (month) X-axis?

Thanks

[craigfr]

I'm still looking for ideas... is there any way to do this?

[craigfr]

Does no one really have any idea how to do this?

[TSIGOS1]

Why Dont Try Puting Them In One Query?

[rwalle]

I would suggest a totalized query for each year , then combine both into another query so now you have 2 rows one for each year, and 12 columns one for each month and you are done, use years as the Y axis and time ( month grouped) as X axis

[Mariostg]

Something like:

SELECT Count(Defect),theMonth,TheYear FROM DefectTable
Group BY theMonth, TheYear
 

It will give you the count of defect for each each month of each year. Then Bring this into Excel and create different series for each year.

[Stewart Ross]

This kind of task is what crosstab queries in Access are very good at.

Set up a crosstab query in which the total number of defects in a month is calculated as the value field, the column heading is the month number (using the Month function on the defect date to return the month number) and the year is the left-most of the row fields (using the Year function on the defect date to return the year). You will then have rows which are year-oriented returning columns which are monthly defect totals, for months numbered 1 to 12 (assuming that there are no months in the dataset for which there were no defects).

Excel is indeed the best tool then for comparing these using line graphs, with each year a separate series on the same chart.

-Stewart