Sorting by Day

[kidrobot]

I want to sort my query by Month then by Day. I have month sorted 01, 02, 03, etc. Although my days are listed as Monday, Tuesday, Wed... So when I sort it'll sort in alpha order. Friday, Monday... This is my field

Day: Format([Date],'dddd')

What is the best way to get it to sort in the correct order? My idea was to make a table that orders them, but that seems like too much. Would it be possibly to make the days order by number then show by name? What do you guys recommend?

 

[namliam]

You want to use the function weekday to sort on
weekday(date(),2)

Good luck !

 

[redneckgeek]

Access, by default, sorts dates by month then by day.
You shouldn't need to seperate the 2 for sorting.
I think you're probably looking for something like:

SELECT FORMAT([DATE],"MMM") AS MNTH, FORMAT([DATE], "DDDD") AS DAY
FROM SOME_TABLE
ORDER BY [DATE]

 

[Brianwarnock]

Weekday will give you the day number.

Brian

Too late again!!

 

[Brianwarnock]

Having reread the thread I think you should sort on Format([datefield], "mm/dd")

Brian

 

[redneckgeek]

Having reread the thread, also, I think we need more info to know what the poster is actually trying to do.

 

[kidrobot]

Sorry for being unclear. I want my query to sort by Month then by Day. For example

Mon Day
08 Monday
08 Tuesday
08 Wednesday
08 Thurs
etc

BUT, my query is soring like this

Mon Day
08 Friday
08 Monday
08 Thursday
08 Tuesday
etc

(I excluded Sat and Sun if you're wondering)

 

[namliam]

That is what I am saying: USE WEEKDAY! Not much unclear about it... quite clear to me anyway...

Use a field with Weekday to order by... dont use it to display as you allready have "Friday" but you want to order by "5" for friday....

USE WEEKDAY !

 

[kidrobot]

I got an error on:
Expr1: weekday(StartDate(),2)

 

[redneckgeek]

I'm not as clear on this as namliam is, but this will do what you are trying to do...

SELECT Month([your_datefield]) AS MNTH, 
Format([your_datefield],"dddd") AS DY
FROM your_table
GROUP BY Month([your_datefield]), Format([your_datefield],"dddd"), 
    Month(your_datefield), Weekday(your_datefield)
ORDER BY Month(your_datefield), Weekday(your_datefield);

 

[namliam]

I got an error on:
Expr1: weekday(StartDate(),2)

Why gave the () there??? It is a regular field of the table right??

RedNeck's query is what I was/am pointing towards... Except the second line in the Group by is not needed:

SELECT Month([your_datefield]) AS MNTH, 
Format([your_datefield],"dddd") AS DY
FROM your_table
GROUP BY Month([your_datefield]), Format([your_datefield],"dddd")
ORDER BY Month(your_datefield), Weekday(your_datefield);

 

[redneckgeek]

I didn't think it was necessary, either. But when I tested it on my own data, it wouldn't run unless I included it in the group by.

Actually, the group by was just an assumption on my part. kidrobot may not want just distinct values.
I'm guessing he's also including another field like SUM(number_of_calls)

 

[kidrobot]

sorry im un smart

 

[namliam]

Hmz, Odd... you should be able to run weekday as an Expression and not need it in the Group by.... But... that is what I thought... RedNeck appears to be right tho, it is needed.

 

[Brianwarnock]

What has made this thread difficult is the phrase

I want my query to sort by Month then by Day

when in reality what is wanted is to sort by Month and Day of the week, presumably as Redneck says to group some count or sum.

Brian

 

[raskew]

Just pull another of your date field into the query(you don't have to display it), sort Ascending. Remove the sort on your formatted field.

Bob

 

[redneckgeek]

That's what I offered in my first "solution". However, that could leave him with 4 or 5 "Monday"s for each month, when he only wants 1 (I think)

 

[kidrobot]

That's what I offered in my first "solution". However, that could leave him with 4 or 5 "Monday"s for each month, when he only wants 1 (I think)

correct. i solved the problem with namliam's suggestion