query error

[lin qin]

when i execute following query, it displays error.

SELECT [mod], region, Sum(rev) AS srev, Sum(qty) AS sqty,
srev/sqty AS cru
FROM Table1
GROUP BY [mod];


the following query is correct:
SELECT [mod], region, Sum(rev) AS srev, Sum(qty) AS sqty,
srev/sqty AS cru
FROM Table1
GROUP BY [mod],region;

however, I don't understand I need to add"[]"in mod column,
and if i want to see the column region in the result but I
don't want to group by region,(only group by mod). how can
i modify the first query to let it run correctly.

 

[Dale Fye]

Lin,

You cannot use computed field names to do the calculations you want within a
single query. Instead, try,

SELECT T.*, T.srev/T.sqty as CRU
FROM
(
SELECT [Mod], Region, Sum(Rev) as srev, Sum(qty) as sqty
FROM Table1
GROUP BY [Mod], [Region]
) as T

The reason you are probably having trouble with your [MOD] field is that it
is an Access reserved word, so when Access sees it, it tries to perform the
mod() function.

If you don't want to group by the region, then viewing the region in the
result set doesn't make any sense. You could use First(Region),
Last(Region), Max(Region), Min(Region) to display a region, but it would
only display one, and the results would not actually reflect valid numbers.

HTH
Dale