Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2003
    Posts
    4

    Unanswered: Need help with recordset data

    I have a large database in access with one field having repeated values. I used a Select Distinct statement to fill a listbox with one of each value in that field. Now I want to select one of these values and get the average of the data from another field for all of the records with this value and put it into a textbox. It seems to get the data and take the average, but I don't know how t get the final number into a textbox. The code below is what I have been using.

    Private Sub Form_Load()

    Set ashcmd = New ADODB.Command
    ashcmd.ActiveConnection = ashconn
    Set ashrs = New ADODB.Recordset
    ashsources = "select distinct Source from Flyash"
    ashcmd.CommandText = ashsources
    ashrs.Open ashcmd

    'Filling source listboxes with Ash sources in database.
    lstash.Clear
    While (Not ashrs.EOF)
    lstash.AddItem ashrs("Source")
    ashrs.MoveNext
    Wend

    End Sub


    Private Sub lstash_Click()

    Set ashsbcmd = New ADODB.Command
    ashsbcmd.ActiveConnection = ashconn
    Set ashsbrs = New ADODB.Recordset
    ashsbrs.ActiveConnection = ashconn

    ashsource = lstash.List(lstash.ListIndex)
    meansb = "select Avg(Antimony) from Flyash where Source ='" + ashsource + "'"
    ashsbcmd.CommandText = meansb
    ashsbrs.Open ashsbcmd

    This is where I want to put the average of the data into a textbox.


    Any help would be greatly appreciated.

    Thanks,

    Michael

  2. #2
    Join Date
    Dec 2003
    Posts
    4

    Re: Need help with recordset data

    I changed the code to the following and have gotten it to work a little better.


    Private Sub lstash_Click()

    Set ashsbcmd = New ADODB.Command
    ashsbcmd.ActiveConnection = ashconn
    Set ashsbrs = New ADODB.Recordset
    ashsbrs.ActiveConnection = ashconn

    ashsource = lstash.List(lstash.ListIndex)
    meansb = "select * from Flyash where Source ='" + ashsource + "'"
    ashsbcmd.CommandText = meansb
    ashsbrs.Open ashsbcmd

    While (Not ashcontentrs.EOF)
    sb = ashcontentrs("Antimony")
    sumsb = sumsb + sb
    numsb = numsb + 1
    ashcontentrs.MoveNext
    Wend
    avgab = sumsb / numsb


    This gives me the average for all values in that field. Is there an easier way. Also, if a record in a given is blank, how do I tell it to skip that one and continue with the next?

    Thanks in advance for any help given

    Michael

Posting Permissions

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