Hello all,

My primary order form lists line items with a price for the line item. I have a txt field in the foot of the form that totals all line items. I need a report that will derive the total of line items on the order and display it as the order total in a report for each order. Then I need to sum each order total which i know how to do already. I'm just having a hard time getting report to display the derived sum of all order line items. I'm sure this is easy. I'm just having a hard time finding it on google.


FORM
Item price
foo $50
bar $25

sum $75

REPORT
Order Total Order $
334 $75
335 $86

I think I'm close by my syntax might be off. It's printing the same total for all line items. Any ideas?

SELECT ws_dealer.dealer_name, ws_order.id, DSum("sale_price","ws_order_line","id =[id]") AS [Order Total]
FROM ws_order_line INNER JOIN (ws_order INNER JOIN (ws_authorized_contact INNER JOIN (ws_locations INNER JOIN ws_dealer ON ws_locations.dealer_id = ws_dealer.id) ON ws_authorized_contact.location_id = ws_locations.id) ON ws_order.ac_id = ws_authorized_contact.id) ON ws_order_line.order_id = ws_order.id;

I got it working so for those of you whom this may help:

I had to write a function (perhaps not. I would love to know another way.)

Public Function DsumWSOrderLine(id As Integer) As Double
Dim strInject As String
strInject = "order_id = " & id
DsumWSOrderLine = DSum("sale_price", "ws_order_line", strInject)
End Function

I put this in another module i had already made.

then I referred to it in the query like so:

SELECT ws_dealer.dealer_name, ws_order.id, ws_order.cleared_for_manuf, DsumWSOrderLine([ws_order.id]) AS [Order Amount]