Count and sum records

[Avick]

I am running an export Query but I need to count records and total there qty cell values. Its hard to explain what I mean so have a look below

This is what I have

Product QTY
Paper 1 20
Paper 1 14
Paper 2 5
Paper 3 3
Paper 3 7


This is what I would like to happen
Paper 1 34
Paper 2 5
Paper 3 10

The final results would be exported to an accounts package or an excel file.

I have tried everything but can’t seem to get this to work. To add to my problems, every time I view the query I have to enter a start date and an end date. This is ok as it is part of the query but very annoying when you are trying to run testing.

 

[IanH]

A simple group / count query should suffice

SELECT Product, SUM(QTY) AS SumQuantity
FROM UserTable
GROUP BY Product;

Regards

Ian

 

[Avick]

Thanks Ian
I will get back to you when I give it a try.

 

[Avick]

Hi Ian
Having a bit of a problem with the SQL as it already as a GROUP BY Field.
Here is the code

SELECT [Batch Qry].ba_id, [Batch Qry].ba_date, [Batch Qry].pap_computerRef, IIf([Batch Qry]!ba_actul_lenght>5999,1) AS [Reel 1 used], [Batch Qry].pap_computerRef2, IIf([ba_actul_lenght2]>5999,1) AS [Reel 2 used], [Batch Qry].cor_computerRef, Round(([Batch Qry]!pc_unit_descr*[Batch Qry]!ba_qty_produced/1000),2) AS [Cores Used], [box_computerRef]+0 AS [Box Ref], [Batch Qry]!ba_qty_produced+0 AS [Boxes used]
FROM [Batch Qry]
GROUP BY [Batch Qry].ba_id, [Batch Qry].ba_date, [Batch Qry].pap_computerRef, IIf([Batch Qry]!ba_actul_lenght>5999,1), [Batch Qry].pap_computerRef2, IIf([ba_actul_lenght2]>5999,1), [Batch Qry].cor_computerRef, Round(([Batch Qry]!pc_unit_descr*[Batch Qry]!ba_qty_produced/1000),2), [box_computerRef]+0, [Batch Qry]!ba_qty_produced+0, IIf([Batch Qry]!ba_actul_lenght>5999,1)
HAVING (((IIf([Batch Qry]![ba_actul_lenght]>5999,1))=1));


Fingers crossed