Graham - 

	The Access syntax is pretty much identical to foxpro in this case
(following generated from the Access query designer):

SELECT Sum(Table1.qty) AS SumOfqty, Sum(Table1.value) AS SumOfvalue,
Table1.depot
FROM Table1
GROUP BY Table1.depot
ORDER BY Sum(Table1.value);

hth,

John

-----Original Message-----
From: gbrown@c... [mailto:gbrown@c...]
Sent: Tuesday, March 27, 2001 4:41 AM
To: sql language
Subject: [sql_language] Access - subtotals in a select


Hi all

I'm using access and visual foxpro for the backend to a web app we've 
written.

Using Visual Foxpro I can do a  
strsql="select depot,sum(qty),sum(value) from trans order by depot group 
by depot;"

This will give the total qty and values for each depot. Access errors with 
this, which I think is down to the group by clause.

I thought the syntax for access is 

strsql="select distinct depot,sum(qty),sum(value) from trans order by 
depot;"

but this only shows the last depot.

What have I got wrong?

tia 

Regards
Graham Brown