Week breakdown to days

[Gary Nelson]

In Access 2000, I am being asked to take data that is currently available as
jobs done on a weekly basis and break it down by day.

For example:

Jobs completed week of 7/11/03

I have the follwing fields: Jobs Completed Units
Pages
200
5,000 2,280,000

I need them to be broken down as follows:

W/E 7/11/03 Jobs Completed Units
Pages
Monday 40 1000
456,000,000
Tuesday 40 1000
456,000,000
Wednesday 40 1000
456,000,000
Thursday 40 1000
456,000,000
Friday 40 1000
456,000,000

This is something new, and I am a novice with Access2000, so please walk me
through it, if you don't mind. I can be emailed direct at
(e-mail address removed)

Thanks.

 

[SA]

Your ability to "re-aggregate" the data all depends on how the date complete
data is stored in the database. If it is stored by the actual date, then
your problem is pretty simple, if it is stored by the last day of the week,
you'll have to change the data collected and stored in the db first.

But lets assume that the data is currently stored by the date completed, and
the current report's underlying query simply aggregates that (in Access
query parlance uses a GroupBy) on the what ever field calculates the last
day of the week.

To modify and move to the new aggregation all you should have to do is to
stick another copy of the date complete field in the report's underlying
query and set it to group by. Then the query would output the data for
each date, but also retain the field which calculates the last day of week.

Then in your report, add a Group Header and Group Footer (using the sorting
and grouping dialog) to group on the last day of the week field.

Your report would then output:

Group Header
For the Week of:
Detail
Values by day
Group Footer
Sum() the values for individual days

Hope that helps