By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,509 Members | 1,686 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,509 IT Pros & Developers. It's quick & easy.

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

P: n/a
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
Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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.
Nov 12 '05 #2

P: n/a
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

Nov 12 '05 #3

P: n/a
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
Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.