MSGROUPS.NET | Search | Post Question | Groups | Stream | About | Register

### How do I get a correct Net Total

• Follow

```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.

--
Message posted via http://www.accessmonster.com

```
 0

```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" <u56381@uwe> wrote in message
news:a0287587799e4@uwe...
> 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.
>
> --
> Message posted via http://www.accessmonster.com
>

```
 0

```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

Al Campagna wrote:
>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.

--
Message posted via http://www.accessmonster.com

```
 0

```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.

--
Daryl S

"Garthe via AccessMonster.com" wrote:

> 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.
>
> --
> Message posted via http://www.accessmonster.com
>
> .
>
```
 0

```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

Daryl S wrote:
>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.

--
Message posted via http://www.accessmonster.com

```
 0

```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

"Garthe via AccessMonster.com" wrote:

> 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
>
>
>
>
>
>
>
>
>
> Daryl S wrote:
> >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.
>
> --
> Message posted via http://www.accessmonster.com
>
> .
>
```
 0

```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

"Garthe via AccessMonster.com" wrote:

> 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
>
>
>
>
>
>
>
>
>
> Daryl S wrote:
> >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.
>
> --
> Message posted via http://www.accessmonster.com
>
> .
>
```
 0

```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

Duane Hookom wrote:
>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]
>> >>
>> >> I welcome any ideas and solutions the Access Community would like to suggest.

--
Message posted via http://www.accessmonster.com

```
 0

7 Replies
141 Views

Similiar Articles:

7/26/2012 1:08:34 AM