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