MS Access: Need to add calculated field to Crosstab Query

I'm trying to add a calculated field to my Crosstab Query. This field should sum certain columns that were created by using [Account Number] as a column header.  For example, there is currently Columns A, B, C, D, E in the crosstab query.  I only want to sum columns A, B, C.  The SQL view is below.  I hope this makes sense and thank you for your help in advance.  I've tried so many different things and nothing's working!


TRANSFORM Sum([Query 4].Quantity) AS [SumOfQuantity]
SELECT [Query 4].P, [Query 4].E, [Query 4].I, Sum([Query 4].Quantity) AS [SumOfQuantity1]
FROM [Query 4]
GROUP BY [Query 4].P, [Query 4].E, [Query 4].M
PIVOT [Query 4].[Account Number];
Wonderwall99Asked:
Who is Participating?
 
Dale FyeCommented:
Another method, using the table Jeff provided:

TRANSFORM Sum(YourTable.Quant) AS SumOfQuant
SELECT YourTable.City, Sum(IIf(InStr("ABC",[Category])>0,[Quant])) AS SUMABC
FROM YourTable
GROUP BY YourTable.City
PIVOT YourTable.Category;
0
 
Dale FyeCommented:
can you post some sample data in a table, and what you would expect the output to look like based on that data?
0
 
Wonderwall99Author Commented:
I attached a file with sample data, the highlighted field is the expected output.
New-Microsoft-Office-Excel-Worksheet.xls
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Jeffrey CoachmanMIS LiasonCommented:
Try something like this:
TRANSFORM Sum(YourTable.Quant) AS SumOfQuant
SELECT YourTable.City, DSum("quant","YourTable","Category='A'" & " AND " & "City=" & "'" & [City] & "'")+DSum("quant","YourTable","Category='B'" & " AND " & "City=" & "'" & [City] & "'")+DSum("quant","YourTable","Category='C'" & " AND " & "City=" & "'" & [City] & "'") AS SumABC
FROM YourTable
GROUP BY YourTable.City
PIVOT YourTable.Category;

Sample attached...
Database72.mdb
0
 
Jeffrey CoachmanMIS LiasonCommented:
produces something like this:
Sum just A,B and C
(in the future please attach your sample as a database to facilitate giving you a solution built around you design)
0
 
Jeffrey CoachmanMIS LiasonCommented:
Dale,
...I knew you were going t post a "more efficient" solution...
lol
;-)

Jeff
0
 
Dale FyeCommented:
Jeff,

Love those CrossTabs!  

I'm doing more and more with SQL Server now and the Pivot queries in SQL Server are not nearly as friendly.  For example, you must explicitly declare all of the columns in the PIVOT clause.  To do so, you must create a query to define all of the column headers, build them into a string, and then create a dynamic SQL statement that appends those column headings to the string.
0
 
Wonderwall99Author Commented:
Thanks a ton!  I worked hours on this and couldn't figure out!  You guys rock!
0
 
Dale FyeCommented:
Glad we could help.  I'm a bit surprised you didn't at least split the points.  Jeff gave you the first workable solution.  I just embellished it a bit.

Click the "Request Attention" hyperlink below your original post if you want to reallocate points.
0
 
Dale FyeCommented:
The thing you have to remember with CrossTabs is that you don't have to group by all of the fields that you are selecting as "row headers".  You can do counts and sums and other creative stuff.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.