Graph displaying dates alphabetically on x axis

  • Thread starter Pascoe via AccessMonster.com
  • Start date
P

Pascoe via AccessMonster.com

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#));
 
D

Duane Hookom

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;
 
P

Pascoe via AccessMonster.com

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 said:
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;
[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#));
 
D

Duane Hookom

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 said:
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 said:
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;
[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#));
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top