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

 

[wvmbark]

I need all records grouped by week, IF the last day of the week (Saturday) falls within the current month, AND only include records for weeks that have ended (I.e., if today is Wednesday, then the last grouping would be for last week). Can someone help me with this IIf statement?

Thanks!!

 

[GoDawgs]

Make a SundayOfWeek field like above...and a SaturdayOfWeek field:

SaturdayOfWeek: [DateField]+(7-Weekday([DateField]))

Then for the criteria of that field put:

Month([sheetdate]+(7-Weekday([sheetdate])))=Month(Date()) And <Date()

Basically that's saying only include records where the month of the SaturdayOfWeek field is equal to the current month...and where the SaturdayOfWeek is not in the future (therefore the week is completed).

Hope that helps.

Kevin

 

[wvmbark]

Is &quot;[sheetdate]&quot; &quot;[DateField]&quot;?

 

[GoDawgs]

Yep, sorry...it's whatever the name of your date field is...

 

[wvmbark]

It works great except I also get records for the current month of previous years as well.

 

[GoDawgs]

Then you need to use the format function to get both the month and year instead of just the month:

Format([sheetdate]+(7-Weekday([sheetdate])),&quot;mmyyyy&quot=Format(Date(),&quot;mmyyyy&quot And <Date()

 

[wvmbark]

That appears to have solved it. Thanks so much!! I will post back if I run into any problems.

 

[amourdevin]

I tried the suggestion:

Create a field called "SundayOfWeek" in the query and group by that...something like this:

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

It seems to be work great for a week that starts on Sunday.


Instead I tried:

SaturdayOfWeek: [DateField]-(Weekday([DateField])-0)

hoping to get data grouped and summed by week starting on each Saturday. This got every Saturday displayed (instead of Sunday) but the actual data is grouped and summed starting every Sunday (day after the Saturday displayed).

Thanks in advance!

Clueless with MS Access

 

[TonyJollans]

Hi Apollo13a,

Your problem is caused by the fact that the default week starts on a Sunday; if you read back through the thread you will see how to group by Saturdays, but an explanation may help (along with my apologies for a fairly lengthy post).

As I said, the default week starts on a Sunday, so the Weekday Function returns the following numbers:

1 = Sunday
2 = Monday
:
:
7 = Saturday

.. and the expression Weekday([DateValue])-1 returns:

0 = Sunday
1 = Monday
:
:
6 = Saturday

Subtracting Weekday([DateValue])-1 from [DateValue], gives:

If [DateValue] is a Sunday, the Sunday date
If [DateValue] is a Monday, the date one day earlier, the Sunday date
:
:
If [DateValue] is a Saturday, the date six days earlier, the Sunday date

.. so Sunday through Saturday all return the same Sunday date and are thus grouped together.

Now, your change, subtracting Weekday([DateValue])-[highlight]0[/highlight] from [DateValue], gives:

If [DateValue] is a Sunday, the date one day earlier, the Saturday date
If [DateValue] is a Monday, the date two days earlier, the Saturday date
:
:
If [DateValue] is a Saturday, the date seven days earlier, the previous Saturday date

.. so Sunday through Saturday all return the same date again, only this time it's the Saturday date, and are thus grouped together just as before. The only difference is the (somewhat arbitrary) calculated date on which you are grouping.

What you want to do is use a different start of week, not just a different date based on it. You can do this by changing the calculation (as seen in earlier posts) or you can do it by overriding the start of week. It becomes a little clearer if we look at the original expression, and show the (default) week start in it:

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

.. is the same as ..

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

If we change the start of week to be Saturday, the values returned by the Weekday Function become:

1 = Saturday
2 = Sunday
:
:
7 = Friday

.. and the values returned by the expression Weekday - 1 become:

0 = Saturday
1 = Sunday
:
:
6 = Friday

Using this, the values returned by subtracting this from the DateValue, become:

If [DateValue] is a Sunday, the date one day earlier, the previous Saturday date
If [DateValue] is a Monday, the date two days earlier, the previous Saturday date
:
:
If [DateValue] is a Friday, the date six days earlier, the previous Saturday date
If [DateValue] is a Saturday, the date [highlight]no[/highlight] days earlier, the [highlight]current[/highlight] Saturday date

Now the grouping is based on a Saturday week start. The code for this is just like the full code for the Sunday version (above) with an explicit week start of Saturday:

[red]Saturday[/red]OfWeek: [DateField]-(Weekday([DateField][red],vbSaturday[/red])-1)


Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[SUnderwood]

Hay, excellent post thanks!!

Business and Data Analyst
Database & Web Applications
International Applications Development
VB,VBA,ASP,SQL,Java,Pascal,ADA,SSADM,UML
seanunderwood1@hotmail.com

http://www.emergencydatabasesupport.port5.com

 

[amourdevin]

The expression:

SaturdayOfWeek: [When]-(Weekday([When],[vbSaturday])-1)

did not work as literally written.


What did work is:

SaturdayOfWeek: [When]-(Weekday([When],7)-1)

 

[TonyJollans]

Hi amourdevin,

My apologies.

vbSaturday is a VB constant and can only be used in VBA code. You have substituted the correct numeric constant for use in a Query; well done!

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[wvmbark]

I need to group by week for the 4 weeks that end on the Saturday prior to today's date.

My report will show the last 4 weeks (Sunday - Saturday)data, grouped by week.

Can you help me with the expression for that??

Thanks!!!

 

[GoDawgs]

You can do it using the SundayOfWeek discussed above...you can get that Sunday for all of your data and compare it to today's "SundayOfWeek" (using Date()) minus 28 (4 weeks) or 35 (couldn't tell from your description which one you need). You'd also need to make sure to not use today's Sunday either (filter out this week).

Kevin

 

[wvmbark]

I got how to get my records grouped by week using the SundayOfWeek, but I'm not understanding the "filter out this week" part.

Mike

 

[TonyJollans]

Hi wvmbark,

I'm not quite sure what you don't understand - to filter out records just use an appropriate WHERE clause, either on the date itself or on the calculated SundayOfWeek, so that all that remains in your query is the records you want. Something like ..

[blue][tt] ... WHERE [YourDate] BETWEEN Now() AND Now()-28[/tt][/blue] (or 35, or whatever value suits)

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[wvmbark]

Tony,

Thanks for your help. Sorry I haven't been clear enough. I was hoping to substitute an expression representing "the date of the Saturday prior to today" in place of the "[Forms]![frmWkly]![PrvSatDate]" date, see below.

>[Forms]![frmWkly]![PrvSatDate]-28 And <=[Forms]![frmWkly]![PrvSatDate] (This is how I currently filter the data)

I would like to eliminate the need to key in the [PrvSatDate] each time my report is ran.

I apologize if you've answered my question and I just haven't grasped it yet. And, thanks for your help!!

Mike

 

[TonyJollans]

Hi wvmbark,

It depends what you want to happen on a Saturday. If you want to have the Saturday a week earlier then do ..

[blue]Date()-Weekday(Date())[/blue]

If, instead, you want to get the current Saturday, then use ..

[blue]Date()-Weekday(Date(), 7) + 1[/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[wvmbark]

Tony,

That's it! Actually, both give me the prior Saturday's date - 2/28/04. Does the difference have to do with today is Saturday or not?

Mike

 

[TonyJollans]

Hi Mike,

Yes - it's only when today is a Saturday that you'll see any difference.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.