Matrix report

[ilanray]

Hello
I have a table with few employee, tasks and status_task. I would like to create a report that I will be able to see all employees in a columns and the tasks in a rows something like matrix form
Any Ideas?

Thanks

 

[arnelgp]

you first create a Query that will list all employee, regardless of whether he has assigned task or not.
join the task and the status to your query.

SELECT
    employees.employeeID,
    employees.employeeName,
    tasks.taskName,
    employeeTasks.taskStart,
    employeeTasks.taskEnd, IIf(IsDate([taskend]),"Done","On-going") AS Status
FROM (employees
       LEFT JOIN employeeTasks ON employees.employeeID = employeeTasks.employeeID)
           LEFT JOIN tasks ON employeeTasks.taskID = tasks.taskID
ORDER BY employees.employeeID, employeeTasks.taskStart;

then create a report from the query.
see sampleReport on the demo.

 

[ebs17]

Matrix, employees in columns:
A crosstab query is a quick way to create such a matrix. If you can use fixed column headings because the employees are always the same, then it is easy to implement for a report.

 

[ilanray]

this is the problem. I can't use fixed column heading.
Is there a way to create flexible heading coulmns?

 

[arnelgp]

use Unbound textbox and you use VBA to assign it's ControlSource.
also you use VBA to assign the "text" of the label for each columns.
there are already sample here:
Solved - DYNAMIC REPORT FROM CROSSTAB QUERY | Page 6 | Access World Forums (access-programmers.co.uk)

 

[ilanray]

i thought about it but lets say I have in one department with 5 employees and in another department 10 employees. if I understand correctly I have to inset 10 textboxes and assign the vba to them, so in one department the report will be in half page and for the seconf department it will be the full page?

 

[ebs17]

CrossTabReport.mdb (intermediate)

Please consider donating $1 per sample downloaded, find out why here. CrossTabReport.mdb The problem...

www.rogersaccesslibrary.com

 

[ilanray]

the header should be flexible . sometimes there are 8 employees and sometimes could be more than that
I would like to create very flexible report

 

[ebs17]

You got a suggestion with sample DB. Why aren't you interested in this?

 

[ilanray]

becasue i need to add maximum textboxes , lets say I have 40 employee and I have sometimes 5 tasks for 5 employee and sometimes I have 10 tasks for 10 people and the 10 in that case I need to add 10 textboxes . if the team will have 5 people the report will be very small.

 

[Minty]

You don't need to "Add" text boxes. You need to decide whether to display them or not.

It has already been demonstrated how to make available variable numbers of headings (Columns) and label them.
Simply decide on a maximum you can display on one page and fix it at that, then display or hide them as necessary.

If you need more than is sensible for an Access report, you'll need to use something like Excel and format it accordingly.

 

[ilanray]

I think I didn;t explain my self well. I will try to expalin again.
If one team has 3 people and the other team have 10 people, the report for the 3 teams will look smaller .
I have another idea. is there away to create a table with number of column as the employeesnumber? for example the A tream will have 3 column and b team will have 10 column

 

[arnelgp]

you can show us a mock up of the report you are trying to generate?

 

[ilanray]

it just a simple table

sometime it could be 4 employee and sometimes more

 

[arnelgp]

i think you need to put the Employee Name as Row and "Analysis", etc as colum headings.
this will make your report adapt to whatever number of groups you have.

 

[ilanray]

ok. if so, how can I add more rows for each employee ? do you have an examlple for it?

 

[ilanray]

ok I created the crosstab. at the title I have the taske and on the keft I have the employee name.As you can see, the status wait doesn't have number becuase no one is waiting. If I will add text box with bound I will get an error message avout the wait status
So I need to do it as unbound.
I do I create the unbound that will add row for every employee?
as for the bount, I just add one textbox and it add more textbox as much as there are employee

 

[Minty]

You will get as many rows as there are employees, automatically.
That's the point of a cross tab. Fix the headings and have a variable number of rows.

 

[arnelgp]

if you can upload your table it will be easy to show you an example based on your table(s).

 

[ilanray]

OK jow do I create it in vba? I would like ot create one text box for the employee and for every employee it will create another row