Grouping Week query

[dio.m]

Hello all,
Can anybody help me. I need to query a date to become weekly.
The criteria is like:
The week begin in Monday and end in Sunday and must continue until end of the month, to generate report like:

DayName | Date | Production

Monday
Tuesday
Wednesday
Thursday
Friday 1-Jul-11 677.5
Saturday 2-Jul-11 764.5
Sunday 3-Jul-11 909
1st week 2351
Monday 4-Jul-11 636
Tuesday 5-Jul-11 358
Wednesday 6-Jul-11 424.5
Thursday 7-Jul-11 757.5
Friday 8-Jul-11 905
Saturday 9-Jul-11 965
Sunday 10-Jul-11 716.5
2nd week 4762.5
Please give me details, I'm realy stuck on this.

PS: I attached the image, so it's clear to view
Thanks for advance,

Dio

 

[vbaInet]

The week begin in Monday and end in Sunday and must continue until end of the month, to generate report like:

You've used the right term, "report".

What you need is a report, not a query.

You need to:

1. Group by Month and Year, i.e. Format([DateField], "mmyyyy")
2. Group by Week Number, i.e. DatePart("ww", [DateField])
3. Group by Weekday, i.e. WeekDay([DateField]) or Format([DateField], "dddd", vbMonday)
4. Sum in the footer of Week Number from step 2.

Here's a link on grouping and sorting in reports:

http://office.microsoft.com/en-us/access-help/create-a-grouped-or-summary-report-HA010006894.aspx

 

[dio.m]

Thanks for the quick response ... it very useful for me, anyway the step you explained to me very good, but I need the week start in monday and end in sunday.
I have follow all your step but still don't know with where to put Format([DateField], "dddd", vbMonday).

With many thanks,

Dio

 

[vbaInet]

For steps 1 to 3, put them in new columns in your query and include all the fields you need in this query too.

Now you will be able to use them in the Group By in your report.

 

[dio.m]

Still the same ... the week end in saturday and begin in sunday.
I'm using your steps;
- Format([DateField], "mmyyyy")
- DatePart("ww", [DateField])
- WeekDay([DateField])
can you explained to me how to start the week on Monday and end in Sunday in more details.

Sorry if i have too many asking the same subject

Dio

 

[vbaInet]

Alright, in a query vbMonday doesn't work so put 2 instead of vbMonday.

 

[dio.m]

I have no idea how to set Monday to Sunday as the begining of the week and end of the week.
Here is my files..

 

[vbaInet]

3. Group by Weekday, i.e. WeekDay([DateField]) or Format([DateField], "dddd", vbMonday)http://office.microsoft.com/en-us/access-help/create-a-grouped-or-summary-report-HA010006894.aspx

vbMonday should be 2. So use Format([DateField], "dddd", 2)

 

[dio.m]

Nothing change ...

 

[dio.m]

The sum come up after Saturday, but i want to show the sum after every Sunday.
I Think I Group by Week Number, i.e. DatePart("ww", [DateField]) is wrong because it's always end in every Saturday. Should be end in Sunday.
Where is my mistake? Can you please help me?

Thanks,

Dio

 

[vbaInet]

There you go.

 

[dio.m]

Hey I got the answer how to change the Week Number ... DatePart("ww",[ProductionDate],2)
and change .... Weekday([productionDate],2) to make monday as the first day of the week ...
Thank you very much for your help ...

 

[vbaInet]

Good job!