If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Graph displaying dates alphabetically on x axis
Hello!
Title says it all - I want the x axis to show dates in DATE ORDER, not alphabetical order. I've read thru the forum, can't see how to fix it - your help will be appreciated. Kind Regards, Russell. p.s. Here is the graph Row Source, should there be an ORDER BY statement in here? SELECT (Format([Date Paid],"mmm"" '""yy")) AS Expr1, Sum([Tenant Payments]. Amount) AS SumOfAmount FROM [Tenant Payments] GROUP BY (Format([Date Paid], "mmm"" '""yy")), [Tenant Payments].[Date Paid], (Year([Date Paid])*12+Month( [Date Paid])-1) HAVING ((([Tenant Payments].[Date Paid])=#3/1/2009#)); -- Message posted via http://www.accessmonster.com |
#2
|
|||
|
|||
Graph displaying dates alphabetically on x axis
Try this SQL for the Row Source:
SELECT Format([Date Paid],"mmm"" '""yy") AS Expr1, Sum([Tenant Payments].Amount) AS SumOfAmount FROM [Tenant Payments] Where [Date Paid] =#3/1/2009# GROUP BY Format([Date Paid],"mmm"" '""yy"), [Tenant Payments].[Date Paid], Year([Date Paid])*12 + Month([Date Paid])-1 ORDER BY Year([Date Paid])*12 + Month([Date Paid])-1; -- Duane Hookom Microsoft Access MVP "Pascoe via AccessMonster.com" wrote: Hello! Title says it all - I want the x axis to show dates in DATE ORDER, not alphabetical order. I've read thru the forum, can't see how to fix it - your help will be appreciated. Kind Regards, Russell. p.s. Here is the graph Row Source, should there be an ORDER BY statement in here? SELECT (Format([Date Paid],"mmm"" '""yy")) AS Expr1, Sum([Tenant Payments]. Amount) AS SumOfAmount FROM [Tenant Payments] GROUP BY (Format([Date Paid], "mmm"" '""yy")), [Tenant Payments].[Date Paid], (Year([Date Paid])*12+Month( [Date Paid])-1) HAVING ((([Tenant Payments].[Date Paid])=#3/1/2009#)); -- Message posted via http://www.accessmonster.com . |
#3
|
|||
|
|||
Graph displaying dates alphabetically on x axis
Duane,
Thank you for your response. I tried your suggestion, and it certainly moves me in the right direction (ordered by calendar rather than alphabet), but it seems to have another effect too. The graph is supposed to be a sum of per month performance, over a 13 month period, however, now it is displaying a greater granularity, i.e. Mar 09, Mar 09, Mar 09, Apr 09, Apr 09, May 09, May 09, etc etc Looking at the datasheet behind the graph I can see that the first 26 entries are Mar '09, the next 23 are for Apr'09, whereas the way I read the statement, there should be only one entry for Mar'09, Apr' 09 etc as the statement says it should be a Sum of amount by month. This graph is now displaying 366 entries on the datasheet across the 13 months - I, of course, want only to see 13 entries displayed. Your insight will be much appreciated, because it baffles me! Kind Regards, Russell. Duane Hookom wrote: Try this SQL for the Row Source: SELECT Format([Date Paid],"mmm"" '""yy") AS Expr1, Sum([Tenant Payments].Amount) AS SumOfAmount FROM [Tenant Payments] Where [Date Paid] =#3/1/2009# GROUP BY Format([Date Paid],"mmm"" '""yy"), [Tenant Payments].[Date Paid], Year([Date Paid])*12 + Month([Date Paid])-1 ORDER BY Year([Date Paid])*12 + Month([Date Paid])-1; Hello! [quoted text clipped - 14 lines] "mmm"" '""yy")), [Tenant Payments].[Date Paid], (Year([Date Paid])*12+Month( [Date Paid])-1) HAVING ((([Tenant Payments].[Date Paid])=#3/1/2009#)); -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...ports/201004/1 |
#4
|
|||
|
|||
Graph displaying dates alphabetically on x axis
I started with what you had for the Row Source which included the [Tenant
Payments].[Date Paid] in the GROUP BY. Get rid of it. -- Duane Hookom MS Access MVP "Pascoe via AccessMonster.com" u43716@uwe wrote in message news:a700966fc3a55@uwe... Duane, Thank you for your response. I tried your suggestion, and it certainly moves me in the right direction (ordered by calendar rather than alphabet), but it seems to have another effect too. The graph is supposed to be a sum of per month performance, over a 13 month period, however, now it is displaying a greater granularity, i.e. Mar 09, Mar 09, Mar 09, Apr 09, Apr 09, May 09, May 09, etc etc Looking at the datasheet behind the graph I can see that the first 26 entries are Mar '09, the next 23 are for Apr'09, whereas the way I read the statement, there should be only one entry for Mar'09, Apr' 09 etc as the statement says it should be a Sum of amount by month. This graph is now displaying 366 entries on the datasheet across the 13 months - I, of course, want only to see 13 entries displayed. Your insight will be much appreciated, because it baffles me! Kind Regards, Russell. Duane Hookom wrote: Try this SQL for the Row Source: SELECT Format([Date Paid],"mmm"" '""yy") AS Expr1, Sum([Tenant Payments].Amount) AS SumOfAmount FROM [Tenant Payments] Where [Date Paid] =#3/1/2009# GROUP BY Format([Date Paid],"mmm"" '""yy"), [Tenant Payments].[Date Paid], Year([Date Paid])*12 + Month([Date Paid])-1 ORDER BY Year([Date Paid])*12 + Month([Date Paid])-1; Hello! [quoted text clipped - 14 lines] "mmm"" '""yy")), [Tenant Payments].[Date Paid], (Year([Date Paid])*12+Month( [Date Paid])-1) HAVING ((([Tenant Payments].[Date Paid])=#3/1/2009#)); -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...ports/201004/1 |
#5
|
|||
|
|||
Graph displaying dates alphabetically on x axis
Pure Genius, That does it!
Thanks Duane. Duane Hookom wrote: I started with what you had for the Row Source which included the [Tenant Payments].[Date Paid] in the GROUP BY. Get rid of it. Duane, [quoted text clipped - 39 lines] Paid])*12+Month( [Date Paid])-1) HAVING ((([Tenant Payments].[Date Paid])=#3/1/2009#)); -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...ports/201004/1 |
Thread Tools | |
Display Modes | |
|
|