Percentage of detail section of a report

[Veli Izzet]

Hi all,

I have a report where the detail section has the costs of ingredients of
items.

I multiply units and unit price in the query and pass it to the report
as "Totals"

I also want to print the percentage cost of each ingredient.

I tried a calculated field of =[Totals]/Sum(Totals) which gave a very
stupid percentage. (It may have calculated the sum over the whole report).

How do I go about this?

Thanks for answers.

 

[Guest]

TESTED --
Use two queries - first query used the same criteria as you are now but just
total for output. Use your regular query and add the first query unjoined.
Use the total field in your percent calculation.

First query - I have no criteria here.
SELECT Sum(Data.amt) AS SumOfamt
FROM Data;

SELECT Data.Status, Sum(Data.amt) AS [Status Total],
Sum([amt])/[query47].[SumOfamt]*100 AS [Percent], Query47.SumOfamt AS Total
FROM Data, Query47
GROUP BY Data.Status, Query47.SumOfamt;

 

[Veli Izzet]

Thanks Karl,

I am not sure I understand totally, but it is very early here now, and I
will try to do it.

What I do not understand is mainly: The first query is the query of the
report, how do I put the second query into the report?

TESTED --
Use two queries - first query used the same criteria as you are now but just
total for output. Use your regular query and add the first query unjoined.
Use the total field in your percent calculation.

First query - I have no criteria here.
SELECT Sum(Data.amt) AS SumOfamt
FROM Data;

SELECT Data.Status, Sum(Data.amt) AS [Status Total],
Sum([amt])/[query47].[SumOfamt]*100 AS [Percent], Query47.SumOfamt AS Total
FROM Data, Query47
GROUP BY Data.Status, Query47.SumOfamt;



:

Hi all,

I have a report where the detail section has the costs of ingredients of
items.

I multiply units and unit price in the query and pass it to the report
as "Totals"

I also want to print the percentage cost of each ingredient.

I tried a calculated field of =[Totals]/Sum(Totals) which gave a very
stupid percentage. (It may have calculated the sum over the whole report).

How do I go about this?

Thanks for answers.