Matrix report (1 Viewer)

[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

 

[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.

 

[Pat Hartman]

So, for example if you are showing data by month but a recordset might not always contain data for every month, you can add the month names to the Row Heading - Jan, Feb, Mar, etc.

Sorry, typo. That should be Column Heading

 

[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?

 

[Pat Hartman]

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

You can't do that

I would like to sum the priorities

What does that mean? Adding 1 + 2 + 3 doesn't make sense. Are you saying you want to count the number of 1's, 2's, 3's? And are you talking by person?

You can create a separate crosstab to count the priorities. Then you can join the two crosstabs so you end up with one row per person.

 

[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?

 

[Pat Hartman]

Create a second crosstab that counts by priority. Create a query that joins the two crosstabs on employee. Bind the third query to the form/report

 

[ilanray]

what do you mean by join them?