Group By Day of Week? 2

[ottograham]

I have a table with a date field [Claimdate] and I would like to have a query group the claims by the day of the week on which they occurred?

Can it be done in a query? And can I get the columns to show:
Monday Tuesday Wednesday etc.

Thank you

 

[cmmrfrds]

Example from Northwinds database.

SELECT Format([OrderDate],"dddd&quot AS aDay, sum(freight) as TheFreight
FROM dbrders
group by Format([OrderDate],"dddd&quot
ORDER BY Format([OrderDate],"dddd"

 

[r937]

nice one, worth a star

my first thought was DATEPART and a big multi-nested IIF to translate into day names...


rudy

 

[r937]

by the way, the ORDER BY is syntactically okay, but what actual order will the results be presented in?

that's right -- Friday, Monday, Saturday, Sunday, Thursday, Tuesday, and Wednesday

 

[cmmrfrds]

Yeah, I noticed that the order by is a little tricky, I tried a switch statement in the order by but that conflicts with the aggregation. I tried a Transform with a datepart which gets the rows in order but not the columns.

TRANSFORM sum([freight])
SELECT datepart("w",OrderDate) AS DayNumber
FROM dbrders
GROUP BY datepart("w",OrderDate)
ORDER BY datepart("w",OrderDate)
PIVOT Format([OrderDate],"dddd"

 

[cmmrfrds]

This one should have the order correct, but is getting a little more complex.

SELECT min(A.aDay), min(A.TheFreight)
FROM dbo_orders AS B INNER JOIN [SELECT Format([OrderDate],"dddd&quot AS aDay, sum([freight]) AS TheFreight
FROM dbrders
GROUP BY Format([OrderDate],"dddd&quot]. AS A ON Format(A.aDay,"dddd&quot = Format(B.orderdate,"dddd&quot
GROUP BY datepart("w",b.orderdate)
ORDER BY datepart("w",b.orderdate);

 

[ottograham]

Thank you cmmrfrds! It works. The totals for the Incurred fields are coming in with 6 decimals. I've changed the properties for the field to 0 decimals, but am still getting the same result. Can I use format again like you did to round it to an integer?

I check the Help system before posting the question, but often I can't find anything. You could spend 30 minutes searching when a quick question to Tek-tips yields an answer almost immediately. I'm also amazed that more stars are not awarded to the people answering the questions, and that the question descriptions are so non-descriptive.

Thanks again.

Scott

 

[cmmrfrds]

Probably the simplest since you want to strip the decimals is to use a function that converts the results to an integer data type - probably long to get up to 2.1 billion.

Select Clng(sum(myfield)) as rndField from mytable

OR experiment with Format by putting some examples in a module and running it to see the results.

Dim varx As Double, vary As Long
varx = 333.55
vary = 333.55
' User-defined formats.
Debug.Print Format(varx, "##,##0.00&quot
Debug.Print Format(vary, "##,##0.00&quot
Debug.Print Format(5459.4, "##,##0.00&quot 'Returns "5,459.40".

 

[ottograham]

Thanks - I've added the CLng and it works. Are you saying that I can use format() and add commas, round decimals etc. I've looked up format in the Help files (both Access and VB and I don't find anything on the function)?

I appreciate your help.

 

[CCLINT]

Just use:

GROUP BY Weekday(TheDate)

If you want to also ordewr by the WeekDay, then add:

ORDER BY Weekday(TheDate,2)

The syntax for the WeekDay function has an optional argument for the first day of the week.
For instance, if Monday is the first day of the week and not the default Sunday, then use a 2 for the second argument. If it is Tuesday, then use a three, and so on. [/b][/i][/u][sub]*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!

 

[cmmrfrds]

You should be able to do the type of things you suggested. If you type in the word format in some vba code and right click then select definition, that gives a look at the layout of the function. Do a search on the word format without the parens in the help file. Format is under Strings in the VBA library. Hope that helps.