billing statement

[David_Beginner via AccessMonster.com]

I am trying to create a billing statement with multiple invoices, credits,
reorders and payments.
I have successfully created a report that will summarize the totals for each
"ordertype"; invoice, credit, and reorder for each "shipment" and for each
customer using the grouping function.
All orders(inv/credit/reorder) are entered into the same table and are
distinguished by the field [ordertype]. And all orders correspond to a
shipment which is designated in tblproducts.[shipment#]. I figured this would
be the most efficient design. However, I am not able to perform any
calculations on the invoice level because all the data is displayed through a
single textbox control for each (sum of extended price). For example I cant
subtract the total credits from shipment1 from the invoice total of shipment1.


I believe that I could create separate tables for each ordertype, this would
allow for separate fields in the query and controls in the report. Then
create separate forms for each shipment and embed in a main form to achieve
the desired result, but it seems inefficient.

Is there a better way?
Perhaps there are tools I am unaware of for accomplishing this more
efficiently?

thank you, david

 

[Duane Hookom]

You stated "For example I cant subtract the total credits from shipment1 from
the invoice total of shipment1" but didn't provide much background on the
fields and sample values from your report. Apparently you have a field named
"OrderType" with values like "inv", "credit", and "reorder". If you want to
get an extended price for all "credit" records, your expression in a group or
report footer would be something like:
=Sum(abs([OrderType]="credit") * [extended price calculation])

 

[David_Beginner via AccessMonster.com]

Hi Duane,

Thank you for your reply and suggestion.
I tried your suggestion, but encountered a datatype mismatch error.
the expr looked like this
=Sum(Abs([orderdetails.OrderType]="3")*([qty]*[price]))

The ordertype is actually a number that corresponds to inv/credit/reorder.

The Table Fields are below:

tblClients: [ClientID], [Name]etc..
tblOrders: [ClientID], [OrderID]
tblOrderDetails: [OrderID], [ProductID], [Qty], [OrderType]
tblProducts: [ProductID], [Price], [Shipment#]

The report is grouped on OrderId, OrderType, Shipment

The Detail calculates the extended price for each product (not visible)

In the Shipment footer Sum(extended price calculations), displays all the
sums for all "ordertypes" for each shipment.

Basically, what I am trying to do is display Invoice1 total value in a text
box, next to that would be the total value of any credits, next to that the
total value of any reorders and then the value of the difference as amount
due

I hope that makes sense.

Thank you again,
david

You stated "For example I cant subtract the total credits from shipment1 from
the invoice total of shipment1" but didn't provide much background on the
fields and sample values from your report. Apparently you have a field named
"OrderType" with values like "inv", "credit", and "reorder". If you want to
get an extended price for all "credit" records, your expression in a group or
report footer would be something like:
=Sum(abs([OrderType]="credit") * [extended price calculation])

I am trying to create a billing statement with multiple invoices, credits,
reorders and payments.

[quoted text clipped - 19 lines]

thank you, david