Profit/Loss Calculation in Subreport

[Heather]

I have one main report with 3 subreports. The 2nd two are working just fine,
doing what I want them to do. The 1st is my problem. I have 5 columns on
the report (A/R, COGS, A/P, Labor, Installation, Warranty, Commission). They
are totaling in the Report Footer like they should (I've done the
calculations myself - they're coming out right). I'd like to show the Profit
/ Loss for each project (one report page per project). The formula I've
tried is something like this...

sum(A/R) - sum(COGS) - sum (A/P) - sum(Labor)...etc.

Why isn't this working?!?!?!!!!
Thanks!
Heather

 

[Duane Hookom]

"something like this..." doesn't provide all the information required.

Did you put this in a text box in a group or report footer section? Did you
include "="? Did you place []s around your field names that contain symbols
and/or spaces?

 

[Larry Linson]

It's not clear to me what the relationship of the main report is to the sub
reports, nor where the column data are located, nor where you are trying to
calculate the P/L. The SUM functions work nicely when they are put in a
Footer and are summing Fields in the Detail to which that Footer applies.
On the other hand, if you are trying in the main Report to SUM Fields which
appear in the Subreport, that won't work directly... if you can clarify what
you have, what the relationship of main Report to Subreports is (typically
you'll have a Master Link Fields and Child Link Fields property defined for
the Subreport control, and where you want the Summation performed (if you
are grouping on Project and put it in the Project group Footer, that should
work... if you are putting it in the Page footer, that does not work the
same).

Larry Linson
Microsoft Office Access MVP

I have one main report with 3 subreports. The 2nd two are working just
fine,
doing what I want them to do. The 1st is my problem. I have 5 columns on
the report (A/R, COGS, A/P, Labor, Installation, Warranty, Commission).
They
are totaling in the Report Footer like they should (I've done the
calculations myself - they're coming out right). I'd like to show the
Profit
/ Loss for each project (one report page per project). The formula I've
tried is something like this...

sum(A/R) - sum(COGS) - sum (A/P) - sum(Labor)...etc.

Why isn't this working?!?!?!!!!
Thanks!
Heather

__________ Information from ESET Smart Security, version of virus
signature database 4000 (20090410) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus signature database 4000 (20090410) __________

The message was checked by ESET Smart Security.

http://www.eset.com

 

[Heather]

Duane,

I've done a lot of reading since I posted my initial question and have
learned that Access can't sum a sum (much unlike Excel...I'm very good with
Excel).

OK, here it goes:
I have tables for the following: Project Cost Main, Project Cost Detail,
Backbilled Invoices, and Sales Expenses. My main report is based on the
Project Cost Main table (holds customer name, machine type purchased, serial
number, salesperson). I have that information at the top of each page for
each individual project. Below that is a subreport based on the Project Cost
Detail table. It shows the following information: item (the machine sold,
any accessories, etc.), A/R, Cost of Goods, A/P, Labor to prep machine for
shipment, Installation and Warranty costs, and Commissions paid to the
salesperson. What I'm trying to do with this particular subreport is sum the
following: A/R, Cost of Goods, A/P, Labor, Installation, Warranty, and
Commission, and then get the Profit / Loss (sum(A/R) - sum(everything else)).
My calculations for the summations of A/R, COGS, A/P, Labor, etc. are
working perfectly. I have the formulas in the report footer for THAT
subreport. However, what i CANNOT get is the P/L calculation.

Like I stated, I've learned that Access can't sum a sum. If that is the
case, what are my options? I don't understand VBA too well, so the more
simple, the better.

"something like this..." doesn't provide all the information required.

Did you put this in a text box in a group or report footer section? Did you
include "="? Did you place []s around your field names that contain symbols
and/or spaces?

--
Duane Hookom
Microsoft Access MVP

I have one main report with 3 subreports. The 2nd two are working just fine,
doing what I want them to do. The 1st is my problem. I have 5 columns on
the report (A/R, COGS, A/P, Labor, Installation, Warranty, Commission). They
are totaling in the Report Footer like they should (I've done the
calculations myself - they're coming out right). I'd like to show the Profit
/ Loss for each project (one report page per project). The formula I've
tried is something like this...

sum(A/R) - sum(COGS) - sum (A/P) - sum(Labor)...etc.

Why isn't this working?!?!?!!!!
Thanks!
Heather

 

[Duane Hookom]

I don't think you want to Sum a Sum. Since you have an un-normalized table
structure, you should be able to use the following in your subreport footer:
=Sum([A/R])-Sum([Cost Of Goods] + [A/P] + [Labor To Prep] + ...)
If any of these might be null, you need to use Nz() to convert the nulls to 0.

--
Duane Hookom
Microsoft Access MVP

Duane,

I've done a lot of reading since I posted my initial question and have
learned that Access can't sum a sum (much unlike Excel...I'm very good with
Excel).

OK, here it goes:
I have tables for the following: Project Cost Main, Project Cost Detail,
Backbilled Invoices, and Sales Expenses. My main report is based on the
Project Cost Main table (holds customer name, machine type purchased, serial
number, salesperson). I have that information at the top of each page for
each individual project. Below that is a subreport based on the Project Cost
Detail table. It shows the following information: item (the machine sold,
any accessories, etc.), A/R, Cost of Goods, A/P, Labor to prep machine for
shipment, Installation and Warranty costs, and Commissions paid to the
salesperson. What I'm trying to do with this particular subreport is sum the
following: A/R, Cost of Goods, A/P, Labor, Installation, Warranty, and
Commission, and then get the Profit / Loss (sum(A/R) - sum(everything else)).
My calculations for the summations of A/R, COGS, A/P, Labor, etc. are
working perfectly. I have the formulas in the report footer for THAT
subreport. However, what i CANNOT get is the P/L calculation.

Like I stated, I've learned that Access can't sum a sum. If that is the
case, what are my options? I don't understand VBA too well, so the more
simple, the better.

"something like this..." doesn't provide all the information required.

Did you put this in a text box in a group or report footer section? Did you
include "="? Did you place []s around your field names that contain symbols
and/or spaces?

--
Duane Hookom
Microsoft Access MVP

I have one main report with 3 subreports. The 2nd two are working just fine,
doing what I want them to do. The 1st is my problem. I have 5 columns on
the report (A/R, COGS, A/P, Labor, Installation, Warranty, Commission). They
are totaling in the Report Footer like they should (I've done the
calculations myself - they're coming out right). I'd like to show the Profit
/ Loss for each project (one report page per project). The formula I've
tried is something like this...

sum(A/R) - sum(COGS) - sum (A/P) - sum(Labor)...etc.

Why isn't this working?!?!?!!!!
Thanks!
Heather

 

[Heather]

There is a chance that a value would be zero. How do you use the Nz()?

I don't think you want to Sum a Sum. Since you have an un-normalized table
structure, you should be able to use the following in your subreport footer:
=Sum([A/R])-Sum([Cost Of Goods] + [A/P] + [Labor To Prep] + ...)
If any of these might be null, you need to use Nz() to convert the nulls to 0.

--
Duane Hookom
Microsoft Access MVP

Duane,

I've done a lot of reading since I posted my initial question and have
learned that Access can't sum a sum (much unlike Excel...I'm very good with
Excel).

OK, here it goes:
I have tables for the following: Project Cost Main, Project Cost Detail,
Backbilled Invoices, and Sales Expenses. My main report is based on the
Project Cost Main table (holds customer name, machine type purchased, serial
number, salesperson). I have that information at the top of each page for
each individual project. Below that is a subreport based on the Project Cost
Detail table. It shows the following information: item (the machine sold,
any accessories, etc.), A/R, Cost of Goods, A/P, Labor to prep machine for
shipment, Installation and Warranty costs, and Commissions paid to the
salesperson. What I'm trying to do with this particular subreport is sum the
following: A/R, Cost of Goods, A/P, Labor, Installation, Warranty, and
Commission, and then get the Profit / Loss (sum(A/R) - sum(everything else)).
My calculations for the summations of A/R, COGS, A/P, Labor, etc. are
working perfectly. I have the formulas in the report footer for THAT
subreport. However, what i CANNOT get is the P/L calculation.

Like I stated, I've learned that Access can't sum a sum. If that is the
case, what are my options? I don't understand VBA too well, so the more
simple, the better.

"something like this..." doesn't provide all the information required.

Did you put this in a text box in a group or report footer section? Did you
include "="? Did you place []s around your field names that contain symbols
and/or spaces?

--
Duane Hookom
Microsoft Access MVP


:

I have one main report with 3 subreports. The 2nd two are working just fine,
doing what I want them to do. The 1st is my problem. I have 5 columns on
the report (A/R, COGS, A/P, Labor, Installation, Warranty, Commission). They
are totaling in the Report Footer like they should (I've done the
calculations myself - they're coming out right). I'd like to show the Profit
/ Loss for each project (one report page per project). The formula I've
tried is something like this...

sum(A/R) - sum(COGS) - sum (A/P) - sum(Labor)...etc.

Why isn't this working?!?!?!!!!
Thanks!
Heather

 

[Duane Hookom]

The expression I suggested should have no problems with zero values. Did you
try it? Did it give you the appropriate value?

If you might have Nulls, try:
=Sum([A/R])-Sum(Nz([Cost Of Goods],0) + Nz([A/P],0) + Nz([Labor To Prep],0)
+ ...)

--
Duane Hookom
Microsoft Access MVP

There is a chance that a value would be zero. How do you use the Nz()?

I don't think you want to Sum a Sum. Since you have an un-normalized table
structure, you should be able to use the following in your subreport footer:
=Sum([A/R])-Sum([Cost Of Goods] + [A/P] + [Labor To Prep] + ...)
If any of these might be null, you need to use Nz() to convert the nulls to 0.

--
Duane Hookom
Microsoft Access MVP

Duane,

I've done a lot of reading since I posted my initial question and have
learned that Access can't sum a sum (much unlike Excel...I'm very good with
Excel).

OK, here it goes:
I have tables for the following: Project Cost Main, Project Cost Detail,
Backbilled Invoices, and Sales Expenses. My main report is based on the
Project Cost Main table (holds customer name, machine type purchased, serial
number, salesperson). I have that information at the top of each page for
each individual project. Below that is a subreport based on the Project Cost
Detail table. It shows the following information: item (the machine sold,
any accessories, etc.), A/R, Cost of Goods, A/P, Labor to prep machine for
shipment, Installation and Warranty costs, and Commissions paid to the
salesperson. What I'm trying to do with this particular subreport is sum the
following: A/R, Cost of Goods, A/P, Labor, Installation, Warranty, and
Commission, and then get the Profit / Loss (sum(A/R) - sum(everything else)).
My calculations for the summations of A/R, COGS, A/P, Labor, etc. are
working perfectly. I have the formulas in the report footer for THAT
subreport. However, what i CANNOT get is the P/L calculation.

Like I stated, I've learned that Access can't sum a sum. If that is the
case, what are my options? I don't understand VBA too well, so the more
simple, the better.


:

"something like this..." doesn't provide all the information required.

Did you put this in a text box in a group or report footer section? Did you
include "="? Did you place []s around your field names that contain symbols
and/or spaces?

--
Duane Hookom
Microsoft Access MVP


:

I have one main report with 3 subreports. The 2nd two are working just fine,
doing what I want them to do. The 1st is my problem. I have 5 columns on
the report (A/R, COGS, A/P, Labor, Installation, Warranty, Commission). They
are totaling in the Report Footer like they should (I've done the
calculations myself - they're coming out right). I'd like to show the Profit
/ Loss for each project (one report page per project). The formula I've
tried is something like this...

sum(A/R) - sum(COGS) - sum (A/P) - sum(Labor)...etc.

Why isn't this working?!?!?!!!!
Thanks!
Heather

 

[Heather]

Late yesterday afternoon I finally got my report to work! I ended up
entering zero values where we don't yet have an expense (installation,
warranty, etc.). My P/L is correct for each project!

Thank you for your help!
~H

The expression I suggested should have no problems with zero values. Did you
try it? Did it give you the appropriate value?

If you might have Nulls, try:
=Sum([A/R])-Sum(Nz([Cost Of Goods],0) + Nz([A/P],0) + Nz([Labor To Prep],0)
+ ...)

--
Duane Hookom
Microsoft Access MVP

There is a chance that a value would be zero. How do you use the Nz()?

I don't think you want to Sum a Sum. Since you have an un-normalized table
structure, you should be able to use the following in your subreport footer:
=Sum([A/R])-Sum([Cost Of Goods] + [A/P] + [Labor To Prep] + ...)
If any of these might be null, you need to use Nz() to convert the nulls to 0.

--
Duane Hookom
Microsoft Access MVP


:

Duane,

I've done a lot of reading since I posted my initial question and have
learned that Access can't sum a sum (much unlike Excel...I'm very good with
Excel).

OK, here it goes:
I have tables for the following: Project Cost Main, Project Cost Detail,
Backbilled Invoices, and Sales Expenses. My main report is based on the
Project Cost Main table (holds customer name, machine type purchased, serial
number, salesperson). I have that information at the top of each page for
each individual project. Below that is a subreport based on the Project Cost
Detail table. It shows the following information: item (the machine sold,
any accessories, etc.), A/R, Cost of Goods, A/P, Labor to prep machine for
shipment, Installation and Warranty costs, and Commissions paid to the
salesperson. What I'm trying to do with this particular subreport is sum the
following: A/R, Cost of Goods, A/P, Labor, Installation, Warranty, and
Commission, and then get the Profit / Loss (sum(A/R) - sum(everything else)).
My calculations for the summations of A/R, COGS, A/P, Labor, etc. are
working perfectly. I have the formulas in the report footer for THAT
subreport. However, what i CANNOT get is the P/L calculation.

Like I stated, I've learned that Access can't sum a sum. If that is the
case, what are my options? I don't understand VBA too well, so the more
simple, the better.


:

"something like this..." doesn't provide all the information required.

Did you put this in a text box in a group or report footer section? Did you
include "="? Did you place []s around your field names that contain symbols
and/or spaces?

--
Duane Hookom
Microsoft Access MVP


:

I have one main report with 3 subreports. The 2nd two are working just fine,
doing what I want them to do. The 1st is my problem. I have 5 columns on
the report (A/R, COGS, A/P, Labor, Installation, Warranty, Commission). They
are totaling in the Report Footer like they should (I've done the
calculations myself - they're coming out right). I'd like to show the Profit
/ Loss for each project (one report page per project). The formula I've
tried is something like this...

sum(A/R) - sum(COGS) - sum (A/P) - sum(Labor)...etc.

Why isn't this working?!?!?!!!!
Thanks!
Heather