Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2003
    Posts
    2

    Unanswered: Calculations with a recordset

    Hello,

    Am pulling a recordset from MySQL with VBA using ADO 2.6 into Excel. I need to do some calculations with the data in the recordset and would rather not dump it into the spreadsheet to do them

    Essentially, i just need to calcualte the Mean and the number of entries for each column in the recordset.

    I have tried from several different angles so far; the best I have come up with is to use the rs.name value and loop through the column names, assigning the value of a AVG query in SQL to a new RS and saving that to the spreadsheet. It seems like this is going to create much more network traffic than is needed to get a simple calculation done, particularly since a couple of hundred folks will be using the spreadsheet.

    Is there a way to do this directly from a recordset, or is there another way to go about this? I haven't been able to find anything that addresses this in any of the postings here, so I guess it may be impossible by means other than brute force....

    Thanks for any suggestions / advice / direction.

    mathsci

  2. #2
    Join Date
    Jan 2004
    Posts
    184

    Re: Calculations with a recordset

    What about doing the caluculations on the server itself directly in your query?

    i.e. SELECT AVG(Column1), AVG(Column2) FROM Table1

    Also try this control:

    http://www.geocities.com/scirocco_ha/DataControl.htm

    Originally posted by mathsci
    Hello,

    Am pulling a recordset from MySQL with VBA using ADO 2.6 into Excel. I need to do some calculations with the data in the recordset and would rather not dump it into the spreadsheet to do them

    Essentially, i just need to calcualte the Mean and the number of entries for each column in the recordset.

    I have tried from several different angles so far; the best I have come up with is to use the rs.name value and loop through the column names, assigning the value of a AVG query in SQL to a new RS and saving that to the spreadsheet. It seems like this is going to create much more network traffic than is needed to get a simple calculation done, particularly since a couple of hundred folks will be using the spreadsheet.

    Is there a way to do this directly from a recordset, or is there another way to go about this? I haven't been able to find anything that addresses this in any of the postings here, so I guess it may be impossible by means other than brute force....

    Thanks for any suggestions / advice / direction.

    mathsci

Posting Permissions

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