Assign a calculated value -- sql server programming

Aster99
4/21/2004 10:48:40 PM

I need help with the following sql statement:
select ct_off_time, least(2+(0.044117*CT_OFF_GEN),8)/count(*) as mydata2
calculate avg from B_NRatio
it returns:
ct_off_time mydata2
-------------------- --------------------------------
05-MAY-03 18:57:57.7 1.97044
24-JAN-03 11:46:07.8 8
16-AUG-03 09:58:27.9 5.30701
16-AUG-03 21:22:57.7 6.09582
10-DEC-02 16:46:37.6 8
11-AUG-03 07:37:09.6 6.18406
15-APR-03 22:05:55.7 1.95985
09-MAY-03 21:25:45.0 1.97574
24-JAN-03 03:59:27.7 8
25-SEP-02 11:34:25.6 1.99868
16-AUG-03 09:16:47.9 6.1095
18-AUG-02 21:34:39.2 6.42846
28-JUL-03 10:37:57.9 6.10288
16-AUG-03 10:57:57.8 6.07862
23-OCT-02 14:18:13.8 1.91265
16-JAN-03 03:39:21.0 8
--------------------------------
AVG 5.25773
The calculation does what I expect but now I need to assign the 5.25773
average like so:

ADO_rs.Source = "select least(2+(0.044117*CT_OFF_GEN),8)/count(*) as mydata2
calculate avg from B_NRatio
ADO_rs.Open
'this next line is the problem
ActiveCell.Offset(x, 5).Value = ADO_rs.Fields!(calculated avg) <--- how do I
get the calculated average into a field so I can put it in that cell?

Alan Howard
4/22/2004 3:52:00 PM

Execute your query in QA and you should see two resultsets - if this is the
case then once you've processed the first set of results in the caller you
should be able to pick up the average using the NextRecordset method of your
ADO_rs object.

I'm no SQL Server expert but shouldn't you be COMPUTEing the average??

[quoted text, click to view]

sql server programming : Assign a calculated value