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

 

[Pat Hartman]

Access reports are limited to two pages in width. That comes to 22 inches. I'm not sure how many columns you can fit in that space but if you have more employees in ANY department, you cannot use an Access report. You would be able to export the data to Excel and with a little formatting, have a nice looking report there. But, there are still limitations. Access limits the number of columns in a table and therefore in a query to 255 so that would be your export to Excel limit also. If you have still more columns, you can still use Excel but it would be harder because you would need to open multiple recordsets and fill the workbook using code instead. It gets complicated but you can do it.

I'm attaching a sample database that contains a report with variable columns. Look at "Expense example" and the "Report from variable crosstab". to see how it works. In this case, it is Expense Types. The example shows only a single page width so it limits the number of columns to 10 but the concept is the same.

The database itself shows two examples of how to make a perfectly normalized table look like a spreadsheet for data entry. It is mostly done using queries. I wouldn't try to use the concept for more than about 20 columns. That is all I've ever used it for. You might see if you can do 31 days. Eventually Access will complain because the join in the query will become too complex. The forecast example uses 12 months but the expense example only selects 4 expense types. You should be able to discern the pattern from those two queries.

Bound Denormalized Form

I've left the old versions here but ALWAYS go to the last post to get the current version. This database contains two examples (using the same technique) of how to take data that is properly normalized and display it so that it looks like a spreadsheet (rows displayed as columns) but is...

www.access-programmers.co.uk

 

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

 

[Pat Hartman]

With a crosstab, both the rows and the columns are dictated by the contents of the recordset. You can control this to some extent by using two methods. To add additional rows, you can use a left join to a table that lists all the options. As to columns, you can hard code the Row Heading property. 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.

 

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