How to make records of a table as column header and row header from (1 Viewer)

Hello Everybody,

How to make a column as column header and another column as row header in access. I have attached sample.png. I wanted to take Divisions as Column header and Categories as row headers and color as the data. Below is the sample output that I need:
Catogery Division1 Division2 Division3
Category1 Red Yellow Green
Category2 Yellow Red Red
Category3 Green Red Yellow

use first instead of sum for the value field (color)

Thanks for your reply CJ_London. I used first in crosstab query and I am getting below alert box:
Multi-level Group By clause is not allowed in a subquery.

you didn't mention it was a subquery

Perhaps explain in full what you are trying to achieve, it may be you'll need to include it as a separate query and join to it

Its a very long story
Let me explain you the scenario. I have a table tblEstimations(estimation.png), admin will be adding target count for each category, each unit, and each division. Estimation table has divisionid(linked to division(division.png) table), categoryid(linked with category(category.png) table) and unitid(linked to unit(unit.png) table).
The users will be trying to achieve those targets and the users submissions will be stored in tblSubmissions(submissions.png). Submission table has categoryid to link with category(category.png) table and personid to link with person(person.png) table. The person table in turn have unitid to link unit table and unit table have divisionid linking division table.
Estimation table also have red, yellow and green counts. if the total submission for a category in a division is <= to red count than red color has to be displayed and if total submissions > red and less than yellow then yellow color has to be shown and if total submissions > yellow than green color has to be shown. I need final output to be as below and for that I wrote subqueries and calling a crosstab query on that subquery
Catogery | Division1 | Division2 | Division3
----------------------------------------------
Category1 | Red | Yellow | Green

Category2 | Yellow | Red | Red

Category3 | Green | Red | Yellow

forgot to add category table screenshot. Please find it here

Queries I wrote to get the final cross tab results are as below:
query1(output in query1.png attached)
SELECT DISTINCTROW sum(count) AS Target, sum(Red) AS RedCount, sum(Yellow) AS YellowCount, sum(Green) AS GreenCount, DivisionID, CategoryID
FROM tblEstimations
GROUP BY DivisionID, CategoryID;

query2(output in query2.png attached)
SELECT query1.Target, query1.RedCount, query1.YellowCount, query1.GreenCount, (select Name from tblCategory where ID = query1.CategoryID) AS Category, (select ID from tblCategory where ID = query1.CategoryID) AS CategoryID, (select Name from tblDivision where ID = query1.DivisionID) AS Division, (select ID from tblDivision where ID = query1.DivisionID) AS DivisionID, (select sum(ocs.Count) from tblSubmissions ocs, tblPerson p, tblUnit u where
ocs.CategoryID = query1.CategoryID and
p.ID = ocs.PersonID and
u.ID = p.UnitID and
u.DivisionID = query1.DivisionID) AS TotalCount
FROM query1;

query3(output in query3.png attached)
SELECT DivisionID, Division, CategoryID, Category, iif(TotalCount = 0 and RedCount = 0 and YellowCount = 0 and GreenCount = 0, 'Grey',
iif(TotalCount > 0 and TotalCount <= RedCount, 'Red',
iif(TotalCount > RedCount and TotalCount <= YellowCount, 'Yellow',
iif(TotalCount > YellowCount, 'Green')
)
)
) AS Color
FROM query2;

finalquery(error on output: multi-level group by clause is not allowed in a subquery )
PARAMETERS query1.CategoryID Long, query1.DivisionID Long;
TRANSFORM First(query3.[Color]) AS FirstOfColor
SELECT query3.[Category]
FROM query3
GROUP BY query3.[Category]
PIVOT query3.[Division];

Hope I explained it correctly. Sorry for asking for so much. I am trying hard to get the results, its very crucial for me. thanks in advance.

a lot of data to try and digest which I don't have time at the moment, I have a number of tasks I need to complete today.

Your problem appears to be in query2 where there are subqueries.

this doesn't make sense - you are looking up a value you already have

I would put this in a separate query and link to in query2. You'll need to change it to groupby ocs.categoryID and u.DivisionID to provide the links


alternatively, change query3 to a make table query then run your crosstab off that

Thanks for helping CJ_London. I am trying to use one of the below 2 queries.
If I use any of the below queries, its opening window to enter parameter value for q.CategoryID or query1.CategoryID as shown in the attachment.

your original subquery is

(select sum(ocs.Count)
from tblSubmissions ocs, tblPerson p, tblUnit u
where ocs.CategoryID = query1.CategoryID and
p.ID = ocs.PersonID and
u.ID = p.UnitID and
u.DivisionID = query1.DivisionID) AS TotalCount

Click to expand...

I said put this in a separate query and link to in query2. You'll need to change it to groupby ocs.categoryID and u.DivisionID to provide the links - so it will look like this

then join it to your main query on CategoryID and DivisionID

Also just noticed you are using count as a field name. Note this is a reserved word and using it as a field name will cause issues at some point.

That worked like a charm, Thanks a lot.
I am getting below data in a table as required, However I wanted to show red colored circle instead of "red" text on a form or report. Could you please help me in getting that. Also is it possible to show percentage of submission count with the color on a report.
Catogery | Division1 | Division2 | Division3
----------------------------------------------
Category1 | Red | Yellow | Green

Category2 | Yellow | Red | Red

Category3 | Green | Red | Yellow

using red colour circles is possible, but very complex. It requires an unknown number of image controls, knowledge of windows api's, calculating locations on the screen and how to create images with transparent backgrounds.

It would be easier to use conditional formatting to colour the background and font when the value is 'Red'

With regards percentage of commission count, probably. Depends on what percentage you ar talking about - a category over all categories or colour over category.

I need to display percentage of total submission count for each category for each division with respect to the total target count for each category for each division as shown in the attached screenshot. The attached screenshot contains the final output to be displayed with percentage and round color circles. Please let me know if its possible or could you please point me to any concept on it. Thanks a ton.

OK, that is a bit more doable in principle, but you will need to research. To do this you would have two crosstabs, one as you have for the color, and one for the percentages. Then join the two crosstabs together on category to form the recordsource to your form.

You will also need to create image files of the dots - one for each colour. A quick way of doing this is to create a button and format it to the size and colour you require, place it over the background colour your require and take a screenshot of it - use the snipping tool. Save as bmp, jpg, png or gif - you will need to experiment as to which one is right for this requirement

On your continuous form, create unbound image controls of the right size and place in the required position of each control.

then you will need to populate the controlsource of each image control with the path to the image - something like

"C:\images\" & me.div1colour & ".jpg"

where me.div1colour is the name of the field in your 'color' xtab

You may find it easier to rename your xtab columns for easier identification.

you do not need to have controls on the form for the color fields.

If the number of divisions changes, you will need you have code to handle that, or create a new form

Finally I am able to get the required results. Thanks a lot for helping and guiding me to correct direction. Instead of applying round circles I am just doing conditional formatting and changing the fields background color as red, yellow and green respectively in the grid based on the percentage.
I have to make another screen, In the above one I have done category and divisions. Another screen with category and all the units in a particular division has to be shown. How should I pass divisionid in each of the query to get final crosstab query output by unit and category? Please do let me know. Thanks again for your help.

How to execute queries dynamically so that the results of first query are the source for second query because in my case I have to pass divisionid dynamically to get submissions count and target count for categories and units in a particular division

think you need to show an example of what you mean

Query1
SELECT DISTINCTROW sum(oce.TargetCount) AS TargetCount, oce.DivisionID, oce.CategoryID
FROM tblEstimations AS oce
GROUP BY oce.DivisionID, oce.CategoryID;

Query2:
SELECT ocs.CategoryID, u.DivisionID, sum(ocs.TotalCount) AS TotalCount
FROM tblSubmissions AS ocs, tblPerson AS p, tblUnit AS u
WHERE p.ID = ocs.PersonID and u.ID = p.UnitID
GROUP BY ocs.CategoryID, u.DivisionID;

Query3:
SELECT q1.*, q2.TotalCount, c.Name AS Category, d.Name AS Division
FROM Query1 AS q1, Query2 AS q2, tblCategory AS c, tblDivision AS d
WHERE q1.CategoryID = q2.CategoryID and q1.DivisionID = q2.DivisionID and c.ID = q1.CategoryID and d.ID = q1.DivisionID;

Query4:
SELECT DivisionID, Division, CategoryID, Category, ROUND((TotalCount*100)/TargetCount) AS PercentageCount
FROM Query3;

CrossTabQuery(output in the attached sample.png):
TRANSFORM First(Query4.[PercentageCount]) AS SubmissionPercentage
SELECT Query4.[Category]
FROM Query4
GROUP BY Query4.[Category]
PIVOT Query4.[Division];

All the above five queries are written to get the final output(sample.png) for all the divisions which will be seen by the manager of all the divisions. Now there are division heads for each division. They want to see data for there own individual division. I want to show another screen for each individual division heads there division data. Instead of division as column header as in the attached screenshot, It will be units of a division as column headers. I would have to do something like below by updating above queries to pass parameter for @divisionid in the following queries to get output for a division. The problem is how should I pass @divisionID to get the output for a particular division. In the above queries I am just saving the queries then finally calling the crosstab query to get the output in a form or report but for individual division, how, where and when should I pass @divisionID. Sorry for the long explanation, Hope I explained it correctly. I critically needed it. Let me know if you did not understood. I will try to explain it again.
Query1
SELECT DISTINCTROW TargetCount, oce.DivisionID, oce.CategoryID, oce.UnitID
FROM tblEstimations AS oce where oce.DivisionID = @divisionID
GROUP BY oce.DivisionID, oce.CategoryID;

Query2:
SELECT ocs.CategoryID, u.DivisionID, u.ID, sum(ocs.TotalCount) AS TotalCount
FROM tblSubmissions AS ocs, tblPerson AS p, tblUnit AS u
WHERE p.ID = ocs.PersonID and u.ID = p.UnitID and u.DivisionID = @divisionID
GROUP BY ocs.CategoryID, u.DivisionID;

Query3:
SELECT q1.*, q2.TotalCount, c.Name AS Category, d.Name AS Division, u.Name as UNit
FROM Query1 AS q1, Query2 AS q2, tblUnit u, tblCategory AS c, tblDivision AS d
WHERE q1.CategoryID = q2.CategoryID and q1.DivisionID = q2.DivisionID and c.ID = q1.CategoryID and d.ID = q1.DivisionID and q1.UnitID = q2.UnitID;

Query4:
SELECT DivisionID, Division, CategoryID, Category, UnitID, Unit, ROUND((TotalCount*100)/TargetCount) AS PercentageCount
FROM Query3;

CrossTabQuery(output in the attached sample.png):
TRANSFORM First(Query4.[PercentageCount]) AS SubmissionPercentage
SELECT Query4.[Category]
FROM Query4
GROUP BY Query4.[Category]
PIVOT Query4.[Unit];

Thanks

best place would be the original queries - something like

Q1
...
FROM tblEstimations AS oce
WHERE oce.DivisionID=forms!myform!cboDivision
GROUP BY oce.DivisionID, oce.CategoryID;

Q2
...
...
WHERE p.ID = ocs.PersonID and u.ID = p.UnitID and u.DivisionID=forms!myform!cboDivision
GROUP BY ocs.CategoryID, u.DivisionID;