Hi Marc,
12:00 AM of Friday of this week
=DateAdd("d", 6 - WeekDay(Date()), Date())
or
= Date() +6 - WeekDay(Date())
so...given a date in a week *Sunday-Saturday*,
the above will find the Friday *in that week*
just fine.
so if your workweek was Sunday-Saturday,
the following would be what you want:
SELECT
Table8.EmpNo,
DatePart("ww",[WorkDay]) AS Week,
Sum(Table8.Hours) AS SumOfHours,
Max(DateAdd("d", 6 - WeekDay(WorkDay), WorkDay)) AS EndDay
FROM Table8
GROUP BY
Table8.EmpNo,
DatePart("ww",[WorkDay]);
But your week is from Saturday thru Friday.
Each group will contain any dates between
a Sat and the next Fri.
So if you ran the following query on a Saturday
-- so had no dates for Sun - Fri of that week --
(or had an emp who only worked on Sat),
the formula will find "last week's Friday"
for that group - not the Friday of the group.
SELECT
Table8.EmpNo,
DatePart("ww",[WorkDay],7) AS Week,
Sum(Table8.Hours) AS SumOfHours,
Max(DateAdd("d", 6 - WeekDay(WorkDay), WorkDay)) AS EndDay
FROM Table8
GROUP BY
Table8.EmpNo,
DatePart("ww",[WorkDay],7);
Like the DatePart function, the WeekDay function
allows you to define the firstdayofweek:
WeekDay(date, [firstdayofweek])
If add that parameter, would we always get Fridays?
DateAdd("d", 6 - WeekDay(WorkDay,7), WorkDay)
This has never come up for me personally,
so I was not sure if "formula" would work.
In my limited testing, it would work with one
more adjustment.
DateAdd("d", 7 - WeekDay(WorkDay,7), WorkDay)
here are my *limited* tests in Immediate window
WorkDay=#7/2/2005# <-- a Saturday
?DateAdd("d", 6 - WeekDay(WorkDay,7), WorkDay)
7/7/2005 <--WRONG
{All rest (with adjustment) are correct I believe}
?DateAdd("d", 7 - WeekDay(WorkDay,7), WorkDay)
7/8/2005
WorkDay=#7/3/2005#
?DateAdd("d", 7 - WeekDay(WorkDay,7), WorkDay)
7/8/2005
WorkDay=#7/4/2005#
?DateAdd("d", 7 - WeekDay(WorkDay,7), WorkDay)
7/8/2005
WorkDay=#7/5/2005#
?DateAdd("d", 7 - WeekDay(WorkDay,7), WorkDay)
7/8/2005
WorkDay=#7/6/2005#
?DateAdd("d", 7 - WeekDay(WorkDay,7), WorkDay)
7/8/2005
WorkDay=#7/7/2005#
?DateAdd("d", 7 - WeekDay(WorkDay,7), WorkDay)
7/8/2005
WorkDay=#7/8/2005#
?DateAdd("d", 7 - WeekDay(WorkDay,7), WorkDay)
7/8/2005
WorkDay=#7/9/2005#
?DateAdd("d", 7 - WeekDay(WorkDay,7), WorkDay)
7/15/2005
WorkDay=#7/30/2005#
?DateAdd("d", 7 - WeekDay(WorkDay,7), WorkDay)
8/5/2005
So...I believe the following will work:
SELECT
Table8.EmpNo,
DatePart("ww",[WorkDay],7) AS Week,
Sum(Table8.Hours) AS SumOfHours,
Max(DateAdd("d", 7 - WeekDay(WorkDay,7), WorkDay)) AS EndDay
FROM Table8
GROUP BY
Table8.EmpNo,
DatePart("ww",[WorkDay],7);
good luck,
gary
Whoo - hooo. Works like a charm.
How would I get an additional field that shows the date of the end of the
week? You got me to where I see:
Emp001 Week1 TotalHours
How can I get to:
Emp001 Week1 Totalhours 1/7/2005
Emp001 Week2 Totalhours 1/14/2005
Thanks so much for the help.
Marc
Chaim said:
Just one modification to Karl's post:
DatePart("ww", [Work Day], 7)
The makes Saturday the first day of the week.
--
Chaim
:
SELECT Table8.EmpNo, DatePart("ww",[WorkDay]) AS Week,
Sum(Table8.Hours) AS
SumOfHours
FROM Table8
GROUP BY Table8.EmpNo, DatePart("ww",[WorkDay]);
:
I have a table that collects employee time in various blocks
throughout the
day (an employee may have 4 time entries in a single day). I would
like to
generate a query that shows total time worked by week for 2005.
Each record in the table includes EmpNo, Date, Hours. I would like
to see
something like:
EmpNo Week Hours
Emp001 Week1 TotalHours
Emp002 Week1 TotalHours
Emp001 Week2 TotalHours
Emp002 Week2 TotalHours
etc, etc
Our week is from Saturday thru Friday.
Thanks in advance for any help.
Marc
