SUM and GROUP BY

[circuz_phreak]

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???

 

[dcobau]

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

 

[Frivolous Sam]

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.

 

[circuz_phreak]

Thanks

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

 

[Frivolous Sam]

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.

 

[ICONINV]

I am having a similar problem. I have a database which has firm names, repname, city, state, territory and amount. I am trying to create a pivot table showing that sum of all the reps as well as the the firm, i.e. ABC company has 14 reps and at the same time, there were 42 entries for 14 reps which amounted to x amount.
However, design view does not allow sum and group by together and tried to change the coding for it to give me my results, it keeps coming back with syntax error. Any help you can provide would be greatly appreciated.