Subtotals on unpredictable key within a report

[John Baker]

Hi:

I have a situation where I have a series of time records (one or more per day) for a
number of projects. Each record has an identification for the activity conducted during
the period recorded. For any project, the activity may be one of many things, and the
activities may vary by project number.

I need to run a report and end up with a list of all the times reported, and totals by the
project number and the activity, as well as totals by Reporting individual, project
number, activity. I am having a little trouble with the activity piece, since you cant
predict the categories that will come up. I have tried pivot tables, but find that I have
to merge the pivot table back into the report using a spreadsheet (Excel), and that gets a
bit hairy round the edges. Before I end this madness, there may be one other category that
surfaces, but that's for the client to determine. Right now I am trying to figure out the
architectures for a solution, then I will execute the solution!

Anyone got any suggestions?
Totals would be

Project
Activity

and

Employee

Project
Activity
Regards

John Baker

[Phil Stanton]

Had what I think was a similar problem with an unknown number of races in a
series of races and needed to produce a report with a column for the
position of the boat in each race.
The basis of the solution is a temporary crosstab query programatically.
This will include all your unknown nunber of categories.
Then, again programattically you can check what columns there are in your
temporary query and create a report with the position of each column
positioned to the right of the previous column.

It's a lot of coding, but it works OK

The code for creating the crosstab query is so specific that it is not worth
sending, but if you want, I will send you the code for creating the report,
which again is specific but could be doctored (butchered would be closer)

Phil
"John Baker" <Ba******@Verizon.net> wrote in message
news:ls********************************@4ax.com...

Hi:

I have a situation where I have a series of time records (one or more per day) for a number of projects. Each record has an identification for the activity conducted during the period recorded. For any project, the activity may be one of many things, and the activities may vary by project number.

I need to run a report and end up with a list of all the times reported, and totals by the project number and the activity, as well as totals by Reporting individual, project number, activity. I am having a little trouble with the activity piece, since you cant predict the categories that will come up. I have tried pivot tables, but find that I have to merge the pivot table back into the report using a spreadsheet (Excel), and that gets a bit hairy round the edges. Before I end this madness, there may be one other category that surfaces, but that's for the client to determine. Right now I am trying to figure out the architectures for a solution, then I will execute the solution!

Anyone got any suggestions?
Totals would be

Project
Activity

and

Employee

Project
Activity
Regards

John Baker

[John Baker]

Phil:

Thanks a lot.

I am a bit of a neophyte in Access and any help is much appreciated. If you could send the
code it would be great.It would give me some sense of direction as I try and tackle his
issue. Were you able to roll the whole thing back into an Access report, or did you end up
using Excel to produce the final document?

Regards
John Baker
"Phil Stanton" <ph**@stantonfamily.co.uk> wrote:

Had what I think was a similar problem with an unknown number of races in a
series of races and needed to produce a report with a column for the
position of the boat in each race.
The basis of the solution is a temporary crosstab query programatically.
This will include all your unknown nunber of categories.
Then, again programattically you can check what columns there are in your
temporary query and create a report with the position of each column
positioned to the right of the previous column.

It's a lot of coding, but it works OK

The code for creating the crosstab query is so specific that it is not worth
sending, but if you want, I will send you the code for creating the report,
which again is specific but could be doctored (butchered would be closer)

Phil
"John Baker" <Ba******@Verizon.net> wrote in message
news:ls********************************@4ax.com.. .

Hi:

I have a situation where I have a series of time records (one or more per

day) for a

number of projects. Each record has an identification for the activity

conducted during

the period recorded. For any project, the activity may be one of many

things, and the

activities may vary by project number.

I need to run a report and end up with a list of all the times reported,

and totals by the

project number and the activity, as well as totals by Reporting

individual, project

number, activity. I am having a little trouble with the activity piece,

since you cant

predict the categories that will come up. I have tried pivot tables, but

find that I have

to merge the pivot table back into the report using a spreadsheet (Excel),

and that gets a

bit hairy round the edges. Before I end this madness, there may be one

other category that

surfaces, but that's for the client to determine. Right now I am trying to

figure out the

architectures for a solution, then I will execute the solution!

Anyone got any suggestions?
Totals would be

Project
Activity

and

Employee

Project
Activity
Regards

John Baker

[aagarcia]

I just finished a payroll db w/ similar situation. Reports with the
job category and with employees hours depicted.

In Reports I had to "Group by" job code and then "group by"
employees(I used first name of employee). In the details the name and
hours worked by employees.

In the group number footer is where I put the first sum equation for
job number totals to appear. Then a second sum total in the first name
footer. In the end you get two totals. One total for the job number
each employee worked for the time period specified and another total
combining all the job numbers combined. I also created a report
summarizing all the job numbers with all the total for that time
period specified as well. Hope I helped.
John Baker <Ba******@Verizon.net> wrote in message news:<ls********************************@4ax.com>. ..

Hi:

I have a situation where I have a series of time records (one or more per day) for a
number of projects. Each record has an identification for the activity conducted during
the period recorded. For any project, the activity may be one of many things, and the
activities may vary by project number.

I need to run a report and end up with a list of all the times reported, and totals by the
project number and the activity, as well as totals by Reporting individual, project
number, activity. I am having a little trouble with the activity piece, since you cant
predict the categories that will come up. I have tried pivot tables, but find that I have
to merge the pivot table back into the report using a spreadsheet (Excel), and that gets a
bit hairy round the edges. Before I end this madness, there may be one other category that
surfaces, but that's for the client to determine. Right now I am trying to figure out the
architectures for a solution, then I will execute the solution!

Anyone got any suggestions?
Totals would be

Project
Activity

and

Employee

Project
Activity
Regards

John Baker