CODEWITHSUNDEEP
chartsms-access-2010
Sinaesthetic
Sinaesthetic

Reputation: 12192

Creating a query to be used with a muti-series line chart

I have a query:

SELECT Format(dateOpened,"mmmm") AS [Month], Format(dateOpened, "yyyy") AS [Year], sum(holdQty) AS [Total]
FROM record_holdData
GROUP BY Format(dateOpened,"mmmm"), Format(dateOpened, "yyyy")
ORDER BY Format(dateOpened,"yyyy"), Format(dateOpened,"mmmm") DESC;

And what I'm trying to do is use this saved query to build a line chart in Access where each year is a different series. The x axis is the month and y axis is the hold quantity. So for example, the chart will plot a different line for 2009, 2010, 2011 that all run over the same time line, January through December. The problem that I'm running into with this query is that instead of running the same time line, it's appending the series. So for instance, it'll show january-dec 2009, then it'll start a new line at the end of that one for 2010. Can someone show me how to fix this and maybe explain the error in my logic here? TIA

Upvotes: 2

Views: 620

Answers (1)

Rachel Hettinger
Rachel Hettinger

Reputation: 8442

Try using a crosstab query to separate the years into separate data series:

TRANSFORM Sum(holdQty) AS Total
SELECT Format([dateOpened],"yyyy") AS [Year]
FROM record_holdData
GROUP BY Format([dateOpened],"yyyy")
PIVOT Format([dateOpened],"mm-mmmm");

Upvotes: 1

Related Questions