Week and days query

  • Thread starter Thread starter Peter
  • Start date Start date
P

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!
 
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!
Click to expand...

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?
 
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 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...
 
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.
Click to expand...

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.
 
Thanks John & John, you guys really helped me out on this one. Precisely what
i was looking for! Thanks!

John Spencer said:
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!
Click to expand...
Click to expand...
 
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!


John Spencer said:
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!
Click to expand...
Click to expand...
 
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!


John Spencer said:
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!
Click to expand...
Click to expand...
Click to expand...
 
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??
Click to expand...

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.
 
Back
Top