sum then divide columns

  • Thread starter Thread starter G.
  • Start date Start date
G

G.

I saw a past post relating to this but I couldn't apply the information to my
problem.

I need to sum 2 columns then divide the sum's to get a percentage. I also
need the output on the report to show as a percentage. The previous post
asked how to do this from fields on the report. My information is based on a
query. I tried many variations of the DSum formula in the control source
property box of a text box. Currently:

=(((DSum("[Field1]","[Query1]"))/(DSum("[Field2]","[Query1]")),"0.00%"))

I've rattled my brain and strained my eyes for longer than I should have.
Any help please...please.
 
OK, I got this far:

=DSum("[Field1]","[Query]")/DSum("[Field2]","[Query]")

This seems to give my the decimal I need. But I need to convert that decimal
into a percentage. Currently, the decimal is .32, I need this # to say
31.98%. I tried:

=(DSum("[Field1]","[Query]")/DSum("[Field2]","[Query]"),"0.00%")

That did not work. Hope that information helps.
 
OK all, I got it. I just used the formula below and changed the Format
property to Percent --- duh!

Although I'm sure there was an easier way to accomplish what I needed.

G. said:
OK, I got this far:

=DSum("[Field1]","[Query]")/DSum("[Field2]","[Query]")

This seems to give my the decimal I need. But I need to convert that decimal
into a percentage. Currently, the decimal is .32, I need this # to say
31.98%. I tried:

=(DSum("[Field1]","[Query]")/DSum("[Field2]","[Query]"),"0.00%")

That did not work. Hope that information helps.

G. said:
I saw a past post relating to this but I couldn't apply the information to my
problem.

I need to sum 2 columns then divide the sum's to get a percentage. I also
need the output on the report to show as a percentage. The previous post
asked how to do this from fields on the report. My information is based on a
query. I tried many variations of the DSum formula in the control source
property box of a text box. Currently:

=(((DSum("[Field1]","[Query1]"))/(DSum("[Field2]","[Query1]")),"0.00%"))

I've rattled my brain and strained my eyes for longer than I should have.
Any help please...please.
Click to expand...
Click to expand...
 
You are making this a whole lot harder than it needs to be.
Assuming Field1 and Field2 are fields in the report's record source, here is
what you need.

Use your Sorting and Grouping to create a group based on the field you want
to sum on and add a footer for the group.

Put 3 text box controls in the group footer.

The control source for the first control should be:
=Sum(Field1)
For the second control
=Sum(Field2)
For the third control
=FirstControl/SecondControl

In the third control's format property, use Percent
--
Dave Hargis, Microsoft Access MVP


G. said:
OK all, I got it. I just used the formula below and changed the Format
property to Percent --- duh!

Although I'm sure there was an easier way to accomplish what I needed.

G. said:
OK, I got this far:

=DSum("[Field1]","[Query]")/DSum("[Field2]","[Query]")

This seems to give my the decimal I need. But I need to convert that decimal
into a percentage. Currently, the decimal is .32, I need this # to say
31.98%. I tried:

=(DSum("[Field1]","[Query]")/DSum("[Field2]","[Query]"),"0.00%")

That did not work. Hope that information helps.

G. said:
I saw a past post relating to this but I couldn't apply the information to my
problem.

I need to sum 2 columns then divide the sum's to get a percentage. I also
need the output on the report to show as a percentage. The previous post
asked how to do this from fields on the report. My information is based on a
query. I tried many variations of the DSum formula in the control source
property box of a text box. Currently:

=(((DSum("[Field1]","[Query1]"))/(DSum("[Field2]","[Query1]")),"0.00%"))

I've rattled my brain and strained my eyes for longer than I should have.
Any help please...please.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top