Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 01-11-2017, 04:53 PM   #1
wajans
Newly Registered User
 
Join Date: Jan 2017
Posts: 11
Thanks: 4
Thanked 0 Times in 0 Posts
wajans is on a distinguished road
How to make records of a table as column header and row header from

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
Attached Images
File Type: png sample.png (5.3 KB, 55 views)

wajans is offline   Reply With Quote
Old 01-11-2017, 05:27 PM   #2
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 10,464
Thanks: 40
Thanked 3,381 Times in 3,276 Posts
CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice
Re: How to make records of a table as column header and row header from

use first instead of sum for the value field (color)
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
The Following User Says Thank You to CJ_London For This Useful Post:
wajans (01-12-2017)
Old 01-12-2017, 12:24 AM   #3
wajans
Newly Registered User
 
Join Date: Jan 2017
Posts: 11
Thanks: 4
Thanked 0 Times in 0 Posts
wajans is on a distinguished road
Re: How to make records of a table as column header and row header from

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.

wajans is offline   Reply With Quote
Old 01-12-2017, 01:06 AM   #4
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 10,464
Thanks: 40
Thanked 3,381 Times in 3,276 Posts
CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice
Re: How to make records of a table as column header and row header from

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
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 01-12-2017, 01:35 AM   #5
wajans
Newly Registered User
 
Join Date: Jan 2017
Posts: 11
Thanks: 4
Thanked 0 Times in 0 Posts
wajans is on a distinguished road
Re: How to make records of a table as column header and row header from

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
Attached Images
File Type: png person.png (11.1 KB, 41 views)
File Type: png division.png (3.4 KB, 36 views)
File Type: png estimation.png (9.2 KB, 27 views)
File Type: png submission.png (10.0 KB, 41 views)
File Type: png unit.png (9.8 KB, 19 views)
wajans is offline   Reply With Quote
Old 01-12-2017, 01:38 AM   #6
wajans
Newly Registered User
 
Join Date: Jan 2017
Posts: 11
Thanks: 4
Thanked 0 Times in 0 Posts
wajans is on a distinguished road
Re: How to make records of a table as column header and row header from

forgot to add category table screenshot. Please find it here
Attached Images
File Type: png category.png (5.2 KB, 39 views)
wajans is offline   Reply With Quote
Old 01-12-2017, 01:54 AM   #7
wajans
Newly Registered User
 
Join Date: Jan 2017
Posts: 11
Thanks: 4
Thanked 0 Times in 0 Posts
wajans is on a distinguished road
Re: How to make records of a table as column header and row header from

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.
Attached Images
File Type: png query1.png (3.8 KB, 28 views)
File Type: png query2.png (7.7 KB, 42 views)
File Type: png query3.png (4.2 KB, 45 views)

wajans is offline   Reply With Quote
Old 01-12-2017, 02:06 AM   #8
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 10,464
Thanks: 40
Thanked 3,381 Times in 3,276 Posts
CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice
Re: How to make records of a table as column header and row header from

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
Code:
 
 (select ID from tblDivision where ID = query1.DivisionID) AS DivisionID
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

Code:
 
 (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)
alternatively, change query3 to a make table query then run your crosstab off that
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
The Following User Says Thank You to CJ_London For This Useful Post:
wajans (01-12-2017)
Old 01-12-2017, 10:30 PM   #9
wajans
Newly Registered User
 
Join Date: Jan 2017
Posts: 11
Thanks: 4
Thanked 0 Times in 0 Posts
wajans is on a distinguished road
Re: How to make records of a table as column header and row header from

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.

Code:
select sum(ocs.Count) as TotalCount, q.CategoryID, q.DivisionID from tblSubmissions ocs, tblPerson p, tblUnit u, query1 q  where 
ocs.CategoryID = q.CategoryID and 
p.ID = ocs.PersonID and
u.ID = p.UnitID and
u.DivisionID = q.DivisionID
order by q.CategoryID, q.DivisionID
Code:
select sum(ocs.Count) as TotalCount, query1.CategoryID, query1.DivisionID from tblOperationCategorySubmissions 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
order by query1.CategoryID, query1.DivisionID
Attached Images
File Type: png error.png (8.9 KB, 48 views)
wajans is offline   Reply With Quote
Old 01-13-2017, 01:17 AM   #10
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 10,464
Thanks: 40
Thanked 3,381 Times in 3,276 Posts
CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice
Re: How to make records of a table as column header and row header from

your original subquery is

Quote:
(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
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

Code:
 
 select ocs.CategoryID, u.DivisionID, sum(ocs.Count) AS sumofCount
 from tblSubmissions  ocs, tblPerson p, tblUnit u 
 where p.ID = ocs.PersonID and u.ID = p.UnitID 
 GROUP BY ocs.CategoryID, u.DivisionID
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.
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
The Following User Says Thank You to CJ_London For This Useful Post:
wajans (01-14-2017)
Old 01-14-2017, 01:57 AM   #11
wajans
Newly Registered User
 
Join Date: Jan 2017
Posts: 11
Thanks: 4
Thanked 0 Times in 0 Posts
wajans is on a distinguished road
Re: How to make records of a table as column header and row header from

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
wajans is offline   Reply With Quote
Old 01-14-2017, 02:53 AM   #12
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 10,464
Thanks: 40
Thanked 3,381 Times in 3,276 Posts
CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice
Re: How to make records of a table as column header and row header from

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.
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 01-14-2017, 03:21 AM   #13
wajans
Newly Registered User
 
Join Date: Jan 2017
Posts: 11
Thanks: 4
Thanked 0 Times in 0 Posts
wajans is on a distinguished road
Re: How to make records of a table as column header and row header from

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.
Attached Images
File Type: png sample.png (9.6 KB, 35 views)
wajans is offline   Reply With Quote
Old 01-14-2017, 03:41 AM   #14
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 10,464
Thanks: 40
Thanked 3,381 Times in 3,276 Posts
CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice
Re: How to make records of a table as column header and row header from

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
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
The Following User Says Thank You to CJ_London For This Useful Post:
wajans (01-15-2017)
Old 01-15-2017, 07:16 AM   #15
wajans
Newly Registered User
 
Join Date: Jan 2017
Posts: 11
Thanks: 4
Thanked 0 Times in 0 Posts
wajans is on a distinguished road
Re: How to make records of a table as column header and row header from

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.

wajans is offline   Reply With Quote
Reply

Tags
column and row headers , cross tab query

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Date in report header not working in page header fat controller Reports 8 12-14-2014 11:56 AM
Qry header table records based on form table field without duplicates brharrii Queries 8 02-12-2013 09:01 AM
Prevent Page Header on Grouping Header minute37 Reports 1 05-12-2008 06:59 PM
Image across Report Header/Page Header Crash_Pie Reports 2 08-15-2007 12:09 AM
Report header Vs Page header... and footer! Help! pduran Reports 7 01-16-2007 12:37 AM




All times are GMT -8. The time now is 09:05 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World