Getting a Report with a Single SQL String

donwiss

Board Regular
Joined
Jul 5, 2020
Messages
62
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
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
L

Legacy 456155

Guest
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]"
 

donwiss

Board Regular
Joined
Jul 5, 2020
Messages
62
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,939
Office Version
  1. 365
Platform
  1. Windows
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.
 
L

Legacy 456155

Guest

ADVERTISEMENT

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:

donwiss

Board Regular
Joined
Jul 5, 2020
Messages
62
Platform
  1. Windows
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.
 
L

Legacy 456155

Guest

ADVERTISEMENT

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

donwiss

Board Regular
Joined
Jul 5, 2020
Messages
62
Platform
  1. Windows
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

Board Regular
Joined
Jul 5, 2020
Messages
62
Platform
  1. Windows
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];"
 

Watch MrExcel Video

Forum statistics

Threads
1,114,614
Messages
5,549,012
Members
410,889
Latest member
laingwb
Top