Suming fields in queries

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Asif - 14 Jun 2007 15:19 GMT

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

Reply to this Message

Jerry Whittle - 14 Jun 2007 15:51 GMT

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?

Signature

Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

> 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
[quoted text clipped - 22 lines]
>
> Thanks

Reply to this Message

Asif - 14 Jun 2007 16:14 GMT

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

Reply to this Message

Jerry Whittle - 14 Jun 2007 17:32 GMT

Try this:

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

Signature

Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

> Jerry,
>
[quoted text clipped - 15 lines]
>
> Thanks

Reply to this Message

Sprinks - 14 Jun 2007 15:56 GMT

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

> 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
[quoted text clipped - 22 lines]
>
> Thanks

Reply to this Message