Report Design

[Mark Richardson]

Hi,

It has been a while since I used Access and I've come to a point where I'm struggling. I am trying to create a report from a query that I have created which looks at the total hours individuals have done on a month by month basis, the hours have also been split into the different project types. Here is an example data extract:

Categroy EmployeeName Project Code Month Total Hours
Engineering Brian McCorry 4 09/2010 15
Engineering Brian McCorry 4 10/2010 55
Engineering Brian McCorry 4 11/2010 22
Engineering David Smith 4 09/2010 116
Engineering David Smith 4 10/2010 122.5
Engineering David Smith 4 11/2010 151.5
Engineering David Smith 4 12/2010 96.5
Engineering David Smith 5 12/2010 0.5
Engineering David Smith 7 09/2010 1
Engineering David Soane 4 09/2010 87
Engineering David Soane 4 10/2010 50.75
Engineering David Soane 4 11/2010 30

I am trying to produce a report that lists the hours each person has done within each month, broken down by project type and display it on one row. Unfortunately I'm struggling to get all the information on one line, it simply displays each individual entry on separate lines.

I'm not sure if it's something that I configure in the report or whether I need to change my query or even create multiple queires? Any help wouldbe gretly apprecited.

Regards
Mark

 

[Mr Smin]

If you make a section header in the report based on the staff name you can avoid repetitions of the name.
You can summarise by month etc. as well, but I can't recall how to do that.

 

[vbaInet]

Let's see a screenshot of your report and indicate on there what you mean by "it displays on separate lines".

 

[Mark Richardson]

I've tried to attacha a screenshot but not sue how to reference an image that is held on my machine thorugh URL address. To try and explain it a bit better, here is an example of what I hope it should look like:

11/2010 12/2010
4 5 7 4 5 7 Project Code
David Smith 116 0.0 0.0 96.5 0.5 1 Total Hours
Brian Mcorry 22 0.0 0.0 0.0 0.0 0.0 Total Hours

I'm trying to group the data into month sections, but then splittining the hours worked into each project group i.e 4, 5, 7. When I create the query I can only get it to show the hours that each person has spent on one project for one month, see below:

09/2010
4
Engineering
Brian McCorry 15
David Smith 116
David Soane 87

I then just get a long list with an entry for each month/project code combination.

Does this shed any light on it?

Regards
Mark

 

[vbaInet]

It can't be done in a report without a good amount of coding. Reports are made to display data in row-wise order, not column-wise order. You have a dynamic list of Project Codes so it just won't work.

What you need to do is look into using a crosstab query.