Error in Dsum

[confuse]

Hi To all!! I need again your help guyz... I have an error in my query which i name it Product Sold Query, it is compose of 3 tables named: Inventory Transactions Query, Order Details and products which the 2 tables are being related.

In Inventory Transactions Query fields are:
ProductID
Name
Unitsreceived
TotalReceived

Order Details

ProductID
Name
Quantity
Total


I use this formula to add all the value of product sold with the same productID

ProductSold: DSum("[Quantity]","Order Details","ProductID =" & [Inventory Transactions Query].ProductID)


But it give me error, that this is not part of aggregate function...

Please do help i got headache with this...

Thanksss

 

[raskew]

Hi -

Would you post your query SQL.

Bob

 

[confuse]

tnx for immediate reply bob....

attached here my sample database....

thanks again...

 

[raskew]

Try this:

SELECT
    Products.ProductID
  , Products.ProductName
  , Products.ProductDescription
  , DSum("[Quantity]","Order Details Petshop","ProductID =" & [Inventory Transactions Query].[ProductID]) AS ProductSold
  , [Inventory Transactions Query].UnitsReceived
  , [Order Details Petshop].Quantity
FROM
   (Products 
INNER JOIN
   [Inventory Transactions Query] 
ON
   Products.ProductID = [Inventory Transactions Query].ProductID) 
INNER JOIN
   [Order Details Petshop] 
ON
   Products.ProductID = [Order Details Petshop].ProductID
GROUP BY
   Products.ProductID
  , Products.ProductName
  , Products.ProductDescription
  , DSum("[Quantity]","Order Details Petshop","ProductID =" & [Inventory Transactions Query].[ProductID])
  , [Inventory Transactions Query].UnitsReceived
  , [Order Details Petshop].Quantity;

Bob