Hey, guys. I want to get the min,avg, and max values for several fields and
DISPLAY THEM ON AN ASP WEB PAGE. The default value for the fields is 0.00
and I don't want that calculated in with the average and the min. Here is
the current recordset:

set EmpConHCC = server.createobject("adodb.recordset")
EmpConHCC.open "SELECT min(EmpOnlyConHCC2003) as EmpOnlyConHCC2003min,
avg(EmpOnlyConHCC2003) as EmpOnlyConHCC2003avg, max(EmpOnlyConHCC2003) as
EmpOnlyConHCC2003max, min(EmpPlusSpouseConHCC2003) as
EmpPlusSpouseConHCC2003min, avg(EmpPlusSpouseConHCC2003) as
EmpPlusSpouseConHCC2003avg, max(EmpPlusSpouseConHCC2003) as
EmpPlusSpouseConHCC2003max, min(EmpFamilyConHCC2003) as
EmpFamilyConHCC2003min, avg(EmpFamilyConHCC2003) as EmpFamilyConHCC2003avg,
max(EmpFamilyConHCC2003) as EmpFamilyConHCC2003max, min(EmpOnlyConHCC2002)
as EmpOnlyConHCC2002min, avg(EmpOnlyConHCC2002) as EmpOnlyConHCC2002avg,
max(EmpOnlyConHCC2002) as EmpOnlyConHCC2002max FROM survey_information WHERE
SurveyDescID=" &request("SurveyDescID"),conn,3

Is there a way to keep the big, long recordset and specify not to collect
0.00 values for each, or do I have to separate them into six different
recordsets?

And perhaps I'm going about this a completely wrong way. If so, let me know!

Thanks in advance,

Ashley

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.456 / Virus Database: 256 - Release Date: 2/18/2003
 

If "null" were the default value instead 0.00 then MIN, MAX, and AVG would
automatically exclude those default values.

You could change them in the database or possibly do a sub-SELECT and change
ZEROes to NULLs.

Your field names are so long as to be confusing. You could save a lot of
time by copying the table to a new table and then changing the field names
to easy acronyms. For example:

    change field name:    EmpOnlyConHCC2003    to    eocH03
    change field name:    EmpPlusSpouseConHCC2003   to   epscH03

Then you can use a conditional in a sub-SELECT:

    SubString = "( "

    SubString = SubString & " SELECT "
    SubString = SubString & " if( eocH03<>0, eocH3, null ) as eoc, "
    SubString = SubString & " if( epscH03<>0, epsc, null ) as epsc "
    SubString = SubString & " FROM  SurveyInfo "

    SubString = SubString & " )"


    SqlString = " SELECT max(eoc) as EocMax FROM " & SubString


These are just some ideas. Does it help?


-- Zee



----- Original Message -----
From: "Young, Ashley" <Ashley.Young@c...>
To: "Access ASP" <access_asp@p...>
Sent: Friday, March 21, 2003 2:58 PM
Subject: [access_asp] Major Help Needed: Recordset


> Hey, guys. I want to get the min,avg, and max values for several fields
and
> DISPLAY THEM ON AN ASP WEB PAGE. The default value for the fields is 0.00
> and I don't want that calculated in with the average and the min. Here is
> the current recordset:
>
> set EmpConHCC = server.createobject("adodb.recordset")
> EmpConHCC.open "SELECT min(EmpOnlyConHCC2003) as EmpOnlyConHCC2003min,
> avg(EmpOnlyConHCC2003) as EmpOnlyConHCC2003avg, max(EmpOnlyConHCC2003) as
> EmpOnlyConHCC2003max, min(EmpPlusSpouseConHCC2003) as
> EmpPlusSpouseConHCC2003min, avg(EmpPlusSpouseConHCC2003) as
> EmpPlusSpouseConHCC2003avg, max(EmpPlusSpouseConHCC2003) as
> EmpPlusSpouseConHCC2003max, min(EmpFamilyConHCC2003) as
> EmpFamilyConHCC2003min, avg(EmpFamilyConHCC2003) as
EmpFamilyConHCC2003avg,
> max(EmpFamilyConHCC2003) as EmpFamilyConHCC2003max, min(EmpOnlyConHCC2002)
> as EmpOnlyConHCC2002min, avg(EmpOnlyConHCC2002) as EmpOnlyConHCC2002avg,
> max(EmpOnlyConHCC2002) as EmpOnlyConHCC2002max FROM survey_information
WHERE
> SurveyDescID=" &request("SurveyDescID"),conn,3
>
> Is there a way to keep the big, long recordset and specify not to collect
> 0.00 values for each, or do I have to separate them into six different
> recordsets?
>
> And perhaps I'm going about this a completely wrong way. If so, let me
know!
>
> Thanks in advance,
>
> Ashley
>
> ---
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.456 / Virus Database: 256 - Release Date: 2/18/2003
>
>
>