How to 'Sum Order Totals' for Accts Rx Report ?

[Will]

Ok... I was advised here to not create a data field in the Orders Table to
hold the Order Total...
Fine... I created an unbound field on the Order Form and calculated the
total there... but I didn't/don't save the Total in a Table.

Now I want to do an Accounts Receivable Report for a Customer and need to
list each Order along with the Total Due, Total Paid and Balance Due, etc...
then sum all that up at the bottom of the report.

But, since I don't save the Order Total in a table I will have to calculate
it for each Order... in other words for each line item in the Accounts
Receivable Report I must sum up the items on that order... add shipping,
handling, tax, etc.

I did this on the Orders Form by calculating a 'Sub Total' on the Order
Items Form and putting that on the Orders Form...

But I don't want to show every line item for every order for the Accounts
Receivable Report.

So... how do you calculate the total for each order... and display that
'order total' it as a line item on a report? and then calculate the total of
all the totals...

Thanks for any help.

 

[Joseph Meehan]

Ok... I was advised here to not create a data field in the Orders
Table to hold the Order Total...
Fine... I created an unbound field on the Order Form and calculated
the total there... but I didn't/don't save the Total in a Table.

Now I want to do an Accounts Receivable Report for a Customer and
need to list each Order along with the Total Due, Total Paid and
Balance Due, etc... then sum all that up at the bottom of the report.

But, since I don't save the Order Total in a table I will have to
calculate it for each Order... in other words for each line item in
the Accounts Receivable Report I must sum up the items on that
order... add shipping, handling, tax, etc.

I did this on the Orders Form by calculating a 'Sub Total' on the
Order Items Form and putting that on the Orders Form...

But I don't want to show every line item for every order for the
Accounts Receivable Report.

So... how do you calculate the total for each order... and display
that 'order total' it as a line item on a report? and then calculate
the total of all the totals...

Thanks for any help.

There are a number of methods. I am going to suggest using a query.

 

[Will]

Hmmm... I guess I didn't ask the question in the right manor.

I would like for the Report to look something like...

===
Customer: Company ABC

Cust PO - Order Total
PO 123 $50
PO 876 $75
PO 343 $25
===

(And, I just need to display one line per order... I don't want to list each
item for the order)

AND... the underlying data is stored in 3 tables...

- the record for the order (in the Orders Table) has Shipping Cost,
Handling Cost, and Tax
- the collection of records in the Items Table for each Order have the item
Qty and Discount
- and the Products Table contains the List Price

1 - I have to calculate the 'Item Total' for each item.. [List
Price]*((100-[Discount])/100)*[Qty]
2 - I have to calculate the sum up the 'Item Totals' for each order to get
the 'Order Sub Total'
3 - I have to add Shipping, Handling & Tax to that to get the 'Order Total'

I can create a query with all of these data fields from the various
tables...

And, I can create an unbound control to do the calculation for number one
above...

But... I'm not sure how to do Two and Three above

Plus... my Report wants to show every individual line item... and I only
want to show one line per Order...

Hopefully the question is more clearly stated here and someone will be kind
enough to give me some help on this one... I need to have it done before
Monday morning.

thanks again for any help.

 

[Guest]

Hi Will,

You can have the best of both worlds, in one report, which will either print
the detailed view of each order or just the summary information that you are
presently seeking. Start by creating a grouped report that shows all of the
detail information for each order.

Then use a form, with a checkbox and label that reads "Show Detail". You
open the report via a command button on the form (or you open the form
modally from the Report's Open event procedure). Add the following VBA
procedure to your report:

Note: The procedure shown below is for the detail section, with the order
details. In this example, the name of this section is "Detail1". Your detail
section may have a different name:

Private Sub Detail1_Format(Cancel As Integer, FormatCount As Integer)
On Error GoTo ProcError

Cancel = Not ([Forms]![frmReportsSelection]![chkShowDetail].Value)

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume ExitProc
End Sub


If you know that you will always want to suppress the formatting of this
detail section, I suppose you could just hard code it. I've never tried that,
but I assume it would work just fine, ie.:

Private Sub Detail1_Format(Cancel As Integer, FormatCount As Integer)
On Error GoTo ProcError

Cancel = True

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume ExitProc
End Sub



Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

Hmmm... I guess I didn't ask the question in the right manor.

I would like for the Report to look something like...

===
Customer: Company ABC

Cust PO - Order Total
PO 123 $50
PO 876 $75
PO 343 $25
===

(And, I just need to display one line per order... I don't want to list each
item for the order)

AND... the underlying data is stored in 3 tables...

- the record for the order (in the Orders Table) has Shipping Cost,
Handling Cost, and Tax
- the collection of records in the Items Table for each Order have the item
Qty and Discount
- and the Products Table contains the List Price

1 - I have to calculate the 'Item Total' for each item.. [List
Price]*((100-[Discount])/100)*[Qty]
2 - I have to calculate the sum up the 'Item Totals' for each order to get
the 'Order Sub Total'
3 - I have to add Shipping, Handling & Tax to that to get the 'Order Total'

I can create a query with all of these data fields from the various
tables...

And, I can create an unbound control to do the calculation for number one
above...

But... I'm not sure how to do Two and Three above

Plus... my Report wants to show every individual line item... and I only
want to show one line per Order...

Hopefully the question is more clearly stated here and someone will be kind
enough to give me some help on this one... I need to have it done before
Monday morning.

thanks again for any help.

 

[Will]

Thanks Tom.

This is helpful.

Will

Hi Will,

You can have the best of both worlds, in one report, which will either
print
the detailed view of each order or just the summary information that you
are
presently seeking. Start by creating a grouped report that shows all of
the
detail information for each order.

Then use a form, with a checkbox and label that reads "Show Detail". You
open the report via a command button on the form (or you open the form
modally from the Report's Open event procedure). Add the following VBA
procedure to your report:

Note: The procedure shown below is for the detail section, with the order
details. In this example, the name of this section is "Detail1". Your
detail
section may have a different name:

Private Sub Detail1_Format(Cancel As Integer, FormatCount As Integer)
On Error GoTo ProcError

Cancel = Not ([Forms]![frmReportsSelection]![chkShowDetail].Value)

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume ExitProc
End Sub


If you know that you will always want to suppress the formatting of this
detail section, I suppose you could just hard code it. I've never tried
that,
but I assume it would work just fine, ie.:

Private Sub Detail1_Format(Cancel As Integer, FormatCount As Integer)
On Error GoTo ProcError

Cancel = True

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume ExitProc
End Sub



Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

Hmmm... I guess I didn't ask the question in the right manor.

I would like for the Report to look something like...

===
Customer: Company ABC

Cust PO - Order Total
PO 123 $50
PO 876 $75
PO 343 $25
===

(And, I just need to display one line per order... I don't want to list
each
item for the order)

AND... the underlying data is stored in 3 tables...

- the record for the order (in the Orders Table) has Shipping Cost,
Handling Cost, and Tax
- the collection of records in the Items Table for each Order have the
item
Qty and Discount
- and the Products Table contains the List Price

1 - I have to calculate the 'Item Total' for each item.. [List
Price]*((100-[Discount])/100)*[Qty]
2 - I have to calculate the sum up the 'Item Totals' for each order to get
the 'Order Sub Total'
3 - I have to add Shipping, Handling & Tax to that to get the 'Order
Total'

I can create a query with all of these data fields from the various
tables...

And, I can create an unbound control to do the calculation for number one
above...

But... I'm not sure how to do Two and Three above

Plus... my Report wants to show every individual line item... and I only
want to show one line per Order...

Hopefully the question is more clearly stated here and someone will be
kind
enough to give me some help on this one... I need to have it done before
Monday morning.

thanks again for any help.

 

[Guest]

Hi Will,

You're welcome.

I forgot to mention this, but you need to leave the form with the checkbox
open (you can reopen it in hidden mode, after clicking on an OK button on the
form to open the report) so that the report can read the value of the
checkbox (0=false or -1=true). Otherwise, the line of code that reads:

Cancel = Not ([Forms]![frmReportsSelection]![chkShowDetail].Value)

will fail if the form has been closed. The sample I volunteered includes
this note in the Powerpoint presentation.

Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

Thanks Tom.

This is helpful.

Will