group by week 5

[Apollo13a]

Hello, I'm trying to create a query that will group employee labor by week. The records are entered daily so when I run my query I can get the daily hours. I created two queries, one to get the hours and one to Sum the daily entries. But how do I get the report to show just the totals, it always gives me records sorted by day.
Thanks, Jim

 

[jhall156]

If you want to group by week then you first need to create an expression that will uniquely identify each week. There are a number of ways to do this. Try creating an expression that will give you a text result that is the 4 digit year followed by the left zero filled week number (2 characters long) i.e. 200301 through 200352

Once you have created this expression column in your query, group by it.

JHall

 

[GoDawgs]

JHall's way works perfectly...just thought I'd throw out another "idea" (not really much different actually, but I use it in a similar db). Create a field called "SundayOfWeek" in the query and group by that...something like this:

SundayOfWeek: [DateField]-(Weekday([DateField])-1)

Hope that helps...

Kevin

 

[Apollo13a]

Thank you both.
GoDawgs, You're code worked great. How does this code create the grouping. Does Access do something behind the scenes with this function. I understand the weekday function will give me an integer value for the date argument. How does the grouping take place?
Thanks, jim

 

[GoDawgs]

It's pretty simple, the weekday function gives you an integer based on what day of the week the date you specify is...so if it's a Sunday then it will return 1, Wednesday will return 4 and so on. If you subtract an integer from a date it's subtracting days...so if I know that the date is Wednesday then I know that I want to subtract 3 days to get back to the Sunday before...thus the "-1" after finding out the weekday. Hope that helps.

Kevin

 

[Apollo13a]

Thanks, I get it. When I run this code I get grouping from Sunday to Sunday. How can I get Sunday thru Sat. or one weeks record of Labor. I'm trying a couple of different things.
Thanks again, jim

 

[GoDawgs]

Not sure what you mean...the grouping I gave you will group every week into the Sunday of that week...meaning Sunday 8/24 through Saturday 8/30 will be grouped with 8/24...Sunday 8/31 will be grouped with 8/31.

 

[Apollo13a]

Yes, your code works right. I must have something else going on. I changed the -1 to -2 so that I can get a grouping for Mon thru Sunday which is our pay period. I get a grouping of labor hours for 8-18-03 thru 8-24-03 which what I want. But my last 6 reords are for 8-25-03 so I not sure whats going on. I ran this on my whole table which includes dates back to june 1. I'm only getting records for the last week. Any way to group or display them all by week?
thanks, jim

 

[GoDawgs]

Give us some example of the data/field names in your table...and what your getting and what you expect to get. This should work for every week since it's based on the date...I mean you can't get 8/18 if the date is 6/5 or something...maybe you're using the Date() function instead of the actual field in your table? Let me know.

Kevin

 

[Apollo13a]

My mistake. I set criteria parameters in a previous query that I based this new query upon. Hence the one grouping

 

[Apollo13a]

Thanks GoDawgs, it worked like a champ!

 

[wvmbark]

GoDawgs,

Utilizing the format of your "SundayOfWeek" expression, can you show me the expressions for "LastDayOfMonth" and "LastDayOfQuarter"? And if I wanted first days, would it be much different?

I've been grouping months using a similar approach - "Format([TheDate], "mmm yy&quot for grouping by month. It's a hassel due to the data type issues. Your "SundayOfWeek" expression is so much better! I have oodles of applications for it, especially in the "month" and "quarter" groupings. Actually my whole job as a airline reliability analyst revolves around weekly, monthly, quarterly, etc. reports. So this is a major breakthrough for me.

Thanks!!

 

[GoDawgs]

Hmmm...last day of month is one I've used before...it's kind of long, but here it is:

CDate(Format((Month([workofdate])+1) & "/1/" & Year([workofdate]),"mm/dd/yy&quot)-1

Workofdate is your date field...basically this is just creating a date that's the 1st of the month after your date field (that's the month()+1)...then it's subtracting a day. Hope that makes sense.

I haven't done a Quarter query, let me look into that and I'll get back to you.

Kevin

 

[GoDawgs]

Oops, made a mistake above...I did that off of the top of my head and made the same mistake I did the first time I created that...it has a problem in the month of December (12/20/03 will have a month end of 12/31/02). Here's the updated:

CDate(Format(IIf(Month([testdate])=12,1,Month([testdate])+1) & "/1/" & IIf(Month([testdate])=12,Year([testdate])+1,Year([testdate])),"mm/dd/yyyy&quot)-1

Basically this just has a clause added that says if it's December then put it ahead a year. I also got a Quarterly query...but it's very convoluted, I'm thinking there's got to be a better way (and maybe someone else will post it)...but for now, here's what I used:

CDate(Format(IIf(Month([testdate])>=10,1,Month([testdate])+(IIf(Month([testdate]) Mod 3=0,0,3-(Month([testdate])) Mod 3))+1) & "/1/" & IIf(Month([testdate])>=10,Year([testdate])+1,Year([testdate])),"mm/dd/yyyy&quot)-1

It basically does the same thing as the month end (creates a date that's the first of the month after the month needed, then subtracts a day to get the last day of the month needed). There's a little difference though, the Mod formula returns the remainder...so for Feb we want the month to be March...Month(Feb) returns 2, Month(Feb) mod 3 returns 2...so if we do Month(Feb)+ (3-Month(Feb) mod 3) you're doing 2 + (3-2), which gets 3. So there it is...hope that helps.

Kevin

 

[CosmoKramer]

Just another way to skin the EOM cat....

I use code like this that adds one month to the date field and subtracts the number of days of that result to determine the last day:

DateAdd("m",1,[DateField])-CInt(Format(DateAdd("m",1,[DateField]),"dd"))

Regarding the earlier posts about grouping by week, I would add an expression field to the query that will hold the week number, then refer to that field to group by week:

WeekNum: Format([DateField],"ww")

Hoc nomen meum verum non est.

 

[GoDawgs]

Ahh, I like that end of month...never thought to use that for some reason...and that's why I like this place.

 

[GoDawgs]

Using that for the End of Quarter part would get you this:

DateAdd("m",(IIf(Month([testdate]) Mod 3=0,0,3-(Month([testdate])) Mod 3))+1,[testdate])-CInt(Format(DateAdd("m",(IIf(Month([testdate]) Mod 3=0,0,3-(Month([testdate])) Mod 3))+1,[testdate]),"dd&quot)

I still can't find an easy way determine what month is the end of the quarter...that's what's keeping this formula so long. Hopefully someone else will post something...

 

[CosmoKramer]

Last day of quarter:

DateAdd("d",-1,DateAdd("m",1,DatePart("q",[DateField])*3 & "/1/" & Year([DateField])))

Hoc nomen meum verum non est.

 

[GoDawgs]

Oh man, now I feel really stupid...never even thought to look if quarter could be pulled from datepart. I've learned something...so that deserves a star.

Kevin

 

[CosmoKramer]

Kevin,

That's the beauty of this stuff; there's usually several ways to get to the same destination.....

Thanks for the star.

Hoc nomen meum verum non est.