expand a crosstab report to add a percentage

I created a crosstab report that lists the Sales, Cost of Goods, and the Margin by Grouping and by Month that the Sale was made.  Everything works fine, but now the user would like me to calculate a Percentage which is Margin / Sales.  I tried to do this first by taking the Crosstab query and making a new query that will select the data from that query and calculate the percentage, but the monthly data is set and I don't want that, so I went back to the query that created the crosstab and tried to add it there.  This is the code that I wrote before I started to make changes:
 TRANSFORM Format(Round(Sum([WrkFRCGSUnion].[Amount]),0),'#,###') AS SumOfAmount
SELECT WrkFRCGSUnion.Grouping, WrkFRCGSUnion.[P/LCategory], Format(Round(Sum([WrkFRCGSUnion].[Amount]),0),'#,###') AS [Grand Total]
FROM Specifics, WrkFRCGSUnion INNER JOIN WrkFRCGSPerSales ON WrkFRCGSUnion.Grouping = WrkFRCGSPerSales.Grouping
WHERE (((Format([DATEBILLED],'yyyymmdd')) Between (Select BegDate from Specifics where Id = 1) And (Select EndDate from Specifics where ID =1)))
GROUP BY WrkFRCGSUnion.Grouping, WrkFRCGSUnion.[P/LCategory]
PIVOT Format([DATEBILLED],'yyyymmdd');
In design mode I added a new column called Percent, but it won't show anything  when I describe it as an expression.  This is what I wrote:
Percent: IIf([WrkFRCGSUnion].[P/LCategory]="3-Margin",Format([Grand Total]/[WrkFRCGSPerSales].[SumOfAmount],"#.##%"),"")
Can this be done?  
Any suggestions would be appreciated.
LenTompkinsAsked:
Who is Participating?
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.

GRayLCommented:
You have to remember that every row you put into the SELECT clause must be balanced In the GROUP BY clause if the Insert is not an aggregate function.  I don't see anything in your Percent: that is aggregate so that phrase must also be in the the GROUP BY.
0
LenTompkinsAuthor Commented:
I went back to the drawing board and added a colum that I called percent and added this code:
Percent: Format(Sum([WrkFRCGSUnion].[Amount],"#,###")  and that worked fine.  Then I wanted to divide that total by a total that I have in my linked table and I get this message:
Tried to execute a query that doesn't include the specified expression " Format(Sum([WrkFRCGSUnion].[Amount])/[WrkFRCGSPerSales].[Total],"#,###")" as part of an aggregate function.

Any thoughts as to how to do this?
0
GRayLCommented:
Going back to your question:

Percent: IIf([WrkFRCGSUnion].[P/LCategory]="3-Margin",Format([Grand Total]/[WrkFRCGSPerSales].[SumOfAmount],"#.##%"),"")

can this be done?  I say no.  Firstly, SumOfAmount is not a field in table WrkFRCGSPerSales, it is a calculated value deposited in each of the Pivot date columns.  It would probably vary from date to date.  You are trying to get two values per date, the sum of Amount on that date (which is what the crosstab is all about), and the ratio of the grand total / sum of Amount on that date - from the SELECT clause of the crosstab - which I would say you cannot do - from the same query.  If you knew all the dates that would be produced by the crosstab, you could write a second query that used the crosstab to calculate the percent.  BTW do you not have the ratio backward in the question.  I see you have it corrected in the post above.
0
The Ultimate Tool Kit for Technolgy Solution Provi

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 for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

LenTompkinsAuthor Commented:
GRayL  I realize that my first attempt couldn't be done, but in my last attempt I was recalculating the total Margin records and then dividing by the total Sales from a linked query .
Format(Sum([WrkFRCGSUnion].[Amount])/[WrkFRCGSPerSales].[Total],"#,###")"
When I just calculate the sum it creates the correct total and I want to add the division part.  That Cant be done?
If not,  what suggestions do you have to create a report like this:
Grouping      P/LCategory      Grand Total      20070902      20070903      20070904      20070905      20070906      
Account      1-Sales      34,740                              4,
Account      2-CGS      9,926                              149
Account      3-Margin      24,813                              4,377

except add a Percentage for the Grand total amount.      
I also increased the point value because I may have to go about creating this report in a completely different fashion and I would  like some ideas.  Thanks            
0
GRayLCommented:
Yes, but don't you see, in the crosstab, it is not named WrkFRCGSUnion!Amount (value 4) - it is - qxtName![20070902] (value 4) - which is why I said if you were going to query the crosstab query, you would need to know all the names of the pivot fields which are date values.  You have 5 dates in Sept 2007 listed.  
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
GRayLCommented:
Thanks, but why the B?  Didn't like the answer?
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.