The months of the year come up alphabetically in a query

F

FC

I have entries in a table and for every month there are about 30 entries and
in the Month/year column the entry is as follows "March'03". the data type
selected is text.
Now I designed a query to select data for the fiscal year from April'04 to
March'05, the values are averaged out for each Month so I do end up having
only 12 records in my query, but all the records and in alphabetical order
for the month, so if I want to plot a graph it is skewed.Below is the result
of my query:

Month/Year AvgWeeksS2M
Apr ' 04 1.43877551020408
Aug ' 04 1.62087912087912
Dec ' 04 2.28042328042328
Feb ' 05 1.17748917748918
Jan ' 05 1.47252747252747
July ' 04 1.57142857142857
June ' 04 1.61344537815126
Mar ' 05 2.71428571428571
May ' 04 1.06802721088435
Nov ' 04 2.28021978021978
Oct ' 04 2.59523809523809
Sept ' 04 2.06015037593985


Pls help!!!!!!!
Thanks
FC
 
R

Rick Brandt

FC said:
I have entries in a table and for every month there are about 30
entries and in the Month/year column the entry is as follows
"March'03". the data type selected is text.
Now I designed a query to select data for the fiscal year from
April'04 to March'05, the values are averaged out for each Month so I
do end up having only 12 records in my query, but all the records and
in alphabetical order for the month, so if I want to plot a graph it
is skewed.Below is the result of my query:

Month/Year AvgWeeksS2M
Apr ' 04 1.43877551020408
Aug ' 04 1.62087912087912
Dec ' 04 2.28042328042328
Feb ' 05 1.17748917748918
Jan ' 05 1.47252747252747
July ' 04 1.57142857142857
June ' 04 1.61344537815126
Mar ' 05 2.71428571428571
May ' 04 1.06802721088435
Nov ' 04 2.28021978021978
Oct ' 04 2.59523809523809
Sept ' 04 2.06015037593985


Pls help!!!!!!!
Thanks
FC
Click to expand...

Include an additional field in the query that formats the months as yyyy-mm
and sort on that.
 
F

FC

the problem i have is that i now have about 1500 records, so to create a new
column and do the entries all over would be cumbersome, any other suggestions
would be appreciated
 
R

Rick Brandt

FC said:
the problem i have is that i now have about 1500 records, so to
create a new column and do the entries all over would be cumbersome,
any other suggestions would be appreciated
Click to expand...

Ok, I didn't pick up the first time that these values are coming from a
table. I assumed you had used the Format function in a query to get the
mmm, yy results. If you are storing this in a text field then that is how
they will sort (as text).

The only thing I can think is to use a query and in the query use an
expression that will take your text entries and convert them to real dates
and then format that result for sorting.

(untested)
DateSort: Format(CDate(Left([YourField], 3) & "-01-20" & Right([YourField],
2)),"yyyy-mm")
 
F

FC

Hi Rick.
Thanks for the reply.
Could you tell me where in the design of the query should I put the
statement that you have given me?
I am sure that you can see I am a novice at this, so do excuse me.
Thanks
FC

Rick Brandt said:
FC said:
the problem i have is that i now have about 1500 records, so to
create a new column and do the entries all over would be cumbersome,
any other suggestions would be appreciated
Click to expand...

Ok, I didn't pick up the first time that these values are coming from a
table. I assumed you had used the Format function in a query to get the
mmm, yy results. If you are storing this in a text field then that is how
they will sort (as text).

The only thing I can think is to use a query and in the query use an
expression that will take your text entries and convert them to real dates
and then format that result for sorting.

(untested)
DateSort: Format(CDate(Left([YourField], 3) & "-01-20" & Right([YourField],
2)),"yyyy-mm")
Click to expand...
 
R

Rick Brandt

FC said:
Hi Rick.
Thanks for the reply.
Could you tell me where in the design of the query should I put the
statement that you have given me?
I am sure that you can see I am a novice at this, so do excuse me.
Thanks
Click to expand...

In a blank column in the query design grid (where a field name would
ordinarily be).
 
F

FC

Hi
that was good it worked for the query and came up as the months of a year
but now if I try to plot a graph in Report they come up alphabetically. how
do I solve this.
Below is the Corrected Query.

Month/Year Avg WeeksBooked2Studied Month/Year04-05
Apr ' 04 1.31632653061224 2004-04
May ' 04 2.55 2004-05
June ' 04 2.21848739495798 2004-06
July ' 04 2.33928571428571 2004-07
Aug ' 04 5.29100529100529 2004-08
Sept ' 04 3.89473684210526 2004-09
Oct ' 04 4.34126984126984 2004-10
Nov ' 04 1.57142857142857 2004-11
Dec ' 04 2.7037037037037 2004-12
Jan ' 05 1.26923076923077 2005-01
Feb ' 05 2.64285714285714 2005-02
Mar ' 05 1.46583850931677 2005-03



FC said:
Hi Rick.
Thanks for the reply.
Could you tell me where in the design of the query should I put the
statement that you have given me?
I am sure that you can see I am a novice at this, so do excuse me.
Thanks
FC

Rick Brandt said:
FC said:
the problem i have is that i now have about 1500 records, so to
create a new column and do the entries all over would be cumbersome,
any other suggestions would be appreciated
Click to expand...

Ok, I didn't pick up the first time that these values are coming from a
table. I assumed you had used the Format function in a query to get the
mmm, yy results. If you are storing this in a text field then that is how
they will sort (as text).

The only thing I can think is to use a query and in the query use an
expression that will take your text entries and convert them to real dates
and then format that result for sorting.

(untested)
DateSort: Format(CDate(Left([YourField], 3) & "-01-20" & Right([YourField],
2)),"yyyy-mm")
Click to expand...
Click to expand...
 
Top