[AccessD] SQL: Sum from the third table

SELECT Customer.CustID,
               Customer.Name,
               First(Order.ValAmount) AS UkVred,
               Sum(OrderDet.Quantity) AS ukkol
  FROM (Customer INNER JOIN [Order] ON Customer.CustID = Order.CustID)
             INNER JOIN OrderDet ON Order.RedBROJ = OrderDet.RedBROJ
  GROUP BY Customer.CustID, Customer.Name;

  Scott,
  I'm very sorry, but the result isn' appropriate for me.
  I want to list the customers from the Customer table. The column UkVred have to sum the field ValAmount from the Order table, and the last column have to sum the field Quantity for customers orders. 
  E.g.
  Customer(CustId, Name)
  1 Scott
  2 Ervin

  Order(REDBROJ, CustId, ValAmount)
  1 1 10
  2 1 15
  3 2 100
  OrderDet(REDBROJ, Quantity)
  1 10
  1 20
  2 30
  2 40
  2 50
  3 60
  3 70
  3 80
  And I want to get:
  1 Scott 25 150 
  2 Ervin 100 210
  But the First(Order.ValAmount)  give me:
  1 Scott 10(list the first ValAmount instead to sum) 150 
  2 Ervin 100 160 
  If I change the First function to Sum
   1 Scott 125(5*25) 150 
  2 Ervin 100 160 
  So, the quantity is ok, but the ValAmount isn't .
  Hope I don't want something impossible :)
  Maybe someone on the List have a good idea...
  Thanks,
      Ervin
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://databaseadvisors.com/pipermail/accessd/attachments/20030724/a63dfc77/attachment-0001.html>