MS Access sum of related records

Question

I have a purchase form that has a continuous subform which shows line items for that purchase. Each line item needs to include a text box that shows the sum for a number of related records in a table for that line item.

(Each line item is allocated for a specific purpose(s). For instance, a line item says that 100 widgets are ordered; the allocation table says that 20 widgets are for this purpose and 40 are for this purpose. The text box needs to say that 60 / 100 are allocated.)

I have written this in VBA for the OnCurrent event of the form, but this only occurs when the first "copy" of the continuous form receives focus. Then all the other "copies" of the form show the same values in the text box. I then realized that I could perhaps do this as part of the query for the form. Whenever I add ... Sum(Quantity) AS TotalAllocations to the SQL query, I get the following error:

"You tried to execute a query that does not include the specified expression 'VendorPartNumber' as part of an aggregate function."

(VendorPartNumber is a field for the records that show up on the continuous form.)

Can someone explain how to do this successfully in a query designer / SQL view or with VBA?

Complete aside: Ideally, it would be nice to have this as a continuous subform on a continuous subform. However, Access does not allow continous subforms on continuous subforms.

Answer 1

If you're going to SUM in SQL, you need to include a GROUP BY clause for the non-aggregated columns, such as:

SELECT VendorPartNumber, Sum(Quantity) as TotalAllocations
    FROM YourTable
    GROUP BY VendorPartNumber