SUM Query 2

[darrenhubbard]

Hi All,

A quick SUM question. This is a result of a query:

Date From To Type Speed Quantity1 Quantity2
3/1 LDN NY A V 10 1000
4/5 LDN FR C Q 5 10
1/1 LDN NY A V 2000 300

I need to write a query that would output the following from the above:

Date From To Type Speed Q1 Q2 Count
3/1 LDN NY A V 2010 1300 2
4/5 LDN FR C Q 5 10 1

ie the grouping by which to sum by is From, To, Type and Speed. Q1 and Q2 should be ths sums, Count should be the number of records added to get those sums and date should be the most recent date on the record.

My query keeps on producing erros about not including certain things in aggregate queries and it's driving me nuts!

Any help would be much appreciated!

Cheers,

Darren Hubbard

 

[mpastore]

select [from],[to],[type],[speed],sum(quantity1] as q1_sum,sum(quantity2] as q2_sum,count(*) as cnt
from [mytb]
group by [from],[to],[type],[speed]

That being said, do not use words such as "from" and "to" as field names (unless you were just providing an example). These words are reserved in most programming languages that use SQL and may confuse the compiler.

Mike Pastore

Hats off to (Roy) Harper

 

[jebry]

Hi Darren!

Your query should look like this:

Max(Date) As LatestDate, From, To, Type, Speed, Sum(Quantity1) As Q1, Sum(Quantity2) As Q2, Count(*) As NumberOfRecords From YourTableOrQuery Group By From, To, Type, Speed

BTW you shouldn't use field names like Date From To and Type since these are keywords in Access and could get Access confused.

hth


Jeff Bridgham
bridgham@purdue.edu

 

[darrenhubbard]

Cheers guys! That did the trick! Takes bloody ages to run, though (about 10,000 records and some of those numbers and REALLY BIG).

Thanks for all your help,

Darren