Access Append Query Help

[deevaetodin]

I have data that is imported as new table in excel. Within the data are products, the customer and the quantity ordered.

example of data:

John Smith Product1 12
Jane Smith Product1 3
Howard Smith Product2 15
Fred Smith Product3 17
Matt Smith Product2 24

How can I write the query so that it will sum all quantities for product1, product2, product3 into a new table. Giving me the resulting table:

Product1 15
Product2 39
Productw 17

Thanks in advance for all help.

 

[sgreenwood]

What you want to accomplish can be done easily using the Access query wizazd.

Go to the query tab and select the "new" button.

Next select the "Simple Query Wizard" button and click ok.

On the next screen select the table you wish to work with and the fields you wish to include (in your case you want to select the "product" and "quantity" fields. Then select next.

On the next screen choose the "summary" radio button the select "summary options".

Select the radio button "sum" on the quantity field, click "ok" then click "finish" and you will be presented with the results of the query.

Product Sum Of Data
Product1 15
Product2 39
Product3 17

I hope that helps!

Steve

 

[JVFriederick]

Assume :
A B C
John Smith Product1 12
etc.

In cell's B10,B11,B12 enter
Product1, Product2, Product3

Now in cell C10, enter the equation
=SUM((B$1:B$5=B10)*C$1:C$5)
Press Ctrl+Shift+Enter to enter the formula as an array.
The result should now look like this
{=SUM((B$1:B$5=B10)*C$1:C$5)}
Copy the formula to cell C11 and C12.

Explaination :
B$1:B$5 is where to find the product. Match this to cell B10.
C$1:C$5 is where to find the quantity. For every record from the B column that matches, sum the values in column C.

For a more detailed explaination, see FAQ titled Summarizing Data with a single equation.

You can travel the Access query route but one equation may be all you need.