Query for columns that have any data at all

Results 1 to 2 of 2

Thread: Query for columns that have any data at all

  1. #1
    Join Date
    Dec 1969

    Default Query for columns that have any data at all

    Hi, I have a data table consisting of columns corresponding to different sites and data types (eg datatype1, datatype2, datatype3 ....). Sites have not necessarily been monitored for every data type, so I have a lot of empty cells.<BR><BR>I need to develop a query where I can sort though this table by site, and provide the user with all of the data types that have been collected there. (eg, "Datatype2, datatype6 and datatype8 are available for site A").<BR><BR>I don&#039;t have a problem looping through the table and getting the raw data, but I don&#039;t know how to do this. Do I need a new table to do this?

  2. #2
    Join Date
    Dec 1969

    Default I'm not clear on ...

    ...what exactly you want, but is it as simple as this:<BR><BR>&#060;%<BR>SQL = ...<BR>Set RS = conn.Execute<BR><BR>Do Until RS.EOF<BR> avail = ""<BR> count = 0<BR> For dnum = 1 To 10 &#039; or however many you have <BR> dt = RS("datatype" & dnum)<BR> If Trim(dt) &#060;&#062; "" Then<BR> count = count + 1<BR> If avail &#060;&#062; "" Then <BR> avail = avail & ", datatype" & dnum<BR> Else <BR> avail = "Datatype" & dnum<BR> End If<BR> End If<BR> Next<BR> If count &#062; 0 Then<BR> If count = 1 Then avail = avail & " is" Else avail = avail & " are"<BR> Response.Write avail & " available for " & RS("site") & "&#060;br/&#062;"<BR> End If<BR> RS.MoveNext<BR>Loop<BR>%&#062;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts