Query Summarizing Records by Week

[dls0406]

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:

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

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:

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#));

 

[xenou]

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).

WorkDate	WorkYear	WorkWeek
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]

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]

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]

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]

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]

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?

 

[dls0406]

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]

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]

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)