Help needed with Query to Graph output in Report (1 Viewer)

[hardrock]

Hi all, this probably an easy request, but I’ve been scratching my head for a few hours to sort this little problem I have. Below is a scaled down output from my query, I’d like to be able to plot a line graph in a report, that would show Y axis as “CountOfQTY” and X axis “Posting date” , but in the format of Month year. So for Jan 07 my graph would show a QTY of 13 and Feb 07 would show QTY of 5 etc.. Could someone please advise me the best way to do this. Thanks


Part PostingDate CountOfQTY
Widget 18/01/2007 6
Widget 25/01/2007 7
Widget 12/02/2007 1
Widget 15/02/2007 1
Widget 26/02/2007 3
Widget 11/06/2007 4
Widget 19/06/2007 1
Widget 20/06/2007 1
Widget 03/07/2007 6
Widget 04/07/2007 3
Widget 11/07/2007 1
Widget 19/07/2007 5
Widget 23/07/2007 1
Widget 24/07/2007 4
Widget 02/08/2007 5
Widget 13/08/2007 2
Widget 14/08/2007 2
Widget 16/08/2007 4
Widget 21/08/2007 5
Widget 28/08/2007 1
Widget 31/08/2007 5
Widget 11/09/2007 10

 

[stopher]

First you need to get your data into the right shape. By this I mean you need to summarise the data by month/year.

The following aggregate query does this by grouping on a formatting expression of the date. The format expression will make the date appear in mmm yy format.

The problem with just this format is that your periods will not be in the right order (because feb comes before jan alphabetically). So I’ve included another column this time formatted mmyy. This will appear numeric and therefore can be sorted in the right order (even though we don’t show it).

SELECT Part, Format([PostingDate],"mmm yy") AS Period, Sum(CountOfQty) AS TotalQty
FROM yourQuery
GROUP BY Part, Format([PostingDate],"mmyy"), Format([PostingDate],"mmm yy")

You can then use the chart wizard on this query to place your graph in your report.

Hth
Chris

 

[hardrock]

Thankyou so much! just one problem though, Using the code you gave me, the data in "period" is not in logical order when i run the query e.g i have APR 08 first then AUG 07 then Aug 08 etc. I tried doing a sort but no luck. The code i used is:

SELECT test.Part, Format([PostingDate],"mmm yy") AS Period, Sum(test.CountOfQTY) AS TotalQty
FROM test
GROUP BY test.Part, Format([PostingDate],"mmm yy"), Format([PostingDate],"mmyy")
ORDER BY Format([PostingDate],"mmm yy");

Any ideas?

 

[stopher]

GROUP BY test.Part, Format([PostingDate],"mmm yy"), Format([PostingDate],"mmyy")

Any ideas?

The sort order will be according to the order you list them in the GROUP BY line.

You have "mmm yy" first then "mmyy" so it will sort in the wrong order. Switch the two statements round like this:

GROUP BY test.Part, Format([PostingDate],"mmyy"), Format([PostingDate],"mmm yy")

hth
Chris