Matrix report

[Minty]

You need to upload some sample data - then someone will create a suitable crosstab for you.
Forget about VBA at this stage.

 

[ilanray]

attached example file . you will be able to see that there are more status than the query
I would like to create a report or a form that show the query

 

[Minty]

You create this query:

TRANSFORM Count(tasks.id) AS Countמתוךid
SELECT employee.EmpName
FROM (tasks INNER JOIN employee ON tasks.employee_id = employee.id) INNER JOIN status ON tasks.status_id = status.id
GROUP BY employee.EmpName
PIVOT status.StatusName In ("StatusName","analysis","development","qa","waiting","ready for production","done");

Then all your status values are included with as many employees as you have:

You can adjust the column order in the query.

 

[ilanray]

Yeaaa the query works great thank you very much. Now can you please tell me how do add more rows for each employee?(like bound but in vba)

 

[Minty]

You just add data. It will add the employees as the data arrives.
To get it to list all employees even if they have no data change the joins:

TRANSFORM Count(tasks.id) AS CountOfid
SELECT employee.EmpName
FROM (tasks RIGHT JOIN employee ON tasks.employee_id = employee.id) LEFT JOIN status ON tasks.status_id = status.id
GROUP BY employee.EmpName
PIVOT status.StatusName In ("StatusName","analysis","development","qa","waiting","ready for production","done");

e.g.
q1 q1

EmpName	StatusName	analysis	development	qa	waiting	ready for production	done
bill	​	​	1​	​	​	​	​
Burt	​	​	​	​	​	​	​
Fred	​	​	​	​	​	​	​
Harry	​	​	​	​	​	​	​
john	​	1​	​	1​	​	​	​
lisa	​	2​	1​	​	​	​	​

 

[ilanray]

I meant how do I create a report that contain all employee in vba

 

[Minty]

Use the saved crosstab as a source for your report.
No vba required.

This was created with the wizard and took about 10 seconds.
You seem obsessed with using VBA when, so far at least, none is really required.

 

[arnelgp]

see also the "crosstab" query and the sampleReport.

 

[ilanray]

Great thank you very much for your help

 

[ilanray]

Hi again
I have another question regarding the same soulution. can I add another parameters to each cell? for example for each task there is a priority and I would like to sum the priorities like I did at the tasks
so if a person has 6 tasks which 1 is low priority , 3 is mediom priority and 2 in high priority it will be in the same cell
the query I used is :

TRANSFORM Count(Tasks.TaskName) AS CountTaskName
SELECT [Employee].FirstName, [Employee].UserName
FROM (Tasks INNER JOIN Employee ON Tasks.Current_ID=[Employee].ID) INNER JOIN Status ON Tasks.Status_ID = Status.id
GROUP BY [Employee].FirstName, [Employee].UserName
PIVOT Status.id In (1,2,3,7,5,4,9,12,13,14,15,16);

 

[ilanray]

anyone?

 

[ebs17]

Your desired view is not derivable with your database. What do you expect?

 

[ilanray]

in my database I hace a table with tasks,employee and priority for every task. I would like to sum them for every employee, is it possible?

 

[ilanray]

Yes I meant that when trere are 6 tasks, one task is in low priority, 3 tasks is mediium priority and 2 tasks is in high priority
what do you mean create another crosstab? how can I join 2 crosstabs in a form?

 

[ilanray]

what do you mean by join them?