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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeOwner, Developing Solutions LLCCommented:
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
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
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

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
Dale FyeOwner, Developing Solutions LLCCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jeffrey CoachmanMIS LiasonCommented:
Dale,
...I knew you were going t post a "more efficient" solution...
lol
;-)

Jeff
0
Dale FyeOwner, Developing Solutions LLCCommented:
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 FyeOwner, Developing Solutions LLCCommented:
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 FyeOwner, Developing Solutions LLCCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.