Report formula problem

D

deiopajw

I have the following table and its fields:

tbl_Products:
ProductName (text)
SqM (number)
Thickness (number)

Report (rpt_product) has been setup where it is sorted by Productname.
Essentially, the report is set up to show only the SqM totals for EACH
product group in the Thickness footer only.

ie:I'm only using ONLY the Thickness Footer to get the total for each
individual group type result.

Product SqM
A 11.70
A 11.70
A 108.72
A 16.20
A 9.90
A 39.42

total for A = 197.64 <----- this is the only thing that shows in the report
which is what i want. The same for B

The formula i'm using in the Control Source properties box of the respective
unbound text box as follows in the Thickness Footer of the report report:

Product
[ProductName]

Thickness
[Thickness]

SqM
=Sum([SqM])

But I have then added unbound text boxes to the Thickness Footer


Area
=[Thickness]*0.001

Volume
=Sum([SqM]*([Thickness]*0.001))

up to this point its ok but then I had the following problem with formula
to get the volume percentage for each Product type group:

% Volume Total

=([SqM]*([Thickness]*0.001))/Sum(([SqM]*([Thickness]*0.001)))

Note: product A is 0.1m thick
Total Area for the group in Product A = 197.64x0.1 = 19.764

Total Area for other products:
B = 2.5506
C=116.9872

Grand Total for all 3 products = 139.3018

This is where I want to divide this 19.764 by the combined Total Areas of
other Products. ie 19.764 / 139.3018 = 0.14187

My problem is using the above mentioned formula:
% Volume Total
=([SqM]*([Thickness]*0.001))/Sum(([SqM]*([Thickness]*0.001)))

does not work. I get in the report only the number 1.

help.
 
J

Jeff Boyce

Are you explicitly casting the results of your calculation into an integer
data type (would result in no decimal places)?

Are you explicitly casting ... into a currency, single or double data type
(should show decimal places)?
 
D

deiopajw

They are all General numbers. The decimals are all there.

Basically I have a total volume amount (General Number data format) of one
product type.
I don't want individual entries for that one product type showing up on the
report. Therefore, I have placed the fields in the ProductName Footer of the
report design.
That way I simply get the total volume amount for that one product type.
In the Report Footer section, I get the Volume Total value of all the
Product types combined.
Now, I simply want to take the total volume amount of that ONE product type
(found in the ProductName Footer) and divide it with the Total volume amount
of all the products combined (found in the Report Footer).

Problem: both these 2 unbound fields share the same formula:
=Sum([SqM]*([Thickness]*0.001))/Sum(([SqM]*([Thickness]*0.001)))

but obviously are in different sections of the report design.
I therefore get the value = 1. Thats understandable.

How can i setup a formula where it recognizes this difference of location in
the report and is able to give me as per my example in my previous posting:
ie (ProductName Footer): Product A Total Volume = 19.764

(Report Footer): All Products Total Volume = 139.3018

So then I would get my desired result of Volume % for product A as
19.764/139.3018 = 0.1418789

This Volume % unbound field will appear in the ProductName Footer, unless it
needs to go somewhere else in the report section.
thanks.






Jeff Boyce said:
Are you explicitly casting the results of your calculation into an integer
data type (would result in no decimal places)?

Are you explicitly casting ... into a currency, single or double data type
(should show decimal places)?

--
Regards

Jeff Boyce
<Office/Access MVP>

deiopajw said:
I have the following table and its fields:

tbl_Products:
ProductName (text)
SqM (number)
Thickness (number)

Report (rpt_product) has been setup where it is sorted by Productname.
Essentially, the report is set up to show only the SqM totals for EACH
product group in the Thickness footer only.

ie:I'm only using ONLY the Thickness Footer to get the total for each
individual group type result.

Product SqM
A 11.70
A 11.70
A 108.72
A 16.20
A 9.90
A 39.42

total for A = 197.64 <----- this is the only thing that shows in the report
which is what i want. The same for B

The formula i'm using in the Control Source properties box of the respective
unbound text box as follows in the Thickness Footer of the report report:

Product
[ProductName]

Thickness
[Thickness]

SqM
=Sum([SqM])

But I have then added unbound text boxes to the Thickness Footer


Area
=[Thickness]*0.001

Volume
=Sum([SqM]*([Thickness]*0.001))

up to this point its ok but then I had the following problem with formula
to get the volume percentage for each Product type group:

% Volume Total

=([SqM]*([Thickness]*0.001))/Sum(([SqM]*([Thickness]*0.001)))

Note: product A is 0.1m thick
Total Area for the group in Product A = 197.64x0.1 = 19.764

Total Area for other products:
B = 2.5506
C=116.9872

Grand Total for all 3 products = 139.3018

This is where I want to divide this 19.764 by the combined Total Areas of
other Products. ie 19.764 / 139.3018 = 0.14187

My problem is using the above mentioned formula:
% Volume Total
=([SqM]*([Thickness]*0.001))/Sum(([SqM]*([Thickness]*0.001)))

does not work. I get in the report only the number 1.

help.
Click to expand...
Click to expand...
 
J

Jeff Boyce

Perhaps you could take a different approach... could you do the math in you
report's underlying query?

Regards

Jeff Boyce
<Office/Access MVP>

deiopajw said:
They are all General numbers. The decimals are all there.

Basically I have a total volume amount (General Number data format) of one
product type.
I don't want individual entries for that one product type showing up on the
report. Therefore, I have placed the fields in the ProductName Footer of the
report design.
That way I simply get the total volume amount for that one product type.
In the Report Footer section, I get the Volume Total value of all the
Product types combined.
Now, I simply want to take the total volume amount of that ONE product type
(found in the ProductName Footer) and divide it with the Total volume amount
of all the products combined (found in the Report Footer).

Problem: both these 2 unbound fields share the same formula:
=Sum([SqM]*([Thickness]*0.001))/Sum(([SqM]*([Thickness]*0.001)))

but obviously are in different sections of the report design.
I therefore get the value = 1. Thats understandable.

How can i setup a formula where it recognizes this difference of location in
the report and is able to give me as per my example in my previous posting:
ie (ProductName Footer): Product A Total Volume = 19.764

(Report Footer): All Products Total Volume = 139.3018

So then I would get my desired result of Volume % for product A as
19.764/139.3018 = 0.1418789

This Volume % unbound field will appear in the ProductName Footer, unless it
needs to go somewhere else in the report section.
thanks.






Jeff Boyce said:
Are you explicitly casting the results of your calculation into an integer
data type (would result in no decimal places)?

Are you explicitly casting ... into a currency, single or double data type
(should show decimal places)?

--
Regards

Jeff Boyce
<Office/Access MVP>

deiopajw said:
I have the following table and its fields:

tbl_Products:
ProductName (text)
SqM (number)
Thickness (number)

Report (rpt_product) has been setup where it is sorted by Productname.
Essentially, the report is set up to show only the SqM totals for EACH
product group in the Thickness footer only.

ie:I'm only using ONLY the Thickness Footer to get the total for each
individual group type result.

Product SqM
A 11.70
A 11.70
A 108.72
A 16.20
A 9.90
A 39.42

total for A = 197.64 <----- this is the only thing that shows in the report
which is what i want. The same for B

The formula i'm using in the Control Source properties box of the respective
unbound text box as follows in the Thickness Footer of the report report:

Product
[ProductName]

Thickness
[Thickness]

SqM
=Sum([SqM])

But I have then added unbound text boxes to the Thickness Footer


Area
=[Thickness]*0.001

Volume
=Sum([SqM]*([Thickness]*0.001))

up to this point its ok but then I had the following problem with formula
to get the volume percentage for each Product type group:

% Volume Total

=([SqM]*([Thickness]*0.001))/Sum(([SqM]*([Thickness]*0.001)))

Note: product A is 0.1m thick
Total Area for the group in Product A = 197.64x0.1 = 19.764

Total Area for other products:
B = 2.5506
C=116.9872

Grand Total for all 3 products = 139.3018

This is where I want to divide this 19.764 by the combined Total Areas of
other Products. ie 19.764 / 139.3018 = 0.14187

My problem is using the above mentioned formula:
% Volume Total
=([SqM]*([Thickness]*0.001))/Sum(([SqM]*([Thickness]*0.001)))

does not work. I get in the report only the number 1.

help.
Click to expand...
Click to expand...
Click to expand...
 
D

Duane Hookom

PMFJI but I think you can add a text box to the Report Header section:
Name: txtRptTotal
Control Source: =Sum(([SqM]*([Thickness]*0.001)))

Then in each group footer, try a text box
Control Source: =Sum([SqM]*([Thickness]*0.001))/txtRptTotal

--
Duane Hookom
MS Access MVP


Jeff Boyce said:
Perhaps you could take a different approach... could you do the math in
you
report's underlying query?

Regards

Jeff Boyce
<Office/Access MVP>

deiopajw said:
They are all General numbers. The decimals are all there.

Basically I have a total volume amount (General Number data format) of
one
product type.
I don't want individual entries for that one product type showing up on the
report. Therefore, I have placed the fields in the ProductName Footer of the
report design.
That way I simply get the total volume amount for that one product type.
In the Report Footer section, I get the Volume Total value of all the
Product types combined.
Now, I simply want to take the total volume amount of that ONE product type
(found in the ProductName Footer) and divide it with the Total volume amount
of all the products combined (found in the Report Footer).

Problem: both these 2 unbound fields share the same formula:
=Sum([SqM]*([Thickness]*0.001))/Sum(([SqM]*([Thickness]*0.001)))

but obviously are in different sections of the report design.
I therefore get the value = 1. Thats understandable.

How can i setup a formula where it recognizes this difference of location in
the report and is able to give me as per my example in my previous posting:
ie (ProductName Footer): Product A Total Volume = 19.764

(Report Footer): All Products Total Volume = 139.3018

So then I would get my desired result of Volume % for product A as
19.764/139.3018 = 0.1418789

This Volume % unbound field will appear in the ProductName Footer, unless it
needs to go somewhere else in the report section.
thanks.






message
Are you explicitly casting the results of your calculation into an integer
data type (would result in no decimal places)?

Are you explicitly casting ... into a currency, single or double data type
(should show decimal places)?

--
Regards

Jeff Boyce
<Office/Access MVP>

I have the following table and its fields:

tbl_Products:
ProductName (text)
SqM (number)
Thickness (number)

Report (rpt_product) has been setup where it is sorted by
Productname.
Essentially, the report is set up to show only the SqM totals for
EACH
product group in the Thickness footer only.

ie:I'm only using ONLY the Thickness Footer to get the total for each
individual group type result.

Product SqM
A 11.70
A 11.70
A 108.72
A 16.20
A 9.90
A 39.42

total for A = 197.64 <----- this is the only thing that shows in the
report
which is what i want. The same for B

The formula i'm using in the Control Source properties box of the
respective
unbound text box as follows in the Thickness Footer of the report report:

Product
[ProductName]

Thickness
[Thickness]

SqM
=Sum([SqM])

But I have then added unbound text boxes to the Thickness Footer


Area
=[Thickness]*0.001

Volume
=Sum([SqM]*([Thickness]*0.001))

up to this point its ok but then I had the following problem with formula
to get the volume percentage for each Product type group:

% Volume Total

=([SqM]*([Thickness]*0.001))/Sum(([SqM]*([Thickness]*0.001)))

Note: product A is 0.1m thick
Total Area for the group in Product A = 197.64x0.1 = 19.764

Total Area for other products:
B = 2.5506
C=116.9872

Grand Total for all 3 products = 139.3018

This is where I want to divide this 19.764 by the combined Total
Areas of
other Products. ie 19.764 / 139.3018 = 0.14187

My problem is using the above mentioned formula:
% Volume Total
=([SqM]*([Thickness]*0.001))/Sum(([SqM]*([Thickness]*0.001)))

does not work. I get in the report only the number 1.

help.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Report Totals 2
Group Footer Summarizing Problem 5
Report Sums of Sums 1
How to Change Digits Into Words In Report 3
How to Sum Total cost field in report 2
Calculating overtime in report footer 2
Sum in report 10
Help with report sums 2

Top