in a many to one relationship, how can i get a subtotal of values from the many side over to the related record on the one side, in a query

If, for example, you have one customer, with many orders, and you want to total the order amounts for the one customer, you would do this:

SELECT tblCustomer.CustomerName, Sum(tblOrder.OrderAmount) AS SumOfOrderAmount
FROM tblCustomer INNER JOIN tblOrder ON tblCustomer.CustomerID = tblOrder.CustomerID
GROUP BY tblCustomer.CustomerName;

If you were making this in the query designer, select your tables, make sure there is a relationship, the select your columns, then select View > Totals, and then in your value column, select Sum.

Does that help?


mmcdonal

YES, THAT'S PERFECT. THANKS.