Summarise by week

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a date field in the format 01-jan-04 and would like to report on data
to show by week, ie Week 1, Week 2 etc. I need to find out how to group my
data using a by week function. Thanks for any help you can give.

Pete
 
A very simple way to do this is to let a report do it for you.

1. Create a report based on this table (or query).

2. In report design view, open the Sorting And Grouping box (View menu), and
choose the Date field. In the lower pane of this dialog, choose:
Group Header No
Group Footer Yes
Group On Week

3. In the new group footer section of your report, add a text box with these
properties:
Name txtWeekNum
Control Source =DatePart("ww, [SaleDate])
Format General Number
Replace SaleDate with the name of your date field.

4. Add another text box to the same section. Properties:
Name txtSumOfAmount
Control Source =Sum([Amount])
Format Currency
Replace Amount with the name of the field you wish to sum.

5. Set the Visible property of the Detail section to No, so that is does not
print.

Each row of the report now shows you the week number, and the total for that
week. As a bonus, you can turn the detail section back on again if you need
to see how it got its totals.
 
Morning
Sorted out half of it with the below;
Expr1: Format([Start Date],"ww")

But how can I show the weeks that have no information or data, ie want weeks
1-52 on the report.

Thanks again for any help.
Cheers
 
Okay: this will need stacked queries and an outer join.

Step 1: Aggregating query.
Format() returns a string.
It would be better to use DatePart(), i.e.:
WeekNum: DatePart("ww", [Start Date])
Save this query for step 3 below.


Step 2: Table of week numbers.
The missing weeks have to come from somewhere.
Create a table with just one field:
Name CountID
Data Type Number (rather than AutoNumber)
Make this field the primary key, and save the table with a name such as
tblCount. Enter the values 1 to 53. (Yes, there can be a 53rd week.)

Step 3: Combine into another query.
Create a new query, using your original query as an input "table".
Add tblCount as well.
In the upper pane, join WeekNum to CountID.
Double-click this join line, and choose:
All records from tblCount, and any matches from ...

The query then shows all the weeks (1 - 53), and any matching data from the
other query.



--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Little pete said:
Morning
Sorted out half of it with the below;
Expr1: Format([Start Date],"ww")

But how can I show the weeks that have no information or data, ie want
weeks
1-52 on the report.

Thanks again for any help.
Cheers

Little pete said:
I have a date field in the format 01-jan-04 and would like to report on
data
to show by week, ie Week 1, Week 2 etc. I need to find out how to group
my
data using a by week function. Thanks for any help you can give.

Pete
Click to expand...
Click to expand...
 
Thanks Allen
Not sure if I have made it the prettiest but have got the final result
looking ok.
Cheers


Allen Browne said:
Okay: this will need stacked queries and an outer join.

Step 1: Aggregating query.
Format() returns a string.
It would be better to use DatePart(), i.e.:
WeekNum: DatePart("ww", [Start Date])
Save this query for step 3 below.


Step 2: Table of week numbers.
The missing weeks have to come from somewhere.
Create a table with just one field:
Name CountID
Data Type Number (rather than AutoNumber)
Make this field the primary key, and save the table with a name such as
tblCount. Enter the values 1 to 53. (Yes, there can be a 53rd week.)

Step 3: Combine into another query.
Create a new query, using your original query as an input "table".
Add tblCount as well.
In the upper pane, join WeekNum to CountID.
Double-click this join line, and choose:
All records from tblCount, and any matches from ...

The query then shows all the weeks (1 - 53), and any matching data from the
other query.



--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Little pete said:
Morning
Sorted out half of it with the below;
Expr1: Format([Start Date],"ww")

But how can I show the weeks that have no information or data, ie want
weeks
1-52 on the report.

Thanks again for any help.
Cheers

Little pete said:
I have a date field in the format 01-jan-04 and would like to report on
data
to show by week, ie Week 1, Week 2 etc. I need to find out how to group
my
data using a by week function. Thanks for any help you can give.

Pete
Click to expand...
Click to expand...
Click to expand...
 
Wanting to complete similar report but using days rather than weeks. I have
tried the following

DateNum: DatePart("Short Date",[Start Date]) but get "invalid procedure call"

Again your help is great

Cheers

Little pete said:
Thanks Allen
Not sure if I have made it the prettiest but have got the final result
looking ok.
Cheers


Allen Browne said:
Okay: this will need stacked queries and an outer join.

Step 1: Aggregating query.
Format() returns a string.
It would be better to use DatePart(), i.e.:
WeekNum: DatePart("ww", [Start Date])
Save this query for step 3 below.


Step 2: Table of week numbers.
The missing weeks have to come from somewhere.
Create a table with just one field:
Name CountID
Data Type Number (rather than AutoNumber)
Make this field the primary key, and save the table with a name such as
tblCount. Enter the values 1 to 53. (Yes, there can be a 53rd week.)

Step 3: Combine into another query.
Create a new query, using your original query as an input "table".
Add tblCount as well.
In the upper pane, join WeekNum to CountID.
Double-click this join line, and choose:
All records from tblCount, and any matches from ...

The query then shows all the weeks (1 - 53), and any matching data from the
other query.



--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Little pete said:
Morning
Sorted out half of it with the below;
Expr1: Format([Start Date],"ww")

But how can I show the weeks that have no information or data, ie want
weeks
1-52 on the report.

Thanks again for any help.
Cheers

:

I have a date field in the format 01-jan-04 and would like to report on
data
to show by week, ie Week 1, Week 2 etc. I need to find out how to group
my
data using a by week function. Thanks for any help you can give.

Pete
Click to expand...
Click to expand...
Click to expand...
 
Open the Immediate Window (by pressing Ctrl+G).

Enter:
? DatePart(
At this point you are shown that the first argument is called Interval.
If you are not sure what values are allowed for the Interval, put the cursor
back in the DatePart word, and press F1. You will find that "Short Date" is
not one of them.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Little pete said:
Wanting to complete similar report but using days rather than weeks. I
have
tried the following

DateNum: DatePart("Short Date",[Start Date]) but get "invalid procedure
call"

Again your help is great

Cheers

Little pete said:
Thanks Allen
Not sure if I have made it the prettiest but have got the final result
looking ok.
Cheers


Allen Browne said:
Okay: this will need stacked queries and an outer join.

Step 1: Aggregating query.
Format() returns a string.
It would be better to use DatePart(), i.e.:
WeekNum: DatePart("ww", [Start Date])
Save this query for step 3 below.


Step 2: Table of week numbers.
The missing weeks have to come from somewhere.
Create a table with just one field:
Name CountID
Data Type Number (rather than AutoNumber)
Make this field the primary key, and save the table with a name such as
tblCount. Enter the values 1 to 53. (Yes, there can be a 53rd week.)

Step 3: Combine into another query.
Create a new query, using your original query as an input "table".
Add tblCount as well.
In the upper pane, join WeekNum to CountID.
Double-click this join line, and choose:
All records from tblCount, and any matches from ...

The query then shows all the weeks (1 - 53), and any matching data from
the
other query.


Morning
Sorted out half of it with the below;
Expr1: Format([Start Date],"ww")

But how can I show the weeks that have no information or data, ie
want
weeks
1-52 on the report.

Thanks again for any help.
Cheers

:

I have a date field in the format 01-jan-04 and would like to report
on
data
to show by week, ie Week 1, Week 2 etc. I need to find out how to
group
my
data using a by week function. Thanks for any help you can give.

Pete
Click to expand...
Click to expand...
Click to expand...
 
Back
Top