Query Question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

My data table comes in like so (actual records is around 4000000)

Date Identifier Type Volume
20060302 611654 A 22
20060302 611654 A 22
20060302 611660 A 10
20060302 611660 B 10



Is it possible to have a query sum the Volume only if the Date, Identifier,
and Type are matching. For the example above, the result of the query would
be 44.

Thank you in advance.
 
carl said:
My data table comes in like so (actual records is around 4000000)

Date Identifier Type Volume
20060302 611654 A 22
20060302 611654 A 22
20060302 611660 A 10
20060302 611660 B 10



Is it possible to have a query sum the Volume only if the Date, Identifier,
and Type are matching. For the example above, the result of the query would
be 44.

Thank you in advance.
Click to expand...

Yup. You would use a totals query. If you group on Date, Identifier, and
Type, then it would "roll up" all those records into a single record,
with Volume being Summed.

Turn this on under View->Totals.

Your SQL would look something like this:
SELECT [Date], Identifier, Type, Sum(Volume) AS SumOfVolume
FROM tblYourTable
GROUP BY [Date], Identifier, Type;

I would also recommend not using "Date" as a field name, as its a
reserved word. Rename it to ContractDate or whatever. That's why I have
to include it in [] above.
 
Back
Top