I have the following statement in an ASP program:



   SELECT Sum(Batters[AB]) As SumAB, SUM (Batters[H]) As SumH, ([SumH]/

[SumAB]) As Avg;



Is there a way I can add an "Order BY" to this to sort these stats in 

ascending order by the Avg column.  I have tried doing this in Access 

alone and it asks for a value for Avg. When I tried to add "Order By Avg" 

to an ASP program, it gave me an error.  



Chris

Did you try ORDER BY [SumH/SumAD]



(also, can't you build the necessary SQL in the Access Query Builder?)



Cheers

Ken



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

From: "Christopher Cote" <chrscote@9...>

Subject: [access_asp] Ordering an SQL statement by results of Expression





: I have the following statement in an ASP program:

:

:    SELECT Sum(Batters[AB]) As SumAB, SUM (Batters[H]) As SumH, ([SumH]/

: [SumAB]) As Avg;

:

: Is there a way I can add an "Order BY" to this to sort these stats in

: ascending order by the Avg column.  I have tried doing this in Access

: alone and it asks for a value for Avg. When I tried to add "Order By Avg"

: to an ASP program, it gave me an error.



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Also



SELECT Sum(Batters[AB]) As SumAB, SUM (Batters[H]) As SumH, ([SumH]/

[SumAB]) As Avg

FROM A_Table

ORDER BY 3 asc;



The 3 stands for the 3rd column in the resultset.

works just fine...



Forgot this in my previous answer. Sorry about that!



Regards,



Serge



----- Original Message -----

From: "Christopher Cote" <chrscote@9...>

To: "Access ASP" <access_asp@p...>

Sent: Tuesday, November 06, 2001 1:02 AM

Subject: [access_asp] Ordering an SQL statement by results of Expression





> I have the following statement in an ASP program:

>

>    SELECT Sum(Batters[AB]) As SumAB, SUM (Batters[H]) As SumH, ([SumH]/

> [SumAB]) As Avg;

>

> Is there a way I can add an "Order BY" to this to sort these stats in

> ascending order by the Avg column.  I have tried doing this in Access

> alone and it asks for a value for Avg. When I tried to add "Order By Avg"

> to an ASP program, it gave me an error.

>

> Chris




>

Chris,



Let your ORDER BY clause look like this:

ORDER BY ([SumH]/SumAB])



Hope this helps,



Regards,



Serge

----- Original Message -----

From: "Christopher Cote" <chrscote@9...>

To: "Access ASP" <access_asp@p...>

Sent: Tuesday, November 06, 2001 1:02 AM

Subject: [access_asp] Ordering an SQL statement by results of Expression





> I have the following statement in an ASP program:

>

>    SELECT Sum(Batters[AB]) As SumAB, SUM (Batters[H]) As SumH, ([SumH]/

> [SumAB]) As Avg;

>

> Is there a way I can add an "Order BY" to this to sort these stats in

> ascending order by the Avg column.  I have tried doing this in Access

> alone and it asks for a value for Avg. When I tried to add "Order By Avg"

> to an ASP program, it gave me an error.

>

> Chris




>

Hi Guys,

  I've actually tried the ORDER BY ([SumH]/[SumAB]) in Access using the 

Query Builder, but when I clicked on the execute button, it asked for 

input from me.  I'm not exactly sure what these inputs were for, but it 

would ask for an input for SumH and SumAB.  I did, however, try the first 

way the Serge told me which was to use the number column to sort by.  

Again, I would have never thought of this, but it actually worked.  I just 

don't understand why just doing a normal SORT BY avg wouldn't work, but 

anyway, thank you very much.  It was a great help.



Chris



> Chris,

> 

> Let your ORDER BY clause look like this:

> ORDER BY ([SumH]/SumAB])

> 

> Hope this helps,

> 

> Regards,

> 

> Serge

> ----- Original Message -----

> From: "Christopher Cote" <chrscote@9...>

> To: "Access ASP" <access_asp@p...>

> Sent: Tuesday, November 06, 2001 1:02 AM

> Subject: [access_asp] Ordering an SQL statement by results of Expression

> 

> 

> > I have the following statement in an ASP program:

> >

> >    SELECT Sum(Batters[AB]) As SumAB, SUM (Batters[H]) As SumH, ([SumH]/

> > [SumAB]) As Avg;

> >

> > Is there a way I can add an "Order BY" to this to sort these stats in

> > ascending order by the Avg column.  I have tried doing this in Access

> > alone and it asks for a value for Avg. When I tried to add "Order By 

Avg"

> > to an ASP program, it gave me an error.

> >

> > Chris




> >

> 

Try:



ORDER BY SUM(Batters[AB])/SUM(Batters[H])



(I assume Batters is some user defined function you have?)



Cheers

Ken



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

From: "Christopher Cote" <chrscote@9...>

Subject: [access_asp] Re: Ordering an SQL statement by results of Expression





: Hi Guys,

:   I've actually tried the ORDER BY ([SumH]/[SumAB]) in Access using the

: Query Builder, but when I clicked on the execute button, it asked for

: input from me.  I'm not exactly sure what these inputs were for, but it

: would ask for an input for SumH and SumAB.  I did, however, try the first

: way the Serge told me which was to use the number column to sort by.

: Again, I would have never thought of this, but it actually worked.  I just

: don't understand why just doing a normal SORT BY avg wouldn't work, but

: anyway, thank you very much.  It was a great help.

:

: Chris

:

: > Chris,

: >

: > Let your ORDER BY clause look like this:

: > ORDER BY ([SumH]/SumAB])

: >

: > Hope this helps,

: >

: > Regards,

: >

: > Serge

: > ----- Original Message -----

: > From: "Christopher Cote" <chrscote@9...>

: > To: "Access ASP" <access_asp@p...>

: > Sent: Tuesday, November 06, 2001 1:02 AM

: > Subject: [access_asp] Ordering an SQL statement by results of Expression

: >

: >

: > > I have the following statement in an ASP program:

: > >

: > >    SELECT Sum(Batters[AB]) As SumAB, SUM (Batters[H]) As SumH,

([SumH]/

: > > [SumAB]) As Avg;

: > >

: > > Is there a way I can add an "Order BY" to this to sort these stats in

: > > ascending order by the Avg column.  I have tried doing this in Access

: > > alone and it asks for a value for Avg. When I tried to add "Order By

: Avg"

: > > to an ASP program, it gave me an error.