I'd like to return results that show calculations based on the day of the week
for example
Date Total
7/9/2007 3
7/10/2007 4
7/11/2007 3
7/12/2007 6
7/13/2007 1
7/14/2007 1
7/15/2007 0
7/16/2007 2
7/17/2007 3
Id like to return a result of
Day: Total:
Monday 5
Tuesday 7
Wednesday 3
Thursday 6
Friday 1
Saturday 1
Sunday 0
Thanks for your help.
|
|
0
|
|
|
|
Reply
|
 Utf
|
7/17/2007 2:10:00 PM |
|
In query design view, enter an expression like this into a fresh column in
the Field row:
DOW: Weekday([MyDate])
Substitute your field name for MyDate.
Depress the Total button on the toolbar.
Group by the DOW field.
Sum (or count or whatever) your other field.
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Qaspec" <Qaspec@discussions.microsoft.com> wrote in message
news:9E5A3A2D-6AD9-453B-B6A1-1EA613B6245A@microsoft.com...
> I'd like to return results that show calculations based on the day of the
> week
> for example
>
> Date Total
> 7/9/2007 3
> 7/10/2007 4
> 7/11/2007 3
> 7/12/2007 6
> 7/13/2007 1
> 7/14/2007 1
> 7/15/2007 0
> 7/16/2007 2
> 7/17/2007 3
>
> Id like to return a result of
>
> Day: Total:
> Monday 5
> Tuesday 7
> Wednesday 3
> Thursday 6
> Friday 1
> Saturday 1
> Sunday 0
>
> Thanks for your help.
|
|
|
0
|
|
|
|
Reply
|
Allen
|
7/17/2007 2:25:06 PM
|
|
In article <9E5A3A2D-6AD9-453B-B6A1-1EA613B6245A@microsoft.com>,
Qaspec@discussions.microsoft.com says...
> I'd like to return results that show calculations based on the day of the week
> for example
>
> Date Total
> 7/9/2007 3
> 7/10/2007 4
> 7/11/2007 3
> 7/12/2007 6
> 7/13/2007 1
> 7/14/2007 1
> 7/15/2007 0
> 7/16/2007 2
> 7/17/2007 3
>
> Id like to return a result of
>
> Day: Total:
> Monday 5
> Tuesday 7
> Wednesday 3
> Thursday 6
> Friday 1
> Saturday 1
> Sunday 0
>
> Thanks for your help.
>
An example for Northwind sample database:
SELECT FORMAT(OrderDate,"ddd") AS [Week Day],
SUM([Daily Sales]) AS [Total Sales]
FROM (SELECT Orders.OrderDate,
SUM([Order Subtotals].Subtotal) AS [Daily Sales]
FROM Orders
INNER JOIN [Order Subtotals]
ON Orders.OrderID = [Order Subtotals].OrderID
WHERE Orders.Orderdate BETWEEN [Enter start date:]
AND [Enter end date:]
GROUP BY Orders.OrderDate) AS a
GROUP BY DATEPART("w",OrderDate),FORMAT(OrderDate,"ddd");
|
|
|
0
|
|
|
|
Reply
|
Michael
|
7/17/2007 2:58:25 PM
|
|
Instead fo saying Monday Through Sunday the DOW column states 1-7
Does 1 equal Sunday?
Our reporting week actually runs Monday-Sunday is there any way I can list
out Monday first?
"Allen Browne" wrote:
> In query design view, enter an expression like this into a fresh column in
> the Field row:
> DOW: Weekday([MyDate])
> Substitute your field name for MyDate.
>
> Depress the Total button on the toolbar.
> Group by the DOW field.
> Sum (or count or whatever) your other field.
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Qaspec" <Qaspec@discussions.microsoft.com> wrote in message
> news:9E5A3A2D-6AD9-453B-B6A1-1EA613B6245A@microsoft.com...
> > I'd like to return results that show calculations based on the day of the
> > week
> > for example
> >
> > Date Total
> > 7/9/2007 3
> > 7/10/2007 4
> > 7/11/2007 3
> > 7/12/2007 6
> > 7/13/2007 1
> > 7/14/2007 1
> > 7/15/2007 0
> > 7/16/2007 2
> > 7/17/2007 3
> >
> > Id like to return a result of
> >
> > Day: Total:
> > Monday 5
> > Tuesday 7
> > Wednesday 3
> > Thursday 6
> > Friday 1
> > Saturday 1
> > Sunday 0
> >
> > Thanks for your help.
>
>
|
|
|
0
|
|
|
|
Reply
|
Utf
|
7/17/2007 3:10:01 PM
|
|
Yes: 1 = Sunday.
To treat Monday as the first day of the week:
DOW: Weekday([MyDate], 2)
You generally want the 1-7 approach so the days sort correctly. Not much use
having output that sorts as:
Fri, Mon, Sat, Sun, Thu, Tue, Wed
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Qaspec" <Qaspec@discussions.microsoft.com> wrote in message
news:EA230557-AC73-476F-8F07-0C0A8CAF1D6F@microsoft.com...
> Instead fo saying Monday Through Sunday the DOW column states 1-7
> Does 1 equal Sunday?
>
> Our reporting week actually runs Monday-Sunday is there any way I can list
> out Monday first?
>
> "Allen Browne" wrote:
>
>> In query design view, enter an expression like this into a fresh column
>> in
>> the Field row:
>> DOW: Weekday([MyDate])
>> Substitute your field name for MyDate.
>>
>> Depress the Total button on the toolbar.
>> Group by the DOW field.
>> Sum (or count or whatever) your other field.
>>
>> "Qaspec" <Qaspec@discussions.microsoft.com> wrote in message
>> news:9E5A3A2D-6AD9-453B-B6A1-1EA613B6245A@microsoft.com...
>> > I'd like to return results that show calculations based on the day of
>> > the
>> > week
>> > for example
>> >
>> > Date Total
>> > 7/9/2007 3
>> > 7/10/2007 4
>> > 7/11/2007 3
>> > 7/12/2007 6
>> > 7/13/2007 1
>> > 7/14/2007 1
>> > 7/15/2007 0
>> > 7/16/2007 2
>> > 7/17/2007 3
>> >
>> > Id like to return a result of
>> >
>> > Day: Total:
>> > Monday 5
>> > Tuesday 7
>> > Wednesday 3
>> > Thursday 6
>> > Friday 1
>> > Saturday 1
>> > Sunday 0
|
|
|
0
|
|
|
|
Reply
|
Allen
|
7/17/2007 3:15:38 PM
|
|
|
4 Replies
746 Views
(page loaded in 5.774 seconds)
Similiar Articles: Filter for Day of the Week - microsoft.public.access.queries ...I'd like to return results that show calculations based on the day of the week for example Date Total 7/9/2007 3 7/10/2007 4 ... Task to begin on certain week day regardless of when predessor end ...How do a I set up a task to begin on a specific day of the week regardless of when its ... and a predecessor to Task B 4) Consider if you want to create a custom filter ... Pivot table define Monday as first day of week - microsoft.public ...Filter for Day of the Week - microsoft.public.access.queries ... To treat Monday as the first day of the week: DOW ... How do a I set ... ... Week topics - Freebase Day Of ... Query from day of week - microsoft.public.access.queries ...I need assistance in creating the formula to select one week ago Wednesday through Thursday of the current week. This query needs the ability to be run on any day of ... Display a filtered list in a "rolodex" format ...Filter data in a range or table - Excel - Office.com If you want to filter by days of the week, format the cells to show the day of the week. If you want to filter by the ... How do I create a day-of-the-week attendance record in Access 07 ...... generate the query based on the table you are trending, then gen the report and group by week day (what ever you have the field named) this will, if you don't filter ... Week, monthly, quarterly - microsoft.public.access.queries ...>>If it's weekly then every week it should be viewed as pending, >>simultaneously it should check Week column to know which day of the week >>it >>should be pending ... Tasks for a week - microsoft.public.projectHi, Use the filter Date Range. Hope this helps, -- Jan De Messemaeker ... Task to begin on certain week day regardless of when predessor end ... How do a I set ... Week start day - microsoft.public.windows.live.mail.desktop ...To treat Monday as the first day of the week: DOW: Weekday([MyDate], 2) You ... How to Change start of week to Monday - microsoft.public.access ... Filter for Day ... tasks due in the next week/tasks overdue - microsoft.public ...All tasks due within the next week. I know I need to do a filter and I recall somewhere seeing ... up to speed in just a few months, let alone a in a matter of days ... Excel :: Filter Days Of The WeekFilter Days Of The Week How could I filter days of the week. Although the date formatting contains the date, ie Thursday, 18 October 2007, when I try to use a ... Filter for Day of the Week - microsoft.public.access.queries ...I'd like to return results that show calculations based on the day of the week for example Date Total 7/9/2007 3 7/10/2007 4 ... Filter for Day of the Week DataBase - DataBase Discussion List ...I'd like to return results that show calculations based on the day of the week for example Date Total 7/9/2007 3 7/10/2007 4 7/11 Day Of Week Filter Day Of Week topics - FreebaseDay Of Week: A community-built table of topics, including Monday, Friday, and Thursday taken from Freebase, the world's database. #7672 (Add filter for 'Day of week' on Date / DateTime fields ...It would be very useful to be able to do DB filtering based on the day of week (eg Mon, Tue, Wed). For example: Event.objects.filter(event_date__dow=3) 7/18/2012 4:13:46 PM
|
Search |
Post Question |
Groups |
Stream |
About |
Register
16 followers. 