I'm trying to create a Query. I have a field "Name" from the table "Products" and the field "Quantity" from the table "Deliveries". Currently, The same product name will appear several times with their quantities. I want to SUM all of those quantities together and only have the Product name appear once. If it's a diffrent product name, then sum all of it's quantities together etc. I'm having a tough time writing a proper formula, It either has syntax errors or produces more than one field, which I don't understand. I need a formula that I can insert into the design view of the query. This formula will also create a new data field to hold the information. Can anyone help?

p.s. Do I have to use SELECT for both Name and Quantity? Where do I insert GROUP BY? I'm so confused!! I've tried things like (SELECT Name, (SELECT SUM(Quantity) FROM Deliveries) FROM Products....by now where am I supposed to write GROUP BY???

You cannot not display the product name many times in the query. You can do so in a report but not in a query.

BTW, name is reserved term in Access. I suggest you change the title of this field to something else (eg. ProductName). Using name as a field name could create problems for you in a later stage of the project (such as when you need to write code using that field).

Dave

What you want to do is use the query to do the work, not a formula. You can do exactly what you want by using the Total row in the design query. Right click on any row in the query and in the options that come up, choose Total. You should then choose Sum for Quantity and Group By for Name. You will also have to have a relationship between the two tables, which you may already have (it is two linked columns, e.g. ProductID).

In the following example, which is the equivalent SQL for the query, I will assume you have a common field called ProductID in both tables (which you would normally have, so that you know which product had a quantity of it delivered):
SELECT Name, SUM(Quantity)
FROM Products INNER JOIN Deliveries ON Products.ProductID = Deliveries.ProductID
GROUP BY Name

If you want the list to show all products whether or not they have had deliveries, you could use LEFT JOIN instead of INNER JOIN. You can also do this in the query design view by double clicking on the line connecting the two columns.

I was so bent up on writing the code for it I didn't realise it was so easy. I clicked on the row and summed quantity and grouped by product names. The new column I get is SumOfQuantity. Is there any way to change the name of this column? It doesn't appear in the design view and I can't double click on it to change it. Thanks for all your help tho

If you switch to SQL view you can do
SELECT Name, SUM(Quantity) As YourName
FROM Products INNER JOIN Deliveries ON Products.ProductID = Deliveries.ProductID
GROUP BY Name

If I remember correctly, you can rename columns from the datasheet view.

I think in query design view you can specify your preferred name by doing
YourName : [Quantity]
in the selected field box, but the name and column reference might be the wrong way around.