column names in a query

[Guest]

Hi All

I have a query which I wrote in MS Query and works fine - however the column
headings are horrible so I renamed them (using as etc) but now the formulae
that uses those columns wont wrok. For example I have two columns returned
based on a formula. If I change the SQL to read column 1 as A and column 2 as
B to make it more readable my third column which I would like to be ((A/B)/A)
wont work. Any clues if you follow this at all?

Heres some of the SQL

SELECT DH.ACCOUNT, CS.NAME, Sum(DH.CARDSHIP) AS 'A',
SUM((DH.CARDSHIP*PH.RETAIL)/1.175) AS 'B',
Sum(DH.CARDSHIP*(PH.WHOLESAL/PH.UNITCONT)) AS 'C', SUM(('B'-'C')/'B') AS
'MARGIN'

Its the margin one that doesnt work

Thanks

Reggiee

 

[Guest]

Try this --
SELECT DH.ACCOUNT, CS.NAME, Sum(DH.CARDSHIP) AS 'A',
SUM((DH.CARDSHIP*PH.RETAIL)/1.175) AS 'B',
Sum(DH.CARDSHIP*(PH.WHOLESAL/PH.UNITCONT)) AS 'C',
SUM((SUM((DH.CARDSHIP*PH.RETAIL)/1.175)-Sum(DH.CARDSHIP*(PH.WHOLESAL/PH.UNITCONT)))/SUM((DH.CARDSHIP*PH.RETAIL)/1.175)) AS
'MARGIN'

 

[David F. Cox]

The "AS" column names are applied after processing is finished, so you
cannot use them in the query.

 

[Guest]

Thanks Karl

I now get the message 'cannot perform an aggregate function on an expression
containing an aggregate or a subquery'

Any other ideas

Thanks

 

[Guest]

Karl - I took out the first sum and now all is well so many thanks. One more
on this - the figures now show things like 0.7686475 - how can i format this
to 2 decimal points ie 0.77

Thanks again

 

[Duane Hookom]

Format the number in your form or report.

 

[Guest]

Hi Duane

Well what Im doing is using a query to pull data from SQL sever direct into
Excel - obviously its easy to format it in Excel but if it was pulled in
already preformatted then so much the better

Reggiee

 

[Duane Hookom]

You might want to use the Round() function.