I really need your help to accomplish adding a separate new row to my table after running a PIVOT command (Crosstab Query).
My objective is to count the column totals of each of the [Request Types] and insert the sum of the column into a new row at the bottom of my table.
This seems like rocket science to me and is above and beyond my capabilities and knowledge of the SQL language/realm.
Here's a quick screenshot of the data in my table:
Here is a quick screenshot of the data captured from the table after running a CrossTab Query of TableA
TRANSFORM Count([Division]) AS CountOfDivision
SELECT [Branch], [Division]
GROUP BY [Branch], [Division]
PIVOT [Request Type]
As stated in a comment to the original post, I am limited to using SQL Server and not MS Access. However, the suggestion was to use a UNION statement in the query for MS Access - which this example still provides a valid way to answer the question.
To create a "Total" line below all of the individual records, an aggregate query needs to be written using the same SQL (with the addition of aggregate functions to the columns to be 'totaled).
I am using
CASE statements here to make the logic a little simpler to digest without the added complexity of a cross-tab and pivot.
SELECT A.Branch ,A.Division ,CASE WHEN A.RequestType = 'Report' THEN 1 ELSE 0 END AS Report ,CASE WHEN A.RequestType = 'Financial Analysis' THEN 1 ELSE 0 END AS FinancialAnalysis ,CASE WHEN A.RequestType = 'Letter' THEN 1 ELSE 0 END AS Letter ,CASE WHEN A.RequestType = 'Research Paper' THEN 1 ELSE 0 END AS ResearchPaper FROM TableA UNION SELECT 'TOTAL' ,NULL ,SUM(CASE WHEN A.RequestType = 'Report' THEN 1 ELSE 0 END) AS Report ,SUM(CASE WHEN A.RequestType = 'Financial Analysis' THEN 1 ELSE 0 END) AS FinancialAnalysis ,SUM(CASE WHEN A.RequestType = 'Letter' THEN 1 ELSE 0 END) AS Letter ,SUM(CASE WHEN A.RequestType = 'Research Paper' THEN 1 ELSE 0 END) AS ResearchPaper FROM TableA
The results of this query will look like this:
Branch Division Report FinancialAnalysis Letter ResearchPaper ASFA ASFAA 1 0 0 0 ASFA ASFAB 1 0 0 0 ASFA ASFAD 0 0 0 1 ASFB ASFBE 0 0 1 0 ASFC ASFCC 0 1 0 0 TOTAL NULL 2 1 1 1
This works by taking all of the tabulated columns form the first query and querying them a second time, but also adding them up. This produces only a single row - which is appended to the first query.
When using MS Access, the
CASE statement I've used above will not work unless you're coding this logic in VBA, and even then the syntax is different. The SQL code in MS access will need to use the
IIF functions instead