How do I get a correct Net Total

[Garthe via AccessMonster.com]

The following Access Table captures mail Bob Jones sends to different
individuals, and the cost for the day it was sent. before breaking to
another customer the postage for all the week's transctions is totaled into a
Gross Charge then a Credit is allowed giving the amount, Net Postage Billed,
that would be billed to a customer. See Below:

Bob Jones Weekly Billable Postage

Package Sent To: M T W Th F
Jack Smith $0.00 $2.00 $0.00 $0.00 $0.00
Rob Brown $0.00 $0.00 $0.00 $0.00 $3.00
Joan Cool $0.00 $3.00 $0.00 $0.00 $4.20



Gross Charges: =[MONDAY]+[TUESDAY]+[WEDNESDAY]+[THURSDAY]+[FRIDAY] $12.20
Weekly Mail Credit: [LWMC] $2.20
Net Postage Billed: =[GROSSTOTAL]-[LWMC]
Expected Results: $10.00

Actual Results: $6.60


It appears that the Mail credit is being accumlated for each record listed -
(if there were 4 records the amount would then be $8.80-.

What I want to do is to take the Gross Charge Less a Fixed Credit of $2.20
yeilding a Net $10.00.
So far no matter how I change the formula - (even saying =sum([Grosstotal]-2.
20) only to have access then not show the figures for Mail Credit and Net
Postage billed I can not get the answer to come out to $10.00.

I welcome any ideas and solutions the Access Community would like to suggest.

 

[Al Campagna]

Garthe,
If only sales where the Total Amount - 2.20 is equal to, or
greater than ten, then... for each line...

IsAMailCredit = IIF(GrossCharges - WeeklyMailCredit >= 10, WeeklyMailCredit,
..00)

Net Postage Billed: =[GROSSTOTAL]-[IsAMailCredit]
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[Garthe via AccessMonster.com]

Al

Since my original posting i added some error trapping routines, which I will
remove when I rid the error. Here is what I have discovered. Again I welcome
your suggested solution. I think also you will have a better understanding
of my predicament with this new information. By the way what I am discussing
is not confidential information and the figures are fictious for the purpose
setting up the report.

Gary:




Purpose of Report:

To Produce a weekly report that will list all Outgoing Legal Mail generated
by an inmate and the Postage Charges, in excess of their Legal Weekly Mail
Credit – “LWMCâ€, that were billed to their account. This report will replace
two separate Excel Spreadsheet reports which are used to do the same thing.

What is / is not Happening when the report is run

This report is set up so that it is sorted by Ascending Order for Inmate
Identification Number so noted in Inmate Legal Mail Table/Date column of
the Query. Further to ensure only “Outgoing†mail is captured in this report
“Outgoing†was typed in the Mail Type Column for Inmate Legal Mail Table
Date/Mail Type Column.

All output records are listed by Inmate Din #(in the Detail Section). When
the Inmate Number changes then the summary totals are then posted in the
InmateDin# footer/Summary Area. – see attached.

At Present Cross Subtotal, LWMC, and Weekly Total Columns are being used in
the Detail Section and Inmate Name Footer Section to help debug the
calculations that are going astray. They will disappear when the report
successfully yields correct total amounts for Gross Total, Legal Weekly Mail
Credit “LWMC†and net billed legal mail, shown below.

Since this report will be run weekly the Inmate Legal Outgoing mail Query
needs to receive a start date and end
Date, from a user, in order to capture records to print out and talley
between those dates. What would be the best way to do so.


Report Sections:

Page Header: Name and date of report
InmateDin# Header: Inmate Name and Inmate Identification Number
Inmate Name Header: Report Column Field Label Names
Detail Section: Actual Text Fields containing data related to a
specific field
Inmate Name Footer: This is just being used to debug totals and will
disappear when
summary totals Are corrected to print out
in the InmateDin# Footer.
Page Footer: Produces the Page number for


While researching for a solution to my total issue I came across the
following write up. I am enclosing it on the chance it may contain the
solution I need. Please let me know if any parts, and what part(s) are
applicable to me. The article is from The Access Monster Web Page.

Essentially what is happening is the Subtotal and Legal Weekly Mail Credit
“LWMC†are totaling properly but the Net cross column total for
Weekly_Postage_AMT is combining the totals of the all the First Immate’s
Daily Gross Postage – 10.44 + the Legal Mail Credit of $2.20 then it is also
picking up the postage amount - $13.55 + the Legal Mail Credit - $2.20 for
the Second Inmate making a combined total of $28.39

For the first Inmate the Subtotal / Gross Total should reads $10.44
2nd Inmate: $13.55
Less: Legal Mail Credit
$ 2.20 $ 2.20

Expected Results:
$ 8.24 $11.35
Actual Results:
$28.39 $28.39

Garthe,
If only sales where the Total Amount - 2.20 is equal to, or
greater than ten, then... for each line...

IsAMailCredit = IIF(GrossCharges - WeeklyMailCredit >= 10, WeeklyMailCredit,
.00)

Net Postage Billed: =[GROSSTOTAL]-[IsAMailCredit]

The following Access Table captures mail Bob Jones sends to different
individuals, and the cost for the day it was sent. before breaking to

[quoted text clipped - 32 lines]

I welcome any ideas and solutions the Access Community would like to
suggest.

 

[Daryl S]

Garthe -

In the section footer for each person, put a field with this recordsource:
=sum([GROSSTOTAL])-[LWMC]
This will sum up the [GROSSTOTAL] for all records for the person, then
remove the LWMC amount.
This assumes you still have the [GROSSTOTAL] being calculated in the detail
section.

 

[Garthe via AccessMonster.com]

Daryl:

It did not work am attaching som information for you to help you further
analyze my situation,


Thanks!

Gary



The Following is located in the Detail line

Postage Amount Temporary Debug routines

Date Tracn # M T W Th F Sutotal LWMC Weekly_Amt
11/23/2009 5592 $6.00 $0.00 $0.00 $0.00 $0.00 $6.00 ($2.20)
11/25/2009 22982 $0.00 $0.00 $4.00 $0.00 $0.00 $4.00
11/26/2009 1798656 $0.00 $0.00 $0.00 $0.44 $0.00 $0.44
------------------------------------------------------------------------------
---------------------------------------------------------------------------

The Following is located in the Inmate Name Footer
Grosstotall =sum([subtotal])
LWMCC =sum([lwmc])
Weekly Amount =Sum([GROSSTOTAL])-[LWMC]


------------------------------------------------------------------------------
---------------------------------------------------------------------------

The Following is located in the InmateDIN# Footer

GROSS LEGAL MAIL CHARGE: =Sum([SUBTOTAL])
LESS: LEGAL WEEKLY MAIL CREDIT: =Sum([LWMC])
NET BILLED LEGAL MAIL INMATE ACCT CHARGE : =Sum([GROSSTOTAL])-[LWMC]


Expected Results

Inmate Jackson:

The Following is located in the Detail line

Postage Amount Temporary Debug routines

Date Tracn # M T W Th F Sutotal LWMC Weekly_Amt
11/23/2009 5592 $6.00 $0.00 $0.00 $0.00 $0.00 $6.00 ($2.20) $3.80
11/25/2009 22982 $0.00 $0.00 $4.00 $0.00 $0.00 $4.00 $4.00
11/26/2009 1798656 $0.00 $0.00 $0.00 $0.44 $0.00 $0.44 $0.44
------------------------------------------------------------------------------
---------------------------------------------------------------------------

The Following is located in the Inmate Name Footer
Grosstotall $10.44
LWMCC ($2.20)
Weekly Amount $8.24


------------------------------------------------------------------------------
---------------------------------------------------------------------------

The Following is located in the InmateDIN# Footer

GROSS LEGAL MAIL CHARGE: =Sum([SUBTOTAL]) $10.44
LESS: LEGAL WEEKLY MAIL CREDIT: =Sum([LWMC]) ($2.20)
NET BILLED LEGAL MAIL INMATE ACCT CHARGE : =Sum([GROSSTOTAL])-[LWMC]
$8.24


Actual Results Results

Inmate Jackson:

The Following is located in the Detail line

Postage Amount Temporary Debug routines

Date Tracn # M T W Th F Sutotal LWMC Weekly_Amt
11/23/2009 5592 $6.00 $0.00 $0.00 $0.00 $0.00 $6.00 ($2.20) $19.59
11/25/2009 22982 $0.00 $0.00 $4.00 $0.00 $0.00 $4.00 $19.59
11/26/2009 1798656 $0.00 $0.00 $0.00 $0.44 $0.00 $0.44 $19.59
------------------------------------------------------------------------------
---------------------------------------------------------------------------

The Following is located in the Inmate Name Footer
Grosstotall $10.44
LWMCC ($2.20)
Weekly Amount $0.00


------------------------------------------------------------------------------
---------------------------------------------------------------------------


Inmate Robinson:
The Following is located in the InmateDIN# Footer

GROSS LEGAL MAIL CHARGE: =Sum([SUBTOTAL]) $10.44
LESS: LEGAL WEEKLY MAIL CREDIT: =Sum([LWMC]) ($2.20)
NET BILLED LEGAL MAIL INMATE ACCT CHARGE : =Sum([GROSSTOTAL])-[LWMC]
$0.00


The Following is located in the Detail line

Postage Amount Temporary Debug routines

Date Tracn # M T W Th F Sutotal LWMC Weekly_Amt
11/23/2009 8898 $0.00 $10.05 $0.00 $0.00 $0.00 $10.05 ($2.20)
11/25/2009 $0.00 $0.00 $1.50 $0.00 $0.00 $1.50
11/26/2009 $0.00 $0.00 $0.00 $2.00 $0.00 $2.00
------------------------------------------------------------------------------
---------------------------------------------------------------------------

The Following is located in the Inmate Name Footer
Grosstotall =sum([subtotal])
LWMCC =sum([lwmc])
Weekly Amount =Sum([GROSSTOTAL])-[LWMC]


------------------------------------------------------------------------------
---------------------------------------------------------------------------

The Following is located in the InmateDIN# Footer

GROSS LEGAL MAIL CHARGE: =Sum([SUBTOTAL])
LESS: LEGAL WEEKLY MAIL CREDIT: =Sum([LWMC])
NET BILLED LEGAL MAIL INMATE ACCT CHARGE : =Sum([GROSSTOTAL])-[LWMC]

Expected Results

Inmate Jackson:

The Following is located in the Detail line

Postage Amount Temporary Debug routines

Date Tracn # M T W Th F Sutotal LWMC Weekly_Amt
11/23/2009 8898 $0.00 $10.05 $0.00 $0.00 $0.00 $10.05 ($2.20) $7.85
11/25/2009 $0.00 $0.00 $1.50 $0.00 $0.00 $1.50 $1.50
11/26/2009 $0.00 $0.00 $0.00 $2.00 $0.00 $2.00 $2.00
------------------------------------------------------------------------------
---------------------------------------------------------------------------

The Following is located in the Inmate Name Footer
Grosstotall $13.55
LWMCC ($2.20)
Weekly Amount $11.35


------------------------------------------------------------------------------
---------------------------------------------------------------------------

The Following is located in the InmateDIN# Footer

GROSS LEGAL MAIL CHARGE: =Sum([SUBTOTAL]) $13.55
LESS: LEGAL WEEKLY MAIL CREDIT: =Sum([LWMC]) ($2.20)
NET BILLED LEGAL MAIL INMATE ACCT CHARGE : =Sum([GROSSTOTAL])-[LWMC]
$11.35


Actual Results Results

Inmate Jackson:

The Following is located in the Detail line

Postage Amount Temporary Debug routines

Date Tracn # M T W Th F Sutotal LWMC Weekly_Amt
11/23/2009 8898 $0.00 $10.05 $0.00 $0.00 $0.00 $10.05 ($2.20) $19.59
11/25/2009 $0.00 $0.00 $1.50 $0.00 $0.00 $1.50 $19.59
11/26/2009 $0.00 $0.00 $0.00 $2.00 $0.00 $2.00 $19.59
------------------------------------------------------------------------------
---------------------------------------------------------------------------

The Following is located in the Inmate Name Footer
Grosstotall $10.44
LWMCC ($2.20)
Weekly Amount $0.00


------------------------------------------------------------------------------
---------------------------------------------------------------------------

The Following is located in the InmateDIN# Footer

GROSS LEGAL MAIL CHARGE: =Sum([SUBTOTAL]) $10.44
LESS: LEGAL WEEKLY MAIL CREDIT: =Sum([LWMC]) ($2.20)

NET BILLED LEGAL MAIL INMATE ACCT CHARGE : =Sum([GROSSTOTAL])-[LWMC] $0.
00

Garthe -

In the section footer for each person, put a field with this recordsource:
=sum([GROSSTOTAL])-[LWMC]
This will sum up the [GROSSTOTAL] for all records for the person, then
remove the LWMC amount.
This assumes you still have the [GROSSTOTAL] being calculated in the detail
section.

The following Access Table captures mail Bob Jones sends to different
individuals, and the cost for the day it was sent. before breaking to

[quoted text clipped - 27 lines]

I welcome any ideas and solutions the Access Community would like to suggest.

 

[Duane Hookom]

It would really help if you explained your table structure. I assum the LWMC
is tied to a group of records (a weeks worth). If the LWMC is not related to
each detail record, you will never use Sum() on it in your report. You may
need to use a running sum of a control bound to LWMC in a week footer to
correctly calculate the sum of LWMC.

--
Duane Hookom
Microsoft Access MVP

Daryl:

It did not work am attaching som information for you to help you further
analyze my situation,


Thanks!

Gary



The Following is located in the Detail line

Postage Amount Temporary Debug routines

Date Tracn # M T W Th F Sutotal LWMC Weekly_Amt
11/23/2009 5592 $6.00 $0.00 $0.00 $0.00 $0.00 $6.00 ($2.20)
11/25/2009 22982 $0.00 $0.00 $4.00 $0.00 $0.00 $4.00
11/26/2009 1798656 $0.00 $0.00 $0.00 $0.44 $0.00 $0.44
------------------------------------------------------------------------------
---------------------------------------------------------------------------

The Following is located in the Inmate Name Footer
Grosstotall =sum([subtotal])
LWMCC =sum([lwmc])
Weekly Amount =Sum([GROSSTOTAL])-[LWMC]


------------------------------------------------------------------------------
---------------------------------------------------------------------------

The Following is located in the InmateDIN# Footer

GROSS LEGAL MAIL CHARGE: =Sum([SUBTOTAL])
LESS: LEGAL WEEKLY MAIL CREDIT: =Sum([LWMC])
NET BILLED LEGAL MAIL INMATE ACCT CHARGE : =Sum([GROSSTOTAL])-[LWMC]


Expected Results

Inmate Jackson:

The Following is located in the Detail line

Postage Amount Temporary Debug routines

Date Tracn # M T W Th F Sutotal LWMC Weekly_Amt
11/23/2009 5592 $6.00 $0.00 $0.00 $0.00 $0.00 $6.00 ($2.20) $3.80
11/25/2009 22982 $0.00 $0.00 $4.00 $0.00 $0.00 $4.00 $4.00
11/26/2009 1798656 $0.00 $0.00 $0.00 $0.44 $0.00 $0.44 $0.44
------------------------------------------------------------------------------
---------------------------------------------------------------------------

The Following is located in the Inmate Name Footer
Grosstotall $10.44
LWMCC ($2.20)
Weekly Amount $8.24


------------------------------------------------------------------------------
---------------------------------------------------------------------------

The Following is located in the InmateDIN# Footer

GROSS LEGAL MAIL CHARGE: =Sum([SUBTOTAL]) $10.44
LESS: LEGAL WEEKLY MAIL CREDIT: =Sum([LWMC]) ($2.20)
NET BILLED LEGAL MAIL INMATE ACCT CHARGE : =Sum([GROSSTOTAL])-[LWMC]
$8.24


Actual Results Results

Inmate Jackson:

The Following is located in the Detail line

Postage Amount Temporary Debug routines

Date Tracn # M T W Th F Sutotal LWMC Weekly_Amt
11/23/2009 5592 $6.00 $0.00 $0.00 $0.00 $0.00 $6.00 ($2.20) $19.59
11/25/2009 22982 $0.00 $0.00 $4.00 $0.00 $0.00 $4.00 $19.59
11/26/2009 1798656 $0.00 $0.00 $0.00 $0.44 $0.00 $0.44 $19.59
------------------------------------------------------------------------------
---------------------------------------------------------------------------

The Following is located in the Inmate Name Footer
Grosstotall $10.44
LWMCC ($2.20)
Weekly Amount $0.00


------------------------------------------------------------------------------
---------------------------------------------------------------------------


Inmate Robinson:
The Following is located in the InmateDIN# Footer

GROSS LEGAL MAIL CHARGE: =Sum([SUBTOTAL]) $10.44
LESS: LEGAL WEEKLY MAIL CREDIT: =Sum([LWMC]) ($2.20)
NET BILLED LEGAL MAIL INMATE ACCT CHARGE : =Sum([GROSSTOTAL])-[LWMC]
$0.00


The Following is located in the Detail line

Postage Amount Temporary Debug routines

Date Tracn # M T W Th F Sutotal LWMC Weekly_Amt
11/23/2009 8898 $0.00 $10.05 $0.00 $0.00 $0.00 $10.05 ($2.20)
11/25/2009 $0.00 $0.00 $1.50 $0.00 $0.00 $1.50
11/26/2009 $0.00 $0.00 $0.00 $2.00 $0.00 $2.00
------------------------------------------------------------------------------
---------------------------------------------------------------------------

The Following is located in the Inmate Name Footer
Grosstotall =sum([subtotal])
LWMCC =sum([lwmc])
Weekly Amount =Sum([GROSSTOTAL])-[LWMC]


------------------------------------------------------------------------------
---------------------------------------------------------------------------

The Following is located in the InmateDIN# Footer

GROSS LEGAL MAIL CHARGE: =Sum([SUBTOTAL])
LESS: LEGAL WEEKLY MAIL CREDIT: =Sum([LWMC])
NET BILLED LEGAL MAIL INMATE ACCT CHARGE : =Sum([GROSSTOTAL])-[LWMC]

Expected Results

Inmate Jackson:

The Following is located in the Detail line

Postage Amount Temporary Debug routines

Date Tracn # M T W Th F Sutotal LWMC Weekly_Amt
11/23/2009 8898 $0.00 $10.05 $0.00 $0.00 $0.00 $10.05 ($2.20) $7.85
11/25/2009 $0.00 $0.00 $1.50 $0.00 $0.00 $1.50 $1.50
11/26/2009 $0.00 $0.00 $0.00 $2.00 $0.00 $2.00 $2.00
------------------------------------------------------------------------------
---------------------------------------------------------------------------

The Following is located in the Inmate Name Footer
Grosstotall $13.55
LWMCC ($2.20)
Weekly Amount $11.35


------------------------------------------------------------------------------
---------------------------------------------------------------------------

The Following is located in the InmateDIN# Footer

GROSS LEGAL MAIL CHARGE: =Sum([SUBTOTAL]) $13.55
LESS: LEGAL WEEKLY MAIL CREDIT: =Sum([LWMC]) ($2.20)
NET BILLED LEGAL MAIL INMATE ACCT CHARGE : =Sum([GROSSTOTAL])-[LWMC]
$11.35


Actual Results Results

Inmate Jackson:

The Following is located in the Detail line

Postage Amount Temporary Debug routines

Date Tracn # M T W Th F Sutotal LWMC Weekly_Amt
11/23/2009 8898 $0.00 $10.05 $0.00 $0.00 $0.00 $10.05 ($2.20) $19.59
11/25/2009 $0.00 $0.00 $1.50 $0.00 $0.00 $1.50 $19.59
11/26/2009 $0.00 $0.00 $0.00 $2.00 $0.00 $2.00 $19.59
------------------------------------------------------------------------------
---------------------------------------------------------------------------

The Following is located in the Inmate Name Footer
Grosstotall $10.44
LWMCC ($2.20)
Weekly Amount $0.00


------------------------------------------------------------------------------
---------------------------------------------------------------------------

The Following is located in the InmateDIN# Footer

GROSS LEGAL MAIL CHARGE: =Sum([SUBTOTAL]) $10.44
LESS: LEGAL WEEKLY MAIL CREDIT: =Sum([LWMC]) ($2.20)

NET BILLED LEGAL MAIL INMATE ACCT CHARGE : =Sum([GROSSTOTAL])-[LWMC] $0.
00

Garthe -

In the section footer for each person, put a field with this recordsource:
=sum([GROSSTOTAL])-[LWMC]
This will sum up the [GROSSTOTAL] for all records for the person, then
remove the LWMC amount.
This assumes you still have the [GROSSTOTAL] being calculated in the detail
section.

The following Access Table captures mail Bob Jones sends to different
individuals, and the cost for the day it was sent. before breaking to

[quoted text clipped - 27 lines]

I welcome any ideas and solutions the Access Community would like to suggest.

 

[Daryl S]

Garthe -

It looks like your sum([LWMC]) is correct in each case. If so, then your
weekly totals should be changed to
=Sum([GROSSTOTAL])-sum([LWMC])


--
Daryl S

Daryl:

It did not work am attaching som information for you to help you further
analyze my situation,


Thanks!

Gary



The Following is located in the Detail line

Postage Amount Temporary Debug routines

Date Tracn # M T W Th F Sutotal LWMC Weekly_Amt
11/23/2009 5592 $6.00 $0.00 $0.00 $0.00 $0.00 $6.00 ($2.20)
11/25/2009 22982 $0.00 $0.00 $4.00 $0.00 $0.00 $4.00
11/26/2009 1798656 $0.00 $0.00 $0.00 $0.44 $0.00 $0.44
------------------------------------------------------------------------------
---------------------------------------------------------------------------

The Following is located in the Inmate Name Footer
Grosstotall =sum([subtotal])
LWMCC =sum([lwmc])
Weekly Amount =Sum([GROSSTOTAL])-[LWMC]


------------------------------------------------------------------------------
---------------------------------------------------------------------------

The Following is located in the InmateDIN# Footer

GROSS LEGAL MAIL CHARGE: =Sum([SUBTOTAL])
LESS: LEGAL WEEKLY MAIL CREDIT: =Sum([LWMC])
NET BILLED LEGAL MAIL INMATE ACCT CHARGE : =Sum([GROSSTOTAL])-[LWMC]


Expected Results

Inmate Jackson:

The Following is located in the Detail line

Postage Amount Temporary Debug routines

Date Tracn # M T W Th F Sutotal LWMC Weekly_Amt
11/23/2009 5592 $6.00 $0.00 $0.00 $0.00 $0.00 $6.00 ($2.20) $3.80
11/25/2009 22982 $0.00 $0.00 $4.00 $0.00 $0.00 $4.00 $4.00
11/26/2009 1798656 $0.00 $0.00 $0.00 $0.44 $0.00 $0.44 $0.44
------------------------------------------------------------------------------
---------------------------------------------------------------------------

The Following is located in the Inmate Name Footer
Grosstotall $10.44
LWMCC ($2.20)
Weekly Amount $8.24


------------------------------------------------------------------------------
---------------------------------------------------------------------------

The Following is located in the InmateDIN# Footer

GROSS LEGAL MAIL CHARGE: =Sum([SUBTOTAL]) $10.44
LESS: LEGAL WEEKLY MAIL CREDIT: =Sum([LWMC]) ($2.20)
NET BILLED LEGAL MAIL INMATE ACCT CHARGE : =Sum([GROSSTOTAL])-[LWMC]
$8.24


Actual Results Results

Inmate Jackson:

The Following is located in the Detail line

Postage Amount Temporary Debug routines

Date Tracn # M T W Th F Sutotal LWMC Weekly_Amt
11/23/2009 5592 $6.00 $0.00 $0.00 $0.00 $0.00 $6.00 ($2.20) $19.59
11/25/2009 22982 $0.00 $0.00 $4.00 $0.00 $0.00 $4.00 $19.59
11/26/2009 1798656 $0.00 $0.00 $0.00 $0.44 $0.00 $0.44 $19.59
------------------------------------------------------------------------------
---------------------------------------------------------------------------

The Following is located in the Inmate Name Footer
Grosstotall $10.44
LWMCC ($2.20)
Weekly Amount $0.00


------------------------------------------------------------------------------
---------------------------------------------------------------------------


Inmate Robinson:
The Following is located in the InmateDIN# Footer

GROSS LEGAL MAIL CHARGE: =Sum([SUBTOTAL]) $10.44
LESS: LEGAL WEEKLY MAIL CREDIT: =Sum([LWMC]) ($2.20)
NET BILLED LEGAL MAIL INMATE ACCT CHARGE : =Sum([GROSSTOTAL])-[LWMC]
$0.00


The Following is located in the Detail line

Postage Amount Temporary Debug routines

Date Tracn # M T W Th F Sutotal LWMC Weekly_Amt
11/23/2009 8898 $0.00 $10.05 $0.00 $0.00 $0.00 $10.05 ($2.20)
11/25/2009 $0.00 $0.00 $1.50 $0.00 $0.00 $1.50
11/26/2009 $0.00 $0.00 $0.00 $2.00 $0.00 $2.00
------------------------------------------------------------------------------
---------------------------------------------------------------------------

The Following is located in the Inmate Name Footer
Grosstotall =sum([subtotal])
LWMCC =sum([lwmc])
Weekly Amount =Sum([GROSSTOTAL])-[LWMC]


------------------------------------------------------------------------------
---------------------------------------------------------------------------

The Following is located in the InmateDIN# Footer

GROSS LEGAL MAIL CHARGE: =Sum([SUBTOTAL])
LESS: LEGAL WEEKLY MAIL CREDIT: =Sum([LWMC])
NET BILLED LEGAL MAIL INMATE ACCT CHARGE : =Sum([GROSSTOTAL])-[LWMC]

Expected Results

Inmate Jackson:

The Following is located in the Detail line

Postage Amount Temporary Debug routines

Date Tracn # M T W Th F Sutotal LWMC Weekly_Amt
11/23/2009 8898 $0.00 $10.05 $0.00 $0.00 $0.00 $10.05 ($2.20) $7.85
11/25/2009 $0.00 $0.00 $1.50 $0.00 $0.00 $1.50 $1.50
11/26/2009 $0.00 $0.00 $0.00 $2.00 $0.00 $2.00 $2.00
------------------------------------------------------------------------------
---------------------------------------------------------------------------

The Following is located in the Inmate Name Footer
Grosstotall $13.55
LWMCC ($2.20)
Weekly Amount $11.35


------------------------------------------------------------------------------
---------------------------------------------------------------------------

The Following is located in the InmateDIN# Footer

GROSS LEGAL MAIL CHARGE: =Sum([SUBTOTAL]) $13.55
LESS: LEGAL WEEKLY MAIL CREDIT: =Sum([LWMC]) ($2.20)
NET BILLED LEGAL MAIL INMATE ACCT CHARGE : =Sum([GROSSTOTAL])-[LWMC]
$11.35


Actual Results Results

Inmate Jackson:

The Following is located in the Detail line

Postage Amount Temporary Debug routines

Date Tracn # M T W Th F Sutotal LWMC Weekly_Amt
11/23/2009 8898 $0.00 $10.05 $0.00 $0.00 $0.00 $10.05 ($2.20) $19.59
11/25/2009 $0.00 $0.00 $1.50 $0.00 $0.00 $1.50 $19.59
11/26/2009 $0.00 $0.00 $0.00 $2.00 $0.00 $2.00 $19.59
------------------------------------------------------------------------------
---------------------------------------------------------------------------

The Following is located in the Inmate Name Footer
Grosstotall $10.44
LWMCC ($2.20)
Weekly Amount $0.00


------------------------------------------------------------------------------
---------------------------------------------------------------------------

The Following is located in the InmateDIN# Footer

GROSS LEGAL MAIL CHARGE: =Sum([SUBTOTAL]) $10.44
LESS: LEGAL WEEKLY MAIL CREDIT: =Sum([LWMC]) ($2.20)

NET BILLED LEGAL MAIL INMATE ACCT CHARGE : =Sum([GROSSTOTAL])-[LWMC] $0.
00

Garthe -

In the section footer for each person, put a field with this recordsource:
=sum([GROSSTOTAL])-[LWMC]
This will sum up the [GROSSTOTAL] for all records for the person, then
remove the LWMC amount.
This assumes you still have the [GROSSTOTAL] being calculated in the detail
section.

The following Access Table captures mail Bob Jones sends to different
individuals, and the cost for the day it was sent. before breaking to

[quoted text clipped - 27 lines]

I welcome any ideas and solutions the Access Community would like to suggest.

 

[Garthe via AccessMonster.com]

Duane Hookum & Daryl S:

I found the mistake - It was in the Net Billed Line Formula:

I was using Grosstotal instead of Subtotal less LWMC. When I corrected it
to read: =sum([subtotal]+[LWMC] (note: LWMC is entered as a negative number)

my figures came out the way they were suppose to.

Gross Legal Mail Charge: =sum([Subtotal]) 10.
44
Less LWMC: =sum([LWMC]) -
(2.20)
Net Filled =sum([subtotal]+[LWMC]
8.24

Again thanks Guys for all your help.

Gary

It would really help if you explained your table structure. I assum the LWMC
is tied to a group of records (a weeks worth). If the LWMC is not related to
each detail record, you will never use Sum() on it in your report. You may
need to use a running sum of a control bound to LWMC in a week footer to
correctly calculate the sum of LWMC.

Daryl:

[quoted text clipped - 195 lines]