Chart Sort by Date 2

[Mugs321]

Hey all,
I'm trying to use a chart to display fiscal data. Everything is working, however, the date, on the bottom axis(formatted as Apr '06 etc..), is being sorted alphabetically. (ie Apr, Aug, Dec etc...).

I'm trying to sort this by month (Apr, May, Jun etc...) but every time I try, I get the 'ol "Query does not include specified expression 'krDate' as part of an aggregate function."

I am familiar with SQL but I cannot get this chart to sort the way I want it.

This is the SQL that the Chart Wizard created (the ORDER BY krs.krDate) was added by myself and creates the error:

SELECT (Format([krDate],"mmm"" '""yy")) AS Expr1, Sum(krs.krAmount) AS SumOfkrAmount
FROM krs
WHERE (((krs.krDate) Between calcDateByPeriodFrom() And calcDateByPeriodTo()))
GROUP BY (Format([krDate],"mmm"" '""yy")), (Year([krDate])*12+Month([krDate])-1)
ORDER BY krs.krDate;

I tried using ORDER BY (Format([krDate],"mmm"" '""yy")) but once again it's sorting alphabetically.

Thx in advance,
Dave

 

[SgtJarrow]

Try

ORDER BY Year([krDate]), Month([krDate])

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[Mugs321]

Thanks for the reply.

Unfortunately, the error is still there:

"...'Year([krDate])' not part of an aggregate function.

 

[SgtJarrow]

Can you post some sample data and expected results??? That way I can help tailor it specifically to your data.


=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[SgtJarrow]

Here is the query...minus the WHERE clause

SELECT Format([krDate],"mmm yy") AS Expr1, Sum(krs.krAmount) AS SumOfkrAmount
FROM krs
GROUP BY Format([krDate],"mmm yy"), (Year([krDate])*12+Month([krDate])-1), Year([krDate]), Month([krDate])
ORDER BY Year([krDate]), Month([krDate]);

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[Mugs321]

Table Data:

kr_id krDate krNumber krAmount
===================================================
22 01-Apr-06 K456486458 $15,660.00
24 01-Jun-06 K456486460 $1,000.00
25 01-May-06 K456486461 $10,000.00
28 01-Sep-06 K456486464 $23,000.00


From this data I would like a vertical bar-graph produced with SUM(krAmount) on the vertical axis and Date(ie Apr '06) on the horizontal. I would like the horizontal axis ordered by date. kr_id and krNumber are insignificant.

Amount |
|
|
|
-------------
Apr '06

 

[Mugs321]

Cancel that last post...

This your query worked perfectly! Thanks a bunch!

 

[PHV]

doesn't this work ?
GROUP BY (Format([krDate],"mmm"" '""yy")), (Year([krDate])*12+Month([krDate])-1)
ORDER BY (Year([krDate])*12+Month([krDate])-1);

ie, no need to add fields in the GROUP BY clause ...


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Mugs321]

lol... yeah that works too..

Silly me...