Calculations

[Steve]

I have a table that has $ totals in it by location. I am trying to divide
each separate total by the sum of the totals to get a percentage in a report
and I get the following statement.

“The expression On Open you entered as the event property setting produced
the following error: The object doesn’t contain the Automation object
‘Total’.â€

I am using this expression in my report to calc the percentage:
=Total!County_Total/Sum([Total]([County_Total]))*100

I had wanted to calc the percentage in my query where I get my totals but am
not sure how to do this.

The query reads in SQL: SELECT Fin_Asst.County, Sum(Fin_Asst.Granted) AS
County_Total INTO Total
FROM Fin_Asst
GROUP BY Fin_Asst.County;

Any ideas?

 

[Roger Carlson]

I think you are misusing the bang (!) here. Try this:
=County_Total/Sum(County_Total)*100

But this brings up another question. Why are you storing the County_Total
at all? Normally, you do not store calculated values in a table. Instead,
you recalculate them any time you need them (like in a query, form, or
report)

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Kai Apel \(Berlin\)]

I have try it in that way and it works!

Make a query, that only show the summ of the countries, such like that SQL:

SELECT YourTable.CountryName, Sum(YourTable.Sumfield) AS [Sum of Country]
FROM YourTable
GROUP BY YourTable.Country;

Then make a new Query, in wich you place the sum-querie and your
country/cost table. Be sure, that both Countryfields (this one from the
Sum-Querie and the One from your Country-Tabel) are related!!! Then you can
querie a new field (CountPercentage) with that function:

Percentage: [YourTable]![Sum]*100/[YourSumQuery]![FieldnameofyourSum] in the
Expressionfield or a SQL like that:
SQL:
SELECT [YourTable].[Sum], [YourSumQuery].[FieldnameofyourSum],
[YourTable]![Sum]*100/[YourSumQuery]![FieldnameofyourSum] AS CountPercentage
FROM YourSumQuery INNER JOIN YourTable ON
[YourSumQuery].[Country]=[YourTable].[Country];

hope, that can helps - here it works fine

Kai Apel (Berlin)

 

[Steve]

I was storing them because I am just learning access. I had tried to do it in
my report but was unable to make it work.

I would welcome any assistance, but am unsure how I could attach it here. I
could email a cut down version of my database to you if you like.

I think you are misusing the bang (!) here. Try this:
=County_Total/Sum(County_Total)*100

But this brings up another question. Why are you storing the County_Total
at all? Normally, you do not store calculated values in a table. Instead,
you recalculate them any time you need them (like in a query, form, or
report)

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

I have a table that has $ totals in it by location. I am trying to divide
each separate total by the sum of the totals to get a percentage in a report
and I get the following statement.

"The expression On Open you entered as the event property setting produced
the following error: The object doesn't contain the Automation object
'Total'."

I am using this expression in my report to calc the percentage:
=Total!County_Total/Sum([Total]([County_Total]))*100

I had wanted to calc the percentage in my query where I get my totals but am
not sure how to do this.

The query reads in SQL: SELECT Fin_Asst.County, Sum(Fin_Asst.Granted) AS
County_Total INTO Total
FROM Fin_Asst
GROUP BY Fin_Asst.County;

Any ideas?

 

[Steve]

I tried your suggestion and still got the same error msg.

I think you are misusing the bang (!) here. Try this:
=County_Total/Sum(County_Total)*100

But this brings up another question. Why are you storing the County_Total
at all? Normally, you do not store calculated values in a table. Instead,
you recalculate them any time you need them (like in a query, form, or
report)

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

I have a table that has $ totals in it by location. I am trying to divide
each separate total by the sum of the totals to get a percentage in a report
and I get the following statement.

"The expression On Open you entered as the event property setting produced
the following error: The object doesn't contain the Automation object
'Total'."

I am using this expression in my report to calc the percentage:
=Total!County_Total/Sum([Total]([County_Total]))*100

I had wanted to calc the percentage in my query where I get my totals but am
not sure how to do this.

The query reads in SQL: SELECT Fin_Asst.County, Sum(Fin_Asst.Granted) AS
County_Total INTO Total
FROM Fin_Asst
GROUP BY Fin_Asst.County;

Any ideas?

 

[Steve]

Kia,

I tried your suggestion for the sum query. It worked to an extent. When I
run the sum query it asks me to enter a parameter value. How do I get around
this?

Below is the sum query:

SELECT Total.CountyName, SUM(County_Total) AS [Sum of County]
FROM Total
GROUP BY Total;