aggregate with an inner join

[Kim]

I am trying to create a function that will return the sum of shipping fees
per items in an order.
I want to pass the orderID to the function and retrieve the total of the
shipping fees for this order.


I have two tables.

orderdetail
---------------------------------------------------------------------
orderdetailID autonumber
orderID long
productID long
quantity integer


product
---------------------------------------------------------------------
productID long
shipfee currency

they are joined on productID


I have created a query

SELECT [quantity]*[shipfee] AS total
FROM OrderDetail INNER JOIN Product ON OrderDetail.ProductID =
Product.ProductID;

and am trying to retrieve the value using the following function


Public Function getShipping(OrderID)
getShipping = DSum("[total]", "qryGetShipFeePerOrder", "[orderID]=" &
OrderID)
End Function


But the problem is I get the entire total all of all orders - I only want
the total for the orderID I pass.

Can Anyone help me?

Thanks in advance.

 

[Allen Browne]

Kim, you can use a calculated field in DSum(), like this:
=DSum("[quantity] * [shipfee]", "OrderDetail", "[orderID = " & Nz([orderID],
0))

(The problem with your existing function is that the query has no OrderID
field, so the Criteria of the DSum() expression is not applied.)

 

[Kim]

Allen,
thank you - I added the orderID to the query and it worked.
That seems to easy.

Thank you for your help.
Kim

Kim, you can use a calculated field in DSum(), like this:
=DSum("[quantity] * [shipfee]", "OrderDetail", "[orderID = " & Nz([orderID],
0))

(The problem with your existing function is that the query has no OrderID
field, so the Criteria of the DSum() expression is not applied.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I am trying to create a function that will return the sum of shipping fees
per items in an order.
I want to pass the orderID to the function and retrieve the total of the
shipping fees for this order.


I have two tables.

orderdetail
---------------------------------------------------------------------
orderdetailID autonumber
orderID long
productID long
quantity integer


product
---------------------------------------------------------------------
productID long
shipfee currency

they are joined on productID


I have created a query

SELECT [quantity]*[shipfee] AS total
FROM OrderDetail INNER JOIN Product ON OrderDetail.ProductID =
Product.ProductID;

and am trying to retrieve the value using the following function


Public Function getShipping(OrderID)
getShipping = DSum("[total]", "qryGetShipFeePerOrder", "[orderID]=" &
OrderID)
End Function


But the problem is I get the entire total all of all orders - I only want
the total for the orderID I pass.

Can Anyone help me?

Thanks in advance.