Calculate total amt due on an invoice.

Hi,

I’m using Access via XP Office Pro on Windows 2007.

Tables:
tblWorkOrder – contains all the parent level information for the work order
/ invoice
Key – WorkOrder – automatically assigned number
Work order data


tblWorkOrderDet – contains all the child / transaction level information for
the work order.
Key: WorkOrderSeqNo – this is an automatically assigned keky
Data: WorkOrder

There are four transaction types contained in this table:
1. Comments on the work done on this work order.
2. Labor charges Quantity (Hours) * Rate
3. Part charges Quantity * Price per (Rate) + Shipping + Tax
4. Expense Quantity (miles) * Rate


Background:
I am working on an invoice report. The report has the main report and a
sub-report. The report is grouped by invoice / work order number. I have a
group heading called WOHeader and a group footing called WOFooter.

The main report’s row source is the tblWorkOrder table. The sub-report’s
row source is tblWorkOrderDet.

The sub-report contains the following fields:
txtTransDate, txtWorkDesc (which is built using multiple fields),
txtQuantity, txtRate, txtTaxAmt, txtShipping, and txtTotAmtDue (=txtQuantity
* txtRate). I want to total the tax amt and shipping and show them on the
bottom of the invoice. They are currently hidden fields.

I understand that I need to have hidden “Running Sum� text boxes on the
group heading for the three amounts (total due, tax, shipping). The visible
boxes for these amounts are in the group footing.

I’ve read the forum discussion entitled Referencing sub-report fields on
main report:

http://www.microsoft.com/communities...d-01389ddddec1


Question:
I trying following the instructions in that discussion to calculate the
following totals in WOHeader:
1. Total amount due (Quantity * rate).
2. Total Shipping
3. Total Tax.

Let’s just talk about the first field because once I get that one working, I
can get the other fields.

In the WOHeader, I have a text box called txtWOHAmtDue. It’s control source
is:
=rptInvoice_srDet.Report!txtAmtDue
And it’s Running Sum property is set to Over group. For debugging, this box
is visible.

When I run my test, I have an invoice with three labor transactions. The
Total Amt due for each transaction is:

1. $45.00
2. $11.25
3. $ 0.00

The total in my txtWOHAmtDue is $45, not the expected 55.25. It appears to
only be including the total from the first deail.

What am I doing wrong?


Dennis

Dennis wrote:
I’m using Access via XP Office Pro on Windows 2007.

Tables:
tblWorkOrder – contains all the parent level information for the work order
/ invoice
Key – WorkOrder – automatically assigned number
Work order data


tblWorkOrderDet – contains all the child / transaction level information for
the work order.
Key: WorkOrderSeqNo – this is an automatically assigned keky
Data: WorkOrder

There are four transaction types contained in this table:
1. Comments on the work done on this work order.
2. Labor charges Quantity (Hours) * Rate
3. Part charges Quantity * Price per (Rate) + Shipping + Tax
4. Expense Quantity (miles) * Rate

Background:
I am working on an invoice report. The report has the main report and a
sub-report. The report is grouped by invoice / work order number. I have a
group heading called WOHeader and a group footing called WOFooter.

The main report’s row source is the tblWorkOrder table. The sub-report’s
row source is tblWorkOrderDet.

The sub-report contains the following fields:
txtTransDate, txtWorkDesc (which is built using multiple fields),
txtQuantity, txtRate, txtTaxAmt, txtShipping, and txtTotAmtDue (=txtQuantity
* txtRate). I want to total the tax amt and shipping and show them on the
bottom of the invoice. They are currently hidden fields.

I understand that I need to have hidden “Running Sum” text boxes on the
group heading for the three amounts (total due, tax, shipping). The visible
boxes for these amounts are in the group footing.

I’ve read the forum discussion entitled Referencing sub-report fields on
main report:

http://www.microsoft.com/communities...d-01389ddddec1


Question:
I trying following the instructions in that discussion to calculate the
following totals in WOHeader:
1. Total amount due (Quantity * rate).
2. Total Shipping
3. Total Tax.

Let’s just talk about the first field because once I get that one working, I
can get the other fields.

In the WOHeader, I have a text box called txtWOHAmtDue. It’s control source
is:
=rptInvoice_srDet.Report!txtAmtDue
And it’s Running Sum property is set to Over group. For debugging, this box
is visible.

When I run my test, I have an invoice with three labor transactions. The
Total Amt due for each transaction is:

1. $45.00
2. $11.25
3. $ 0.00

The total in my txtWOHAmtDue is $45, not the expected 55.25. It appears to
only be including the total from the first deail.


A runningsum is unknown until the end of the group so the
header only sees the first line of the running sum. That
was the end of the discussion in earlier(?) versions of
Access, In later versions the group header can refer to a
total in the group footer. So, first put the total you want
in the group footer and then the group header can refer to
that total.

--
Marsh
MVP [MS Access]

Marshall,

Your comment: So, first put the total you want in the group footer and then
the group header can refer to that total.


My response: Ok, I tried that and it did not work

my subreport name is rptInvoice_srDet
The name of the control on the sub-report is txtAmtDue.
Note: txtAmtDue source = (Quantity & Rate)


The control on the footer is: txtWOFLaborAmt
The source =rptInvoice_srDet.Report!txtAmtDue
Format = Currency
Decimal Places = 2
Running sum = Over Group.


The first line txtAmtDue control is 45.00
The second line txtAmtDue control is 11.25
The third line txtAmtDue control 0.00
When I run the invoice, the amount in txtWOFLaborAmt = 0

Any suggestions?

Dennis

Marshall,

I just need a total in the group footing. I just put it in the header
because that is what one discussion suggested.

I also tried putting a sum amount in the footer of the sumform. I could not
get that to work either.

Dennis

Dennis wrote:
I just need a total in the group footing. I just put it in the header
because that is what one discussion suggested.

I also tried putting a sum amount in the footer of the sumform. I could not
get that to work either.


You need a subreport report footer) text box (named
txtTotal) with an expression like:
=Sum(Quantity * Rate)

Then a main report rext box in the same section as the
subreport can use the expression:
=rptInvoice_srDet.Report!txtTotal
or, as I prefer:
=rptInvoice_srDet.Report.Report.txtTotal

--
Marsh
MVP [MS Access]

Marshall,

That finally worked!!!!!!!

I do have a couple of questions.

1. What is the difference between:
=rptInvoice_srDet.Report!txtTotal
or, as I prefer:
=rptInvoice_srDet.Report.Report.txtTotal

I see the second has Report.Report in it where the first one has just Report
once. What is the advantage of Report.Report?

2. Not that that part works, I want to have two totals.
The first is for Labor
The second is for Parts
In my grand total, I then add the two numbers together.

I can tell if the invoice line is a part if TransCat = "P". If the invoice
line is for labor, TransCat = "L".

I tried this in the subreport's footing section:
=IIf([TransCat]="P",Sum([Quantity]*[Price]),0)
and all I got was a zero. the amount shoud have been about $24.

Dennis

Dennis wrote:
That finally worked!!!!!!!

I do have a couple of questions.

1. What is the difference between:
=rptInvoice_srDet.Report!txtTotal
or, as I prefer:
=rptInvoice_srDet.Report.Report.txtTotal

Man, did I mess that up. It should have been:
=rptInvoice_srDet!txtTotal
Or, as I prefer:
=rptInvoice_srDet.Report.txtTotal

Access resolves the ! syntax at runtime by trying a couple
of possible meanings. Because txtTotal is not part of the
subreport control, Access has to dig around to try to
resolve the name as part of the subreport's controls
collection.

The .Report syntax can be resolved (and error checked) at
design time.


I see the second has Report.Report in it where the first one has just Report
once. What is the advantage of Report.Report?

None, it was a brain fault :-(
The point was the .Report instead of !


2. Not that that part works, I want to have two totals.
The first is for Labor
The second is for Parts
In my grand total, I then add the two numbers together.

I can tell if the invoice line is a part if TransCat = "P". If the invoice
line is for labor, TransCat = "L".

I tried this in the subreport's footing section:
=IIf([TransCat]="P",Sum([Quantity]*[Price]),0)


Try it this way:
=Sum(IIf([TransCat]="P", [Quantity]*[Price], 0))

--
Marsh
MVP [MS Access]

Marshall,

Thanks, I got me invoice "report" working just the way I wanted.

Your comment: Try it this way:
=Sum(IIf([TransCat]="P", [Quantity]*[Price], 0))


That worked great!!! Now that I see it, I feel kind of dumb. Oh well, but
I learned a lot.

Thanks so much for your assitance.

Dennis

Dennis wrote:
Thanks, I got me invoice "report" working just the way I wanted.

Your comment: Try it this way:
=Sum(IIf([TransCat]="P", [Quantity]*[Price], 0))

That worked great!!! Now that I see it, I feel kind of dumb. Oh well, but
I learned a lot.

Thanks so much for your assitance.

Your welcome.

Waxing philosophical ;-)
Don't feel bad about not knowing something. Ignorance
should be overcome, feeling "dumb" or frustrated just gets
in the way. It's so much more fun to learn new things than
to just sit there fuming.

--
Marsh
MVP [MS Access]

Marsh,

Thanks for the encouragement. It much more fun to learn new things. I'm
having so much "fun" climbing the Access learning cliff. :-)

Dennis