Getting a Report with a Single SQL String

donwiss

Board Regular
Joined
Jul 5, 2020
Messages
63
Platform
  1. Windows
I have this macro, which produces exactly the result that I want. But I would think this could be retrieved in one SQL string.

VBA Code:
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
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
How are you getting consistent results using First and Last on an unordered result set? Did you mean Min and Max?

VBA Code:
    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]"
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
I suppose you could add an autonumber field for the purposes of counting. Thanks Joe.

VBA Code:
   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.
 
Last edited by a moderator:
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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:

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

Forum statistics

Threads
1,213,529
Messages
6,114,155
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top