Including/creating pivot table in report access 2k3

[galorin]

I am using the following as a pass-thru query to Mysql, in order to get total hours spent by each person, doing a specific task.

SELECT w.designer, sum(w.HoursWorked) as worked, t.work_type as `work type`
from hoursworked as w
left join work_types as t on w.work_id = t.type_id
where w.date >= '2008-06-17'
and w.date <= '2008-07-24'
group by t.work_type, w.designer

it comes out looking like

name | hours | activity
------------------------
me   | 3.2   | coding
me   | 12    | debugging
him  | 2.2   | coding
him  | 12    | partying
bob  | 2     | phone
bob  | 4     | partying

I want to show a nice formatted report, from inside Access 2k3, that is a grid, names along the top, activities along the side, and the time spent inside the grid, with totals either side. Totals included. It would look something like

          me  | him | bob | total
coding   |3.2 | 2.2 | 0   | 5.4
debugging|12  | 0   | 0   | 12
partying |0   | 12  | 4   | 16
phone    |0   | 0   | 2   | 2
total    |15.2|14.2 | 6   | 35.4

How can I get this kind of formatting into an Access report, so anyone can generate it and not need to go through a dozen steps to get there?

 

[dhookom]

This looks like the results of a basic crosstab query with the name as the column heading, activity as the row heading, and sum of hours as the value. Hopefully the [Name]s won't change much so you can enter them in the column headings property of the crosstab.

Duane
Hook'D on Access
MS Access MVP

 

[galorin]

I'll try that out, I've also found a nifty way for doing it all in MySQL that I'll try out and see if Access can cope with it better than CASE statements in SQL

 

[galorin]

Ok, I have progress, but am a bit stuck after a feature request. Want to know if I can do this.

I am using the following pass-through query to do the pivot-table for our designer's timesheet, on the server.

SELECT t.work_type as work
, SUM(IF(w.designer = "Joe Bloggs",w.HoursWorked,0)) AS `Joe Bloggs`
, SUM(IF(w.designer = "Mike Jones",w.HoursWorked,0)) AS `Mike Jones`
, SUM(IF(w.designer = "David John",w.HoursWorked,0)) AS `David John`
, SUM(IF(w.designer = "Robert Harold",w.HoursWorked,0)) AS `Robert Harold`
, SUM(IF(w.designer = "James Smith",w.HoursWorked,0)) AS `James Smith`
from hoursworked as w
left join work_types as t using(work_id)
where w.date >='2008-06-01'
and w.date <='2008-07-26'
group by t.work_type
order by t.work_id

It's a bit of SQL wizardry that has the potential for dynamic report creation. The date is dynamic by design. However, we are a small company with about 20 people that can use this as a timesheet, in addition to the 5 designers listed above. The feature request is to be able to generate a dynamic timesheet, showing any number of people over any given time. My app would have either a bunch of tickboxes, a multi-select listbox, or listview control to select what employees want to be seen on the timesheet. the , SUM(IF(w.designer = "Mike Jones",w.HoursWorked,0)) AS `Mike Jones` line can easily be dynamically generated by VBA on the fly.

This means that the [Name]s would be constantly changing. Is there a practical method for generating a report dynamically, based upon the title and number of columns in the report, or will I need to provide several classes of fixed timesheets?

 

[dhookom]

There is a crosstab download at

http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

which allows for any number of dynamic columns.

Duane
Hook'D on Access
MS Access MVP