Computing % of a column

[Guest]

I have a table which contains a loan type field, a 2 character text field,
and the current loan balance. I ran a query on the data and it is summed by
the loan balance so I have a total of the loan balances by loan type. How do
I get it to compute pecentages of currbalance for each loan type? This is a
breeze in Excel, I figured has to be away to do it in access.

Type sumofcurrbalance %of total curr balance
CM 100,000 how do I compute this?
CR 100,000
CL 100,000
LE 100,000
Total 400,000

 

[Marshall Barton]

I have a table which contains a loan type field, a 2 character text field,
and the current loan balance. I ran a query on the data and it is summed by
the loan balance so I have a total of the loan balances by loan type. How do
I get it to compute pecentages of currbalance for each loan type? This is a
breeze in Excel, I figured has to be away to do it in access.

Type sumofcurrbalance %of total curr balance
CM 100,000 how do I compute this?
CR 100,000
CL 100,000
LE 100,000
Total 400,000

All that in a query??? Pleas post the query's SQL view.

Would make more sense if it were in a report.

 

[Andreas]

Copy your query and delete the column for the LoanType field.
Save the query. This query should now have a single row and a single
column in datasheet view, showing the total balance.

Create a new query, using your original query and the new query above as
the data source (do not attempt to create a link between the two).

You can now refer to the field in the new query and create your calculation.

Regards,
Andreas

 

[Guest]

Here is the SQL view: SELECT Sum(data.curbalance) AS SumOfcurbalance,
data.lntype
FROM data
GROUP BY data.lntype;

I only have the first two columns and no total. The right hand column was
to illustrate what I wanted to don. I know I can get the total via a report,
but how do I get the percentage of each type?

Thank you,
Kelvin

 

[Marshall Barton]

Try this:

SELECT Sum(data.curbalance) AS SumOfcurbalance,
data.lntype,
Sum(data.curbalance) / (SELECT Sum(X.curbalance)
FROM data As X) As Pct
FROM data
GROUP BY data.lntype

However, I still think you might be better off doing this in
a report using your original query. Then, add a text box
named txtGrandTotal to the report header section with the
expression:
=Sum(SumOfcurbalance)
and in the detail section the percentage would just be the
text box expression:
=SumOfcurbalance / txtGrandTotal