Week and days query

[Peter]

Hi all, a humble question...

I need to create a Query that presents the data per week and days in that
week...in other words:

Week No 1
Monday data
Tuesday data
Wednesday data
Thursday data
Friday data
Saturday data
Sunday data

Thanks for all help and learning that you guys provide!

 

[John W. Vinson]

Hi all, a humble question...

I need to create a Query that presents the data per week and days in that
week...in other words:

Week No 1
Monday data
Tuesday data
Wednesday data
Thursday data
Friday data
Saturday data
Sunday data

Thanks for all help and learning that you guys provide!

You'll need to give us a bit more help here, Peter. What's the table? How is
the "week" identified or determined? Which week: the current week, next week?
What's the data?

 

[John Spencer]

Well a query has all the same columns so you would have something like
the following three columns

Week Weekday Data

SELECT Datepart("ww",SomeDate) as WeekNo
, Format(SomeDate,"dddd") As WeekDay
, [Data]
FROM SomeTable
ORDER BY Datepart("ww",SomeDate)
, Weekday(SomeDate)

You can use the query to build a report.

In the query design view (query grid)
-- Add your table
-- Add your date field three times
-- Add your data field
-- Change the first date field to
WeekNo: Datepart("ww",SomeDate)
Set Sort to ascending
-- Change the second date field to
WeekDay: Format(SomeDate,"dddd")
-- Change the third date to
Weekday(SomeDate)
and change sort by to ascending and uncheck show field


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Peter]

Hi again..

Table A contains a date field. I do not have a week field. i want to sort
all records by week and days...in other words...all records created
May04/Mon-May10/Sun should be sorted under the week number 19.

or am i thinking wrongly here...

 

[John W. Vinson]

Hi again..

Table A contains a date field. I do not have a week field. i want to sort
all records by week and days...in other words...all records created
May04/Mon-May10/Sun should be sorted under the week number 19.

Try creating a query with a calculated field

WeekNo: DatePart("ww", [yourdatefield])

This will be a number from 1 to 54. There are some optional arguments to
DatePart, see the VBA help for the function for details, but it should let you
search, sort or group by the week.

 

[Peter]

Thanks John & John, you guys really helped me out on this one. Precisely what
i was looking for! Thanks!

Well a query has all the same columns so you would have something like
the following three columns

Week Weekday Data

SELECT Datepart("ww",SomeDate) as WeekNo
, Format(SomeDate,"dddd") As WeekDay
, [Data]
FROM SomeTable
ORDER BY Datepart("ww",SomeDate)
, Weekday(SomeDate)

You can use the query to build a report.

In the query design view (query grid)
-- Add your table
-- Add your date field three times
-- Add your data field
-- Change the first date field to
WeekNo: Datepart("ww",SomeDate)
Set Sort to ascending
-- Change the second date field to
WeekDay: Format(SomeDate,"dddd")
-- Change the third date to
Weekday(SomeDate)
and change sort by to ascending and uncheck show field


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Hi all, a humble question...

I need to create a Query that presents the data per week and days in that
week...in other words:

Week No 1
Monday data
Tuesday data
Wednesday data
Thursday data
Friday data
Saturday data
Sunday data

Thanks for all help and learning that you guys provide!

 

[Peter]

Hi again...one last thing

Weeknumber: DatePart("ww",[Report Date])..this is ok returns the week number
Month: DatePart("M",[Report Date])..this is ok retunrs the month number

but..Since Monday is the first day of the Week according to international
Date standards...What DatePart returns Monday = 1??

Thanks again!

Well a query has all the same columns so you would have something like
the following three columns

Week Weekday Data

SELECT Datepart("ww",SomeDate) as WeekNo
, Format(SomeDate,"dddd") As WeekDay
, [Data]
FROM SomeTable
ORDER BY Datepart("ww",SomeDate)
, Weekday(SomeDate)

You can use the query to build a report.

In the query design view (query grid)
-- Add your table
-- Add your date field three times
-- Add your data field
-- Change the first date field to
WeekNo: Datepart("ww",SomeDate)
Set Sort to ascending
-- Change the second date field to
WeekDay: Format(SomeDate,"dddd")
-- Change the third date to
Weekday(SomeDate)
and change sort by to ascending and uncheck show field


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Hi all, a humble question...

I need to create a Query that presents the data per week and days in that
week...in other words:

Week No 1
Monday data
Tuesday data
Wednesday data
Thursday data
Friday data
Saturday data
Sunday data

Thanks for all help and learning that you guys provide!

 

[John Spencer MVP]

You can use the Weekday function.
WeekDay(Date()) returns a number from 1 to 7

If you use the optional argument FirstDayOfWeek
Weekday(Date(),2) you will get 1 returned for Monday and 7 for Sunday

Or you can use
DatePart("w",Date(),2)
to get the same result.

By the way this is explained in the VBA Help.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Hi again...one last thing

Weeknumber: DatePart("ww",[Report Date])..this is ok returns the week number
Month: DatePart("M",[Report Date])..this is ok retunrs the month number

but..Since Monday is the first day of the Week according to international
Date standards...What DatePart returns Monday = 1??

Thanks again!

Well a query has all the same columns so you would have something like
the following three columns

Week Weekday Data

SELECT Datepart("ww",SomeDate) as WeekNo
, Format(SomeDate,"dddd") As WeekDay
, [Data]
FROM SomeTable
ORDER BY Datepart("ww",SomeDate)
, Weekday(SomeDate)

You can use the query to build a report.

In the query design view (query grid)
-- Add your table
-- Add your date field three times
-- Add your data field
-- Change the first date field to
WeekNo: Datepart("ww",SomeDate)
Set Sort to ascending
-- Change the second date field to
WeekDay: Format(SomeDate,"dddd")
-- Change the third date to
Weekday(SomeDate)
and change sort by to ascending and uncheck show field


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Hi all, a humble question...

I need to create a Query that presents the data per week and days in that
week...in other words:

Week No 1
Monday data
Tuesday data
Wednesday data
Thursday data
Friday data
Saturday data
Sunday data

Thanks for all help and learning that you guys provide!

 

[John W. Vinson]

Hi again...one last thing

Weeknumber: DatePart("ww",[Report Date])..this is ok returns the week number
Month: DatePart("M",[Report Date])..this is ok retunrs the month number

but..Since Monday is the first day of the Week according to international
Date standards...What DatePart returns Monday = 1??

From the VBA Help for Datepart:

Syntax

DatePart(interval, date[,firstdayofweek[, firstweekofyear]])

The DatePart function syntax has these named arguments:

Part Description

interval Required. String expression that is the interval of time you want to
return.

date Required. Variant (Date) value that you want to evaluate.

firstdayofweek Optional. A constant that specifies the first day of the week.
If not specified, Sunday is assumed.

firstweekofyear Optional. A constant that specifies the first week of the
year. If not specified, the first week is assumed to be the week in which
January 1 occurs.