Suming fields in queries

[Asif]

I have a query that has the fields; ID, DATE and QUANTITY all from
same table. What I want to do is to display the total for each ID when
I run the query i.e. what I have currently is

ID DATE QUANTITY
1 13/06/07 12
1 14/06/07 25
2 15/06/07 12
3 15/06/07 18
4 15/06/07 78

what I want to be displayed when the query runs is

ID DATE QUANTITY
1 13/06/07 57
2 15/06/07 12
3 15/06/07 18
4 15/06/07 78

hence ID 1 only appears once but with its total

I've tried grouping the Quantity field by "Sum" but no such luck

Any suggestions?

Thanks

 

[Jerry Whittle]

To make it easier for us to help you, show us the SQL of the query. Open the
query in design view.

Also ID 1 has two different dates. You are also probably grouping on the
DATE field. In cases like this, which date do you want to see: the earliest
or latest date or it doesn't matter? Is DATE an actual Date/Time data type?

 

[Sprinks]

Asif,

If you want a total for the quantity field, group by the ID and sum the
quantity. The Date field is meaningless because different records can have
different dates. Also be aware that "Date" is an Access reserved word.
Using reserved words as field names can cause unpredictable behavior.
Assuming a table name of Qtys, the SQL is:

SELECT Qtys.ID, Sum(Qtys.Quantity) AS SumOfQuantity
FROM Qtys
GROUP BY Qtys.ID;

Sprinks

 

[Asif]

Jerry,

As requested the SQL of the query

SELECT tbl_BIN.BINID, tbl_BINPROCESS.BINPROCESSDate,
tbl_BINPROCESS.BINPROCESSQuantity
FROM tbl_BIN INNER JOIN tbl_BINPROCESS ON tbl_BIN.BINID =
tbl_BINPROCESS.BINPROCESSBinID;

tbl_BIN holds the names of the Bin in use and tbl_BINPROCESS holds the
date and quantity just to avoid any confusion

Secondly the date filed that is displayed is an actual Date/Time data
type and I'm not to bothered which one appears earliest or latest,
infact I think date field is irrelivant in this instance so i think
its probably best if I remove it from the query but that still won't
help me in getting the totals is it?

Thanks

 

[Jerry Whittle]

Try this:

SELECT tbl_BINPROCESS.BINPROCESSBinID,
Sum(tbl_BINPROCESS.BINPROCESSQuantity)
FROM tbl_BINPROCESS
GROUP BY tbl_BINPROCESS.BINPROCESSBinID ;