Percentage of Total 1

[AlanJordan]

I have a report that uses groups to create subtotals of each group.

At the end of the report, I have a grand total.

I want to calculate the percentage of each subtotal of the grand total.

I tried the following code, which fails because Access will not allow me to set the focus and it will not allow the assignment with having the focus set.

Interestingly, the function worked once, and then did not work. Perhaps I was in break mode that first time.

Any insights or suggestions will be appreciated.

Function CalcGroupPercentage()
On Error GoTo err_CGP
    Dim dblNumerator As Double
    Dim dblDenominator As Double
    Report_rptTimeInvested.txtGroupSubtotal.SetFocus
    dblNumerator = CDbl(Me.txtGroupSubtotal.Text)
    Me.txtGrandTotal.SetFocus
    dblDenominator = CDbl(Me.txtGrandTotal.Text)
    
    If dblDenominator <> 0 Then
        CalcGroupPercentage = dblNumerator / dblDenominator
    Else
        CalcGroupPercentage = 0
    End If
Exit Function
err_CGP:
    ErrBox
End Function

 

[Remou]

I cannot quite see how the above works. The Text property is only available when the control has focus, however, it should not be necessary.

I would have thought that a summary subreport, based off a summary query would have been easier. The percentage could be calculated by:
[tt]=([SumOfField]/DSum("SumOfField","qrySummary")*100)[/tt]

 

[IGPCS]

just use this format function

format ([yoursumFieldname],"Percent")

I will try my best to help others so will others do!!!!!
IGPCS
Brooklyn, NY

 

[Remou]

IGPCS
Surely that does not show a number as a percentage ofanother number? AlanJordan said in his post "I want to calculate the percentage of each subtotal of the grand total."

 

[AlanJordan]

Dear Remou,

Your approach sounds right to me. However, I am having trouble implementing it.

Could you describe how I set up the summary subreport in more detail? For reference you could download this .mdb. You can see what I tried. (It's only 784KB.)

http://www.hrfreedom.net/TT/DBs/TrackIt1.mdb.

The report is rptTimeInvested or rptExpandedTimeInvested

 

[Remou]

The general idea is to recreate the report groups in a query, which can be used to create a summary subreport. In this case, I think:

[tt]SELECT DISTINCTROW Activities.ProjectName, Sum(Activities.HoursInvested) AS SumOfHoursInvested
FROM Activities
GROUP BY Activities.ProjectName;[/tt]

Should suit as a query.

 

[AlanJordan]

Thanks for your support. I understand how to make the query. In fact, I tried that, as you can see from the DB that I uploaded.

What I don't understand is how to use the SumOfHoursInvested for that particular group and divide it by the total SumOfHours Invested for the total and display that percentage. Do you see this as a separate query?

 

[Remou]

When you build the subreport, add a textbox to the detail with a control source:

[tt]=([SumOfHoursInvested]/DSum("SumOfHoursInvested","qrySumActivities")*100)[/tt]

Is this where the problem lies?

 

[AlanJordan]

I must be missing something. When I do this, the system asks me to input a parameter, SumOfHoursInvested for each group.

 

[Remou]

You can:
* Post the sql of the query you have based the subreport on. (The reason I supplied SQL for a query is that the sample database you posted did not seem to have a suitable query.)

* Simply cut and paste this:
[tt]SELECT DISTINCTROW Activities.ProjectName, Sum(Activities.HoursInvested) AS SumOfHoursInvested FROM Activities GROUP BY Activities.ProjectName;[/tt]

As one line into the Record Source for your subreport.

* Change the the Alias used in the textbox. You will notice AS SumOfHoursInvested (above) this gives an alias to the Sum, which is then used in the textbox to refer to the sum.

 

[AlanJordan]

Thank you for your continued interest.

This produces a report that has the same information that I get when I use a standard report with the GroupBy feature turned on. It does not compute the percentage of that group over the total of all groups.

 

[Remou]

My idea comes in two parts.
First, produce a query that summarizes each group, effectively giving group subtotals.
Second, create a report based on this query, adding a textbox with a control source set to:
[tt]=([SumOfHoursInvested]/DSum("SumOfHoursInvested","qrySumActivities")*100)[/tt]
Where SumOfHoursInvested is the alias of the field from the query that shows the group subtotals.
A textbox with such a control source in the detail section of such a report should show percentages for each group.

Where are we failing to communicate?

 

[AlanJordan]

I don't know. I think that I have done this. I uploaded the database again. My phone number is on the top of frmActivities.

The report where I think that I have followed your instructions is subReportTry2.

Thanks,
Alan

 

[Remou]

Add a textbox to subReportTry2 and set the control source to:

[tt]=([SumOfHoursInvested]/DSum("HoursInvested","Activities")*100)[/tt]

It is not usual to upload databases in Tek-Tips, everthing is done online for the sake of other readers.

 

[AlanJordan]

Your suggestion works fine. I put up the database into a link, so that others could see it too. I would like to upload the database. Can you point me towards the directions.

 

[Remou]

I said it is not usual to upload databases, that is link them, it is, in fact, some what frowned upon.

 

[AlanJordan]

I cannot find the directions for uploading a database. How do I do this?

 

[Remou]

You don't.

 

[AlanJordan]

It's funny. How I read UNusual for "usual" even though you underlined it.

My apologies. Do you feel I should remove the database from the link?

Thanks for all of your help.

Alan

 

[Remou]

I think it is OK, it happens, it is just not the [blue]usual[/blue] . I have believed for some time now that stresses (bold, underline) don't help much and am always glad of another example with which to annoy a colleague.