ADO SQL Query question

[cjmitton]

I have a very simple database that when someone does a search using my custom for it saves the following details to the database:
Username
Clientnumber
Date-time

On saving to the database the record gets a unique reference number.

The next time the access the list I want then to access the top 20 unique clientnumbers in the date/time order that they searched on them.

I've got it so that they pull back the top 20 unique record but struggling with getting them in Date order. My code is as follows:


Private Sub GetPrev()
Dim objMyConn As Object
Dim objMyCmd As Object
Dim objMyRecordset As Object
Dim Username As String
Username = Environ("UserName")
' Set the Variable with relevent code / data
Set objMyConn = New ADODB.Connection
Set objMyCmd = New ADODB.Command
Set objMyRecordset = New ADODB.Recordset
objMyConn.ConnectionString = "Driver=SQL Server;Server=SQL\SQL;Database=SQL;Trusted_Connection=Yes;"
objMyConn.Open
Set objMyCmd.ActiveConnection = objMyConn
objMyCmd.CommandText = "Select top 20 cs_cltnum from CSResults group by cs_cltnum order by max(cs_DT) where cs_user like '" & Username & "'"
objMyCmd.CommandType = adCmdText
objMyCmd.Execute
Set objMyRecordset.ActiveConnection = objMyConn
objMyRecordset.Open objMyCmd
objMyRecordset.MoveFirst
objMyRecordset.Sort "cs_DT Desc"
With Me.cmbcltnum
.Clear
Do
.AddItem objMyRecordset!cs_cltnum
objMyRecordset.MoveNext
Loop Until objMyRecordset.EOF
End With
objMyRecordset.Close
objMyConn.Close
Set objMyConn = Nothing
Set objMyCmd = Nothing
Set objMyRecordset = Nothing
End Sub


I know that the SQL statment is messed up at the moment as I've been trying lots of different statements from when I was looking on google. any thoughts please?

[Frosty]

Quick comment... I like to use ALL CAPS for my sql statements when using key words..
"SELECT top 20 cs_cltnum FROM CSResults GROUP BY cs_cltnum ORDER BY max(cs_DT) WHERE cs_user LIKE '" & Username & "'"

I'm not a SQL guru, but I feel like you can use something like
"ORDER BY cs_DT DESC"

I'm not sure what the "max(cs_DT)" is, but if you're getting everything else properly, and it's just the ORDER BY that is failing, maybe it's the type of connection that doesn't recognize that way of order results

[cjmitton]

I finally got this sorted out yesterday afternoon taking an alternative route!


objMyCmd.CommandText = "Select top 20 cs_cltnum, Max(cs_key) from CSResults where cs_user like '" & Username & "' Group by cs_cltnum Order by max(cs_key) Desc"

Instead of using the date field I used the unique key field to order the client numbers which seemed to work.

I used the Max SQL function to order the 'key' in the Select element then grouped on the client number to ensure I got unique set of numbers then did the final order by to force them to a descending order. If I did not put it on it would just go Assending order.

Thanks for the suggestion on using the capitals I'll look in to that and see how I get on with it.

[Frosty]

Well, one more quick comment then -- I think the reason it started working is because you can't ORDER BY on something which isn't part of your result... your updated sql statement has new stuff in the SELECT area (the stuff between SELECT and FROM)... which you then can use in the ORDER BY area -- that wasn't the case in your original query.

[cjmitton]

Thanks Frosty, fully understand (now) and as per my last line of my original post I said that the SQL was messed up due to the amount of attempts to get it right!

As I learned as I went along I found that as I was trying different things it got messed up so hence the posting to get a different view... and to clarify things which you help helped with and got me back on track.

Thanks

Colin

[gmaxey]

Creating SQL statements has never been something I've found easy to do. I was recently given a tip to create the querry in Access and then Home>View>SQL view. That gives you the statement you need to use:

Here is an example of a statement that I could have probably never devised on my own ;-)


SELECT [fldFirstName] & IIf(IsNull([fldMiddleInitial]),""," " & [fldMiddleInitial]) & " " & [fldLastName] AS fldName, tblPeople.*
FROM tblPeople
WHERE (((tblPeople.fldTitle)="Attorney"))
ORDER BY tblPeople.fldLastName, tblPeople.fldFirstName;

[Frosty]

I definitely used Access SQL View to help my understanding of how SQL queries are built, generally speaking. But you also get a lot of extra confusing stuff which doesn't help the understanding (i.e., all those extra parantheticals in the WHERE section).

In addition, I think you'll run into problems if you tried to use that actual string in VBA code, because of the differences between SQL wanting single quotes around parameters and Access SQL View building parameters with double-quotes (and brackets around field names, etc -- there are some quirks to the SQL view in Access which aren't really "pure" SQL.

I'd agree it can help in the learning, but you might be sent down a garden path at times -- very analogous to learning VBA via recorded macros.

[cjmitton]

Thanks for the tips, I've used excel's "Microsoft Query" to assist with building some of my SQL statement before but SQL statements is something I'm trying to teach myself to try and be a bit more effiecent with my data retrieval / sorting / totaling etc.

Thanks for your help and suggestions.