Calculate total amt due on an invoice.

D

Dennis

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/communitie...&p=1&tid=2928e41c-db3a-43c0-a03d-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
 
M

Marshall Barton

Dennis said:
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/communitie...&p=1&tid=2928e41c-db3a-43c0-a03d-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.
Click to expand...


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

Dennis

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
 
D

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
 
M

Marshall Barton

Dennis said:
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.
Click to expand...

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
 
D

Dennis

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
 
M

Marshall Barton

Dennis said:
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
Click to expand...

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?
Click to expand...

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)
Click to expand...


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

Dennis

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
 
M

Marshall Barton

Dennis said:
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.
Click to expand...

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

Dennis

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
 

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

total Amt Due, Taxes, Shipping, Expense and Total Amt Due on Invoi 2
Reset page number on invoice forms 0
Printing of sub-report skips to second page. 7
Invoice database / report question. 7
page header on second page of invoice. 3
Total is off by a couple of cents 4
Calculate beginning balance, but print details on report 3
Listing cases with no data as zero, and then calculating a total 2

Top