CODEWITHSUNDEEP
sqlms-accessvba
evanburen
evanburen

Reputation: 309

Using ORDER BY With UNION

I'm using Access to create a SQL string in a VBA form. My goal is to return a random set of records with different "Duration of Call" ranges. I'm getting the error message "the ORDER BY expression ORDER BY Rnd(-(100000*ID)*Time()) includes fields that are not selected by the query. Only those fields requested in the first query can be included in an ORDER BY expression." I have ID in the first SELECT statement so I'm not sure what I'm doing wrong. 

strSQL = "SELECT TOP " & Me.txtSampleSize & ""
strSQL = strSQL & " [Workgroup],[Last Name],[First Name],[Titanium Number],"
strSQL = strSQL & "[Phone Number], [Inbound-Outbound], [Date of the Call], 
[Time of the Call], "
strSQL = strSQL & "[Duration of the Call], ID "
strSQL = strSQL & "FROM PCA_Calls WHERE "
strSQL = strSQL & "([Date of the Call] >= " & Format(Me.txtStartDate, 
conJetDate) & ") AND "
strSQL = strSQL & "([Date of the Call] <= " & Format(Me.txtEndDate, 
conJetDate) & ") AND "
strSQL = strSQL & "([Duration of the Call] >= 420) AND ([Duration of the 
Call] <=900) AND "
strSQL = strSQL & "([Workgroup] = """ & Me.workgroup & """) "        

strSQL = strSQL & "UNION "
strSQL = strSQL & "SELECT TOP " & Me.txtSampleSize & ""
strSQL = strSQL & " [Workgroup],[Last Name],[First Name],[Titanium Number],"
strSQL = strSQL & "[Phone Number], [Inbound-Outbound], [Date of the Call], 
[Time of the Call], "
strSQL = strSQL & "[Duration of the Call],ID "
strSQL = strSQL & "FROM PCA_Calls WHERE "
strSQL = strSQL & "([Date of the Call] >= " & Format(Me.txtStartDate, 
conJetDate) & ") AND "
strSQL = strSQL & "([Date of the Call] <= " & Format(Me.txtEndDate, 
conJetDate) & ") AND "
strSQL = strSQL & "([Duration of the Call] >= 901) AND ([Duration of the 
Call] <=1800) AND "
strSQL = strSQL & "([Workgroup] = """ & Me.workgroup & """)"    
strSQL = strSQL & " ORDER BY Rnd(-(100000*ID)*Time());"

Upvotes: 0

Views: 77

Answers (2)

DanielG
DanielG

Reputation: 1675

Yes, Access is a little more picky than SQL Server in things like this. One approach that I know would work would be to break this into multiple steps. Knowing Access, it will actually be faster.

Create a table that holds your fields ([Workgroup],[Last Name],[First Name],[Titanium Number], etc..). In the beginning of your code, delete all rows fro mthe table. You can do this by creating fixed queries in the DB container, and in VBA doing something like DoCmd.OpenQuery "qryDeleteTempTable". Then have an APPEND query to insert the first part of what you currently have as a UNION (DoCmd.OpenQuery "qryAddLessThan900Records"). Then another for the next step. At that point, you will have a table with all of your data. You can run a simple SELECT at that stage.

Not pretty, but it may be faster. :-)

Good luck!!

Upvotes: 0

JNevill
JNevill

Reputation: 50019

Just pop the whole mess into a subquery and do your order by on the result set of your union:

strSQL = "SELECT * FROM ( "
strSQL = strSQL & "SELECT TOP " & Me.txtSampleSize & ""
strSQL = strSQL & " [Workgroup],[Last Name],[First Name],[Titanium Number],"
strSQL = strSQL & "[Phone Number], [Inbound-Outbound], [Date of the Call], 
[Time of the Call], "
strSQL = strSQL & "[Duration of the Call], ID "
strSQL = strSQL & "FROM PCA_Calls WHERE "
strSQL = strSQL & "([Date of the Call] >= " & Format(Me.txtStartDate, 
conJetDate) & ") AND "
strSQL = strSQL & "([Date of the Call] <= " & Format(Me.txtEndDate, 
conJetDate) & ") AND "
strSQL = strSQL & "([Duration of the Call] >= 420) AND ([Duration of the 
Call] <=900) AND "
strSQL = strSQL & "([Workgroup] = """ & Me.workgroup & """) "        

strSQL = strSQL & "UNION "
strSQL = strSQL & "SELECT TOP " & Me.txtSampleSize & ""
strSQL = strSQL & " [Workgroup],[Last Name],[First Name],[Titanium Number],"
strSQL = strSQL & "[Phone Number], [Inbound-Outbound], [Date of the Call], 
[Time of the Call], "
strSQL = strSQL & "[Duration of the Call],ID "
strSQL = strSQL & "FROM PCA_Calls WHERE "
strSQL = strSQL & "([Date of the Call] >= " & Format(Me.txtStartDate, 
conJetDate) & ") AND "
strSQL = strSQL & "([Date of the Call] <= " & Format(Me.txtEndDate, 
conJetDate) & ") AND "
strSQL = strSQL & "([Duration of the Call] >= 901) AND ([Duration of the 
Call] <=1800) AND "
strSQL = strSQL & "([Workgroup] = """ & Me.workgroup & """)"    
strSQL = strSQL & ") as sub ORDER BY Rnd(-(100000*ID)*Time());"

Upvotes: 2

Related Questions