Load SQL results into an array

[Tyork]

I would like to load the results of a sql query into an array. I am utilizing the following approach which is terribly inefficient:

 
    'Calculate number of placements events for selected Client
    strSQL = "SELECT COUNT(*) FROM dbo_VPlacements WHERE Customer = '" & Client & "'"
    Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
    NCount = rs.Fields(0)
 
    ReDim PlacementArray(1 To NCount)
 
    'Load placement numbers into array
 
    For xx = 1 To NCount
        strSQL = "SELECT Top 1 PlacementCount FROM (SELECT Top " & xx & " PlacementCount, PlacementDate FROM (SELECT PlacementCount, PlacementDate FROM dbo_VPlacements WHERE PlacementDate = #" & PriorDay & "# ORDER BY PlacementDate ASC)) ORDER BY PlacementDate"
        Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
        PlacementArray(xx) = rs.Fields("PlacementCount")
 
    Next xx

I get a syntax error when I just try to set the array equal to the selected recordset. Any ideas?

[ajetrumpet]

some of the technique there looks like another language.


but regardless, your query returns a long value as a count, not records.

what do you want out of it, exactly? actual results of one field? or a count of the records returned?

[Tyork]

There are 2 queries

1 query returns a count and the other query loop returns the individual records and places them into an array. This is part of a larger function which calculates the standard deviation of a dataset.

I will need the individual records as well as the count for this and other calcs I'm preforming in the same sub.

[Tyork]

What I really want it to say:

PlacementArray() = r.s.fields("PlacementCount")

but I get a syntax error.

[ajetrumpet]

so you want to put WHAT into an array?? one field, right? as in, one field, record by record, yes?? line by line?? if that's all, you use GETROWS(), not queries and recordsets.

in terms of standard dev, why don't you just use the built in function for that? isn't there one?

[Tyork]

To put it in a bit more context; the table that I'm retrieving the information from has every "placement" that we have received from hundreds of clients. Field Names:

ID
PlacementDate
Customer
PlacementCount
PlacementValue


I'd like to calculate the standard deviation of the number of accounts placed at the client level. Therefore getrows() would not work because I have to retrieve a specific subset of records based on specific criteria. (I've never used getrows() but from what I've read, it cannot do this. Correct me if I'm wrong though)

I've researched it and there is not a built in vba function to calculate the standard deviation of an array.

[Tyork]

Actually, DstDevP may work in the SQL. I'll have to try that.

[Tyork]

You were right this will work

SELECT stDevP(PlacementCount)
FROM dbo_VPlacements
WHERE customer = '[Customer]'

In terms of placing the fetched record set into an array however is there an easier way than I was using when criteria is involved?

[ajetrumpet]

yes. if your set is not already an object, open a recordset and fetch the rows, like:

dim rs as dao.recordset
set rs = currentdb.openrecordset("sql")

rs.movelast
rs.movefirst

arrayvariable = rs.getrows()