Chart Report and Dates....

[antihippy]

Hi There,

I feel this must be a common problem yet I can't find much about it here or Google.

I would like to chart some information based on a date field. Basically I want a running total per month. It's a chart of 'assessments'. X is the month, Y is the value. I have extracted the month values using DatePart. I have two questions:

First some SQL:

SELECT DatePart("yyyy",[qrySCSCADReview]![txtDate]) AS [Year], DatePart("m",[qrySCSCADReview]![txtDate]) AS [Month], Count(DatePart("m",[qrySCSCADReview]![txtDate])) AS MonthCount
FROM qrySCSCADReview
GROUP BY DatePart("yyyy",[qrySCSCADReview]![txtDate]), DatePart("m",[qrySCSCADReview]![txtDate])
HAVING (((DatePart("yyyy",[qrySCSCADReview]![txtDate])) Is Not Null) AND ((DatePart("m",[qrySCSCADReview]![txtDate])) Is Not Null))
ORDER BY DatePart("yyyy",[qrySCSCADReview]![txtDate]), DatePart("m",[qrySCSCADReview]![txtDate]);

1. How do I re-associate the year with the month? At the moment I have a year and a month column, but I want to make sure that Jan 2004 is not confused with Jan 2005.

2. How do I display the month name rather than the month value? So instead of 1 I want 'January'.

Experience tells me that there will be a pretty simple solution to this - I just can't find it. Please help.

 

[GingerR]

1) Since you have a Year and Month in each row, I don't see how it will get "confused". Are you seeing evidence of that?

2) You can use Format([blah],"mmmm")

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[GingerR]

by the way, you might want to try something like this instead:

ReportDate: Format(Month(txtDate),"mmmm")&" "&Year(txtDate)

For charts, I have to make the x-axis dates be strings, otherwise MS Graph tries to format them on it's own:

Cstr(" " & Format(Month(txtDate),"mmmm")&" "&Year(txtDate))

so they'd be:

January 2005
February 2005

etc

Then for sorting, so if you have more than one year's worth of data, so it won't sort alphanumerically, I usually have a calculated field which groups the dates on this:

1/1/04
2/1/04
.
.
.
1/1/05
2/1/05

etc

SortDate: Cdate(Month(txtDate) & "/01/" & Year(txtDate))

sorted Ascending, but not checked so it doesn't show in the result set.

hope this helps.

g

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[antihippy]

Thanks GingerR,

Re:

1)I was trying to describe what happens when I look at the graphical output. The problem is that it is numbering according to the month and as I have more than one years data this is a problem.

2) I tried format(blah,"mmmm") but it seemed to collapse all the months together. Which was odd.

So instead of:

2004: 11: x
2004: 12: x
2005: 1: x
2005: 2: x
2005: 3: x
etc ...
I got:

2004: January: x
2005: December: x
2005: January: x
And that was all, It seems to be keying off the first digit.

Month: Format(DatePart("m",[qrySCSCADReview]![txtDate]),"mmmm")

A confession: I am pretty new to using Access - as a developer. I used to work mostly with Excel and I note that Access is similar in most ways but different in others.

I'm giving your other suggestion a try and I'll report back once I've tried it out.

 

[antihippy]

PS:

I inserted your Reportdate suggestion and got the following output:

2004: 11: January 2004: x
2004: 12: December 2004: x
2005: 1: January 2005: x
2005: 2: January 2005: x
2005: 3: Janury 2005: x

Am I missing something?

 

[antihippy]

Ah! I think I have it - and I think I've been a but thick about it all. Here's the SQL:

SELECT Format([qrySCSCADReview]![txtDate],"mmmm") & " " & Year([qrySCSCADReview]![txtDate]) AS [Report Date], Count(DatePart("m",[qrySCSCADReview]![txtDate])) AS MonthCount
FROM qrySCSCADReview
GROUP BY Format([qrySCSCADReview]![txtDate],"mmmm") & " " & Year([qrySCSCADReview]![txtDate]), DatePart("yyyy",[qrySCSCADReview]![txtDate])
HAVING (((DatePart("yyyy",[qrySCSCADReview]![txtDate])) Is Not Null))
ORDER BY DatePart("yyyy",[qrySCSCADReview]![txtDate]);

When I plop that into a chart it works fine. Thanks for all your help GingerR, it's helped me get a better grasp of date manipulation in Access.

 

[GingerR]

Glad it worked out for you! Good for you for plugging away at it!

The second suggestions I had, were just in case you ever run into that kind of situation. I've made tons of charts, and getting the date reported like I want it is irritating.

Anyhow, good for you!

g

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html