Division problem; not part of an aggregate function

[houde]

Hello to everyone,

I am trying to run a query in Access that should give me a table with the following columns:

Subcategory C_YTD Pre_YTD Change

The column "change" should show the percentages, i.e. C_YTD/Pre_YTD - 1

I am taking the data from a table "BY DEALER & SUBCAT", so consequently I am first taking the sum to arrive at total subcategory level (instead of by subcategory AND by dealer). Also, I specify in the table ATTACK 2 which subcategories I want to include in the final result.

So far so good. Now, when I run this sql code...

SELECT [ATTACK 2].SUBCATEGORY,

Sum([BY DEALER & SUBCAT].C_YTD) AS C_YTD,

Sum([BY DEALER & SUBCAT].PRE_YTD) AS PRE_YTD,

Sum([BY DEALER & SUBCAT].C_YTD)/(SELECT Sum([BY DEALER & SUBCAT].PRE_YTD) FROM [BY DEALER & SUBCAT])-1 AS CHANGE

FROM [ATTACK 2] LEFT JOIN [BY DEALER & SUBCAT] ON [ATTACK 2].SUBCATEGORY = [BY DEALER & SUBCAT].SUBCATEGORY

GROUP BY [ATTACK 2].SUBCATEGORY;

...then it is working, it gives me the correct totals for C_YTD and Pre_YTD, but it gives me wrong percentages.

When I change the sql code to...

SELECT [ATTACK 2].SUBCATEGORY,

Sum([BY DEALER & SUBCAT].C_YTD) AS C_YTD,

Sum([BY DEALER & SUBCAT].PRE_YTD) AS PRE_YTD,

Sum([BY DEALER & SUBCAT].C_YTD)/(SELECT Sum([BY DEALER & SUBCAT].PRE_YTD) FROM [BY DEALER & SUBCAT]-1) AS CHANGE

FROM [ATTACK 2] LEFT JOIN [BY DEALER & SUBCAT] ON [ATTACK 2].SUBCATEGORY = [BY DEALER & SUBCAT].SUBCATEGORY

GROUP BY [ATTACK 2].SUBCATEGORY;

...(I put the -1 within the brackets)then the query doesn't run and I get the following error message instead: "You tried to execute a query that does not include the specified expression 'Subcategory' as part of an aggregate function".

Can anybody help? I would very much appreciate it!!
Thank you! Fabian

 

[PHV]

And this ?
SELECT [ATTACK 2].SUBCATEGORY,
Sum([BY DEALER & SUBCAT].C_YTD) AS C_YTD,
Sum([BY DEALER & SUBCAT].PRE_YTD) AS PRE_YTD,
(Sum([BY DEALER & SUBCAT].C_YTD)/Sum([BY DEALER & SUBCAT].PRE_YTD))-1 AS CHANGE
FROM [ATTACK 2] LEFT JOIN [BY DEALER & SUBCAT] ON [ATTACK 2].SUBCATEGORY = [BY DEALER & SUBCAT].SUBCATEGORY
GROUP BY [ATTACK 2].SUBCATEGORY;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[houde]

Ey PH,
It is working! Thank you very much!! I thought that this second "select" looks a bit strange, but since I had it from an other Tek-Tip thread I left it in there... Anyway, it's working now. Thanks again! Fabian