Query Summarizing Records by Week

dls0406

New Member
Joined
Oct 7, 2019
Messages
14
I have a table TTimeRecords with records including DateofJob and HoursofWork.

DateofJob | HoursOfWork
1/1/19 | 6.0
1/2/19 | 8.0
1/2/19 | 6.0
1/3/19 | 7.0
1/3/19 | 8.0


I then wanted to create a query in which hours were summarized for 2019 on a weekly basis, with week beginning on Sunday. I have written the query like this in design view:
1578596750507.png


And it creates the datasheet view like this, which is correct:
1578596797297.png


I am running into a problem when I try to roll the query over to 2020. I copy the query with 2019 date range criteria and change it to >=#12/29/2019# And <=#12/28/2020#, then the ouput I get is:
1578596980578.png

The issue is that Week 1 of the year, 12/29/19 - 1/5/20, should total 38.25, instead of being split into two lines (week 1 = 22 and week 53 = 16.25). Additionally, the weeks 1 and 2 are now showing a Monday start.

What am I doing wrong? 12/29/19 is a Sunday with the 1st day of the next year, so I think the way I've written the formula should indicate that this is the 1st week of 2020. And it should be including hours from the rest of the week, 12/30 & onward. Also, why does week 2 start 1/6 (Monday) instead of 1/5/2020 (sunday) if I am using a "1" in the datepart function?

TIA for your help!


Edit to add: If it is helpful, here is SQL code
SELECT TEmployee.EmployeeName, DatePart("ww",[DateofJob],1,1) AS [Week of Year], DateSerial(Year([DateofJob]),1,(DatePart("ww",[DateofJob],1,1)-1)*7)-1 AS [WeekBeg (Sun)], Sum(TTimeRecords.HoursOfWork) AS SumOfHoursOfWork
FROM TEmployee INNER JOIN TTimeRecords ON TEmployee.EmployeeID = TTimeRecords.EmployeeName
GROUP BY TEmployee.EmployeeName, DatePart("ww",[DateofJob],1,1), DateSerial(Year([DateofJob]),1,(DatePart("ww",[DateofJob],1,1)-1)*7)-1
HAVING (((TEmployee.EmployeeName)=[enter name]) AND ((DateSerial(Year([DateofJob]),1,(DatePart("ww",[DateofJob],1,1)-1)*7)-1)>=#12/29/2019# And (DateSerial(Year([DateofJob]),1,(DatePart("ww",[DateofJob],1,1)-1)*7)-1)<=#12/28/2020#));
 

Attachments

Last edited:

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,558
Office Version
2013
Platform
Windows
Personally rather than complicated formulas (hard to understand and hard to maintain) I would have in the database a WorkDays table that can be used as a JOIN table to get work years and work weeks.

True you have to spend a little time setting this up but from then on it's a piece of cake (you can set it up for 20 years in advance if you want, or just add each new year as a database maintenance task when it is needed.

Only partial data is shown here (the start and end of this year).


WorkDateWorkYearWorkWeek
12/29/2019​
2020​
1​
12/30/2019​
2020​
1​
12/31/2019​
2020​
1​
1/1/2020​
2020​
1​
1/2/2020​
2020​
1​
1/3/2020​
2020​
1​
1/4/2020​
2020​
1​
1/5/2020​
2020​
2​
1/6/2020​
2020​
2​
12/23/2020​
2020​
52​
12/24/2020​
2020​
52​
12/25/2020​
2020​
52​
12/26/2020​
2020​
52​
12/27/2020​
2020​
53​
12/28/2020​
2020​
53​
12/29/2020​
2020​
53​
12/30/2020​
2020​
53​
12/31/2020​
2020​
53​
1/1/2021​
2020​
53​
1/2/2021​
2020​
53​
1/3/2021​
2021​
1​
1/4/2021​
2021​
1​
1/5/2021​
2021​
1​
1/6/2021​
2021​
1​
 

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,558
Office Version
2013
Platform
Windows
Note that an additional plus with the above is you can easily add more columns for other data associated with particular days of the year - if the day is a holiday, for instance. Then you also save yourself complicated formulas to work out holidays too...
 

dls0406

New Member
Joined
Oct 7, 2019
Messages
14
Hmm I I dont know which is more cumbersome for me - a complex formula or a large data table. I have no need to associating a date with holidays, etc. Do you know of a simpler formula that can summarize hours worked by week? I would thing that this should not be extremely complicated but its looking like it might be :(
 

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,558
Office Version
2013
Platform
Windows
A large table like this is quite easy. And using it would be also very easy. That's just my suggestion and what I actually would do (and have done) in a case like this. As I said piece of cake really. Not sure where you get the idea that it's cumbersome.

I haven't had time to work out your formula to figure out why it's wrong yet I'm afraid. If it's including part of the hours in one week and part in another than probably your formula is failing on the border. So maybe it's with the datepart("yy") in the formula - part of the week would be in one year and part in another.
 

petertenthije

Board Regular
Joined
Sep 25, 2012
Messages
148
You could change the formula to say that if the week = 53, then the week should be 1.
iif(DatePart("ww",[DateofJob],1,1)=53,1,DatePart("ww",[DateofJob],1,1))

keep in mind that this formula will also have to be used to update the year! If the week = 53, then [year]+1.
 

dls0406

New Member
Joined
Oct 7, 2019
Messages
14
Ok yes I think this is the right track, thank you for your help! This helped my get the 2020 query showing correctly.

I am having a little trouble understanding the 2nd piece (updating the year). Perhaps you could write that part out as part of the formula completely so I could see how it fits together?

See the snip Below.(I added the "OfYear" column Temporarily to help me visualize the problem.) Essentially I want "week 1" of 2020 to group with Week 53 of 2019 (as its only a partial week). Is there an easy way to tweak the formula to accomplish this?
1579030422935.png



1579030451133.png
 

Attachments

dls0406

New Member
Joined
Oct 7, 2019
Messages
14
I think for the 2019 Query I just need something like an IF - AND statement. If week = 1 AND year = 2020, then use week 53 of 2019. But I am having trouble writing this.
 

dls0406

New Member
Joined
Oct 7, 2019
Messages
14
Ok - Nevermind I think I got it! Once again - thanks for your help. It is just my familiarity with access formulas that was lacking. The following function, which I can roll forward each year, is what I used:
WeekofYear: IIf(Year([DateofJob])=2021,53,IIf(Year([DateofJob])=2019,1,DatePart("ww",[DateofJob],2,1)))

I also have a date range which I can rollforward each year for the query to encompass all business days.

Thank you!
 

petertenthije

Board Regular
Joined
Sep 25, 2012
Messages
148
Ok - Nevermind I think I got it! Once again - thanks for your help. It is just my familiarity with access formulas that was lacking. The following function, which I can roll forward each year, is what I used:
WeekofYear: IIf(Year([DateofJob])=2021,53,IIf(Year([DateofJob])=2019,1,DatePart("ww",[DateofJob],2,1)))

I also have a date range which I can rollforward each year for the query to encompass all business days.

Thank you!
Your formula would have to be rewritten every year. You might want to use below formula.

year: DatePart("yyyy";[DateofJob];1;1)+IIf(DatePart("ww";[DateofJob];1;1)=53;1;0)
 

Forum statistics

Threads
1,082,727
Messages
5,367,203
Members
400,948
Latest member
Handypom

Some videos you may like

This Week's Hot Topics

Top