Getting a Report with a Single SQL String

[donwiss]

I have this macro, which produces exactly the result that I want. But I would think this could be retrieved in one SQL string.

Private Sub bnDatabaseNames_Click()
' creates global data variable
    Dim i As Integer
    Dim temp As Variant
    If OpenDB Then Exit Sub
' get list of unique names
    sql = "SELECT DISTINCT FldrName,Name FROM SecurityPrices ORDER BY FldrName,Name;"
    Set rs = db.OpenRecordset(sql)
    If Not rs.EOF Then
        data = FlipData(rs.GetRows(10000))
        ReDim Preserve data(UBound(data), 4)
' loop for our unique rows
        For i = 0 To UBound(data)
            sql = "SELECT FIRST(SettleDate),LAST(SettleDate),Count(*) FROM SecurityPrices WHERE FldrName='" & Replace(data(i, 0), "'", "''") & "' AND Name='" & Replace(data(i, 1), "'", "''") & "';"
            Set rs = db.OpenRecordset(sql)
            temp = rs.GetRows(10000)
            data(i, 2) = temp(0, 0)
            data(i, 3) = temp(1, 0)
            data(i, 4) = temp(2, 0)
        Next i
    End If
    CloseDB
End Sub

 

[Legacy 456155]

How are you getting consistent results using First and Last on an unordered result set? Did you mean Min and Max?

    sql = "SELECT FldrName, [Name], Min(SettleDate) AS MinOfSettleDate, Max(SettleDate) AS MaxOfSettleDate, Count(ID) AS CountOfId "
    sql = sql & "FROM SecurityPrices "
    sql = sql & "GROUP BY FldrName, [Name]"
    sql = sql & "ORDER BY FldrName, [Name]"

 

[donwiss]

Uh, you caught me. I have to confess the code was not thoroughly tested, as I am still a long ways from having a large set of data to test with. Thanks for pointing this out.

I'm getting a 3061. Too few parameters. Expected 1. There is no field named ID. The Primary key is FldrName, [Name], SettleDate combined. I haven't given any of my tables an ID field. Should I?

 

[Joe4]

sql = "SELECT FldrName, [Name], Min(SettleDate) AS MinOfSettleDate, Max(SettleDate) AS MaxOfSettleDate, Count(ID) AS CountOfId " sql = sql & "FROM SecurityPrices " sql = sql & "GROUP BY FldrName, [Name]" sql = sql & "ORDER BY FldrName, [Name]"

You will also need to include a space between the "GROUP BY" and "ORDER BY" lines of text. Otherwise, it will create one long entry and result in errors.
Just include a space at the end of the "GROUP BY" line, just as shown on the two rows above it.

 

[Legacy 456155]

I suppose you could add an autonumber field for the purposes of counting. Thanks Joe.

   sql = "SELECT FldrName, [Name], Min(SettleDate) AS MinOfSettleDate, Max(SettleDate) AS MaxOfSettleDate, Count(ID) AS CountOfId "
    sql = sql & "FROM SecurityPrices "
    sql = sql & "GROUP BY FldrName, [Name] " (added space here)
    sql = sql & "ORDER BY FldrName, [Name]"

EDIT. I don't think that will work. The counting the ID. I might be able to help you if you'll post some sample data or share your table. You are welcome to upload it here if you wish.

 

[donwiss]

I added an auto-number field named ID to the table. It is not indexed. I put in the description: "This field exists solely so SQL can count the number of records in the selection."

There is a similar report that also needs a count, so I thought useful enough to add it.

All seems to work. Thanks for your help with this.

 

[Legacy 456155]

Well, glad it worked. I didn't think that it would.

 

[donwiss]

I need to enhance my sorting. The ORDER BY example above is on two regular fields. I'd also like to sort on the function columns. Something like "ORDER BY MaxOfSettleDate DESC,[Name] ASC" gets a 3061 too few parameters.

 

[donwiss]

I poked around the web and figured it out. The example I found did not have the "AS MinOfSettleDate.." assignments in the string. They are not needed. And they ordered by the function. So this works:

SELECT [Name],Min(SettleDate),Max(SettleDate),Count(ID) FROM SecurityPrices GROUP BY [Name] ORDER BY Max(SettleDate),[Name];"