I'm rusty on my Access...can anyone help with a Query question?

[Casper's Friend]

It's been a while since I've had to build anything in Access, so forgive me
if this is simple...I'm probably just having quite the mental block here.

I have a fairly simply query...3 fields which are just grouping and
counting....my fields are:
Day / #of Files / # of Days(this month) - which I would like to read like :
MON / 64 / 3
TUE / 64 / 2
WED / 38 / 2
THU / 49 / 2
FRI / 50 / 2
SAT / 25 / 2
SUN / 25 / 2

I would simply like to sort by the day of the week - Monday thru
Sunday....however my query is wanting to alphabetize the MON thru SUN
instead of putting it in daily order. Is there anyway to override this and
tell it that I want it to go MON, TUE,...etc? I also thought that I could
put another field on the query that would read the data (DAY) and covert it
into a value that I could sort by....ie... If DAY="MON" then 1, If DAY =
"TUE" then 2, etc... is this possible?

Sincerely,

A frustrated Casper's Friend :-P

[Peter Doering]

On Wed, 17 Dec 2003 05:31:36 GMT, Casper's Friend wrote:

I have a fairly simply query...3 fields which are just grouping and
counting....my fields are:
Day / #of Files / # of Days(this month) - which I would like to read like :
MON / 64 / 3
TUE / 64 / 2
WED / 38 / 2
THU / 49 / 2
FRI / 50 / 2
SAT / 25 / 2
SUN / 25 / 2

I would simply like to sort by the day of the week - Monday thru
Sunday....however my query is wanting to alphabetize the MON thru SUN
instead of putting it in daily order. Is there anyway to override this and
tell it that I want it to go MON, TUE,...etc? I also thought that I could
put another field on the query that would read the data (DAY) and covert it
into a value that I could sort by....ie... If DAY="MON" then 1, If DAY =
"TUE" then 2, etc... is this possible?

If DAY is based on a date field you could just duplicate the field, format
it to "w" and sort by this field:

No: Format(YourDate,"w")
DAY: Format(YourDate,"ddd")

(simplified, you certainly have to consider the parameters FirstDayOfWeek
and FirstWeekOfYear, dependent on your region.)

If it is a literal, you'll have to go the way you described, but Switch
would be the function:

No: Switch([Day]="Mon",1,[Day]="Tue",2,[Day]="Wed",3,[Day]="Thu",4,
[Day]="Fri",5,[Day]="Sat",6,[Day]="Sun",7)

Peter

--
No mails please.

[Peter]

You can get the value of a day using the weekday() function. Supply a
date and the day of the week that you wish to use as the firstday.

To sort the records in your query,
You could format the date field as Format(Date,"yyyymmdd") and sort
asc.

"Casper's Friend" <my**********@yahoo.com> wrote in message news:<YS********************@twister.austin.rr.com >...

It's been a while since I've had to build anything in Access, so forgive me
if this is simple...I'm probably just having quite the mental block here.

I have a fairly simply query...3 fields which are just grouping and
counting....my fields are:
Day / #of Files / # of Days(this month) - which I would like to read like :
MON / 64 / 3
TUE / 64 / 2
WED / 38 / 2
THU / 49 / 2
FRI / 50 / 2
SAT / 25 / 2
SUN / 25 / 2

I would simply like to sort by the day of the week - Monday thru
Sunday....however my query is wanting to alphabetize the MON thru SUN
instead of putting it in daily order. Is there anyway to override this and
tell it that I want it to go MON, TUE,...etc? I also thought that I could
put another field on the query that would read the data (DAY) and covert it
into a value that I could sort by....ie... If DAY="MON" then 1, If DAY =
"TUE" then 2, etc... is this possible?

Sincerely,

A frustrated Casper's Friend :-P

[Ed]

"Casper's Friend" <my**********@yahoo.com> wrote in message news:<YS********************@twister.austin.rr.com >...

It's been a while since I've had to build anything in Access, so forgive me
if this is simple...I'm probably just having quite the mental block here.

I have a fairly simply query...3 fields which are just grouping and
counting....my fields are:
Day / #of Files / # of Days(this month) - which I would like to read like :
MON / 64 / 3
TUE / 64 / 2
WED / 38 / 2
THU / 49 / 2
FRI / 50 / 2
SAT / 25 / 2
SUN / 25 / 2

I would simply like to sort by the day of the week - Monday thru
Sunday....however my query is wanting to alphabetize the MON thru SUN
instead of putting it in daily order. Is there anyway to override this and
tell it that I want it to go MON, TUE,...etc? I also thought that I could
put another field on the query that would read the data (DAY) and covert it
into a value that I could sort by....ie... If DAY="MON" then 1, If DAY =
"TUE" then 2, etc... is this possible?

Sincerely,

A frustrated Casper's Friend :-P

Looks like your solution using a numeric value for Day should work.
ET