Report with dynamic content and multi-layered horizontal categories/groups

[jensen76]

Hi everybody,

I'm trying to make a report that sorts data through multiple layers of categories: Work type and Employee.
I want to show employee work hours spent per week in different categories of work. The report will be made on customer level, so the data represents work hours spent on one customer. The report will show a whole year, divided into 53 weeks.
This is the result I'm trying to achieve:

The extra twist is, that the data is dynamic.
Sometimes a work category will be present, sometimes not. A customer might only have had Bookkeeping done in the year and nothing else, so only the bookkeeping category should show.
Sometimes an employee will be present in the report, sometimes not. The employees work on different customers and categories over the course of the year.
The employees will only work sporadically on each customer, so a lot of the fields on a report will be blank.

I've worked on this for three days and I'm completely stuck. I've got some crosstab queries lined up and a whole chain of append and maketable queries, but I can't seem to get there. I think I'm missing the overall idea how to do this - I'm on the wrong track somehow.
Im not an Access or VBA export by any means, I'm not a programmer by education, but I've built simple to intermediate databases in Access most of my life and I have a more than basic understanding of programming, so I'm not scared of a complicated solution if that's what it takes.
I just can't grasp which direction to take to solve this problem - if it can be solved at all...

Hope somebody can point me in the right direction...!

 

[arnelgp]

can you try making a "fixed" report as what you have shown.
the weeks are also fixed.
then for the hours, are Unbound textbox and the ControlSource is:

=DSum("hrs","DutyRecordTable","Emp=1 And DatePart('ww', [DutyDate]) = 1)

all are fixed for each employee and for each weeks.

 

[CJ_London]

When you say report, do you mean a report? Or a data sheet?

and potentially how many columns do you expect for any given customer? Realistically with a column width of 1” you can only expect to get 20-22 on a report

 

[jensen76]

can you try making a "fixed" report as what you have shown.
the weeks are also fixed.
then for the hours, are Unbound textbox and the ControlSource is:

=DSum("hrs","DutyRecordTable","Emp=1 And DatePart('ww', [DutyDate]) = 1)

all are fixed for each employee and for each weeks.

Thanks for the suggestion!
Making the report fixed would mean that it would not shrink when there is an empty column, right?
That would probable be a problem.

 

[jensen76]

When you say report, do you mean a report? Or a data sheet?

and potentially how many columns do you expect for any given customer? Realistically with a column width of 1” you can only expect to get 20-22 on a report

I'm interested in anything printable
So I'm guessing some kind of report as it would have to fit on an A4 paper.

There's going to be 5 categories and 5 employees. Realistically, there would never be more than 5-10 columns on a report, though, assuming empty columns can be removed, as employees are not that widely distributed across work categories. It's mostly 1 guy doing all consulting fx. And most work is concentrated around 3 categories.

 

[ebs17]

One question would be what effort you want to put into what. The following approach provides about the structure you want to have.

SELECT
   tblWeek.Week,
   CT1.*,
   CT2.*,
   CT3.*
FROM
   (
      (tblWeek
         LEFT JOIN CT1
         ON tblWeek.Week = CT1.Week
      )
      LEFT JOIN CT2
      ON tblWeek.Week = CT2.Week
   )
   LEFT JOIN CT3
   ON tblWeek.Week = CT3.Week

Two problems remain here:
- The Week field is repeated per CT.
- In the field list of the SELECT statement, all fields must have different names, so there cannot be multiple "Week" or "Employee 1" as such.

There, especially with the field "Week", one would have to rework considerably => composing the SQL statement via VBA.

So much for a query. With a report there are additional tasks.

 

[arnelgp]

Making the report fixed would mean that it would not shrink when there is an empty column, right?

i made similar report (dynamic columns) on here:
Solved - DYNAMIC REPORT FROM CROSSTAB QUERY | Page 6 | Access World Forums (access-programmers.co.uk)