Need Best SQL Statement GURU out there... others welcome!!!

  • Thread starter Thread starter Ken Snell
  • Start date Start date
K

Ken Snell

For starters, you are not putting spaces between the different words. Add a
space at the end of each text string on each code line.

For example,

"From REmployee " & _

Your words are running together and SQL/ACCESS do not know how to read the
statement.
 
Sorry, but I don't see any spaces *inside* the text string itself.

I copied your code that sets the text string and pasted it in the Immediate
window. Here is the resulting text string:

(SELECT [REmployee.EmployeeSSNumber, REmployee.FileId,REmployee.CompanyID,
REmployee.RecordType, REmployee.StateCode,REmployee.EmployeeFirstName,
REmployee.EmployeeLastName,REmployee.QuarterEnding]From REmployeeWHERE
(((REmployee.EmployeeSSNumber) In(SELECT [EmployeeSSNumber] FROM [REmployee]
As TmpGROUP BY [EmployeeSSNumber] HAVING Count(*)>1 ))AND
((REmployee.FileId)="Fl_u015"))ORDER BY REmployee.EmployeeSSNumber;


Note that you do not have spaces at these locations:
REmployee.QuarterEnding]From
REmployeeWHERE
TmpGROUP


--

Ken Snell
<MS ACCESS MVP>

Bob M. said:
Ken,

Thanks for the quich reply but there are spaces if you look carefully. This
string does work as the recordsource property for my adodc objet and
populates a datagrid with no problem. I cut and pasted the string which
works directly into this message.

Bob ..
^




Ken Snell said:
For starters, you are not putting spaces between the different words.
Click to expand...
Add
a
space at the end of each text string on each code line.

For example,

"From REmployee " & _

Your words are running together and SQL/ACCESS do not know how to read the
statement.
Click to expand...
"
&
DataBasePathAndFileName & ";Persist Security Info=False"
cnn1.ConnectionTimeout = 30
cnn1.Open

cmd = "(SELECT [REmployee.EmployeeSSNumber, REmployee.FileId," & _
"REmployee.CompanyID, REmployee.RecordType, REmployee.StateCode,"
Click to expand...
Click to expand...
&
_
"REmployee.EmployeeFirstName, REmployee.EmployeeLastName,
REmployee.QuarterEnding]" & _
"From REmployee" & _
"WHERE (((REmployee.EmployeeSSNumber) In" & _
"(SELECT [EmployeeSSNumber] FROM [REmployee] As Tmp" & _
"GROUP BY [EmployeeSSNumber] HAVING Count(*)>1 ))" & _
"AND ((REmployee.FileId)=""Fl_u015""))" & _
"ORDER BY REmployee.EmployeeSSNumber;"

Set rstEmployee = New ADODB.Recordset
rstEmployee.CursorType = adOpenKeyset
rstEmployee.LockType = adLockOptimistic
rstEmployee.Open cmd, cnn1, , adCmdText

Do While Not rstEmployee.EOF
Debug.Print rstEmployee!SSNumber
Loop

Close cnn1
Click to expand...
Click to expand...
Click to expand...
 
I also note that you're missing an ending ) in the last line.

The error message is saying that it thinks your query is a UNION query, when
in fact it's not. That is the tipoff that it seems to be misreading your
query's SQL.


--

Ken Snell
<MS ACCESS MVP>

Bob M. said:
Ken,

Thanks for the quich reply but there are spaces if you look carefully. This
string does work as the recordsource property for my adodc objet and
populates a datagrid with no problem. I cut and pasted the string which
works directly into this message.

Bob ..
^




Ken Snell said:
For starters, you are not putting spaces between the different words.
Click to expand...
Add
a
space at the end of each text string on each code line.

For example,

"From REmployee " & _

Your words are running together and SQL/ACCESS do not know how to read the
statement.
Click to expand...
"
&
DataBasePathAndFileName & ";Persist Security Info=False"
cnn1.ConnectionTimeout = 30
cnn1.Open

cmd = "(SELECT [REmployee.EmployeeSSNumber, REmployee.FileId," & _
"REmployee.CompanyID, REmployee.RecordType, REmployee.StateCode,"
Click to expand...
Click to expand...
&
_
"REmployee.EmployeeFirstName, REmployee.EmployeeLastName,
REmployee.QuarterEnding]" & _
"From REmployee" & _
"WHERE (((REmployee.EmployeeSSNumber) In" & _
"(SELECT [EmployeeSSNumber] FROM [REmployee] As Tmp" & _
"GROUP BY [EmployeeSSNumber] HAVING Count(*)>1 ))" & _
"AND ((REmployee.FileId)=""Fl_u015""))" & _
"ORDER BY REmployee.EmployeeSSNumber;"

Set rstEmployee = New ADODB.Recordset
rstEmployee.CursorType = adOpenKeyset
rstEmployee.LockType = adLockOptimistic
rstEmployee.Open cmd, cnn1, , adCmdText

Do While Not rstEmployee.EOF
Debug.Print rstEmployee!SSNumber
Loop

Close cnn1
Click to expand...
Click to expand...
Click to expand...
 
Your square brackets don't appear to be correct. Square brackets are used
with field names that are "improper" from Access's perspective, such as
embedded spaces or reserved words. You don't put them around a group of
field names, such as you're doing.
 
I get an error "Run-time error '2147467259 (80004005)':

Syntax error in union query."

Code is below... can anyone shed some light on this problem??????
Thanks in advance.... Bob M.
+_+_+_+_+_+_+_+_+_+_+_+_+_+_+_+_


Dim cnn1 As ADODB.Connection
Dim rstEmployee As ADODB.Recordset
Dim strcnn As String
Dim cmd As String

' Open a connection without using a Data Source Name (DSN).
' with this connection... multiple recordsets can be opened
Set cnn1 = New ADODB.Connection
cnn1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " &
DataBasePathAndFileName & ";Persist Security Info=False"
cnn1.ConnectionTimeout = 30
cnn1.Open

cmd = "(SELECT [REmployee.EmployeeSSNumber, REmployee.FileId," & _
"REmployee.CompanyID, REmployee.RecordType, REmployee.StateCode," & _
"REmployee.EmployeeFirstName, REmployee.EmployeeLastName,
REmployee.QuarterEnding]" & _
"From REmployee" & _
"WHERE (((REmployee.EmployeeSSNumber) In" & _
"(SELECT [EmployeeSSNumber] FROM [REmployee] As Tmp" & _
"GROUP BY [EmployeeSSNumber] HAVING Count(*)>1 ))" & _
"AND ((REmployee.FileId)=""Fl_u015""))" & _
"ORDER BY REmployee.EmployeeSSNumber;"

Set rstEmployee = New ADODB.Recordset
rstEmployee.CursorType = adOpenKeyset
rstEmployee.LockType = adLockOptimistic
rstEmployee.Open cmd, cnn1, , adCmdText

Do While Not rstEmployee.EOF
Debug.Print rstEmployee!SSNumber
Loop

Close cnn1
 
Thanks for the sharper eyes, Doug.

--

Ken Snell
<MS ACCESS MVP>

Douglas J. Steele said:
Your square brackets don't appear to be correct. Square brackets are used
with field names that are "improper" from Access's perspective, such as
embedded spaces or reserved words. You don't put them around a group of
field names, such as you're doing.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Bob M. said:
I get an error "Run-time error '2147467259 (80004005)':

Syntax error in union query."

Code is below... can anyone shed some light on this problem??????
Thanks in advance.... Bob M.
+_+_+_+_+_+_+_+_+_+_+_+_+_+_+_+_


Dim cnn1 As ADODB.Connection
Dim rstEmployee As ADODB.Recordset
Dim strcnn As String
Dim cmd As String

' Open a connection without using a Data Source Name (DSN).
' with this connection... multiple recordsets can be opened
Set cnn1 = New ADODB.Connection
cnn1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " &
DataBasePathAndFileName & ";Persist Security Info=False"
cnn1.ConnectionTimeout = 30
cnn1.Open

cmd = "(SELECT [REmployee.EmployeeSSNumber, REmployee.FileId," & _
"REmployee.CompanyID, REmployee.RecordType, REmployee.StateCode," & _
"REmployee.EmployeeFirstName, REmployee.EmployeeLastName,
REmployee.QuarterEnding]" & _
"From REmployee" & _
"WHERE (((REmployee.EmployeeSSNumber) In" & _
"(SELECT [EmployeeSSNumber] FROM [REmployee] As Tmp" & _
"GROUP BY [EmployeeSSNumber] HAVING Count(*)>1 ))" & _
"AND ((REmployee.FileId)=""Fl_u015""))" & _
"ORDER BY REmployee.EmployeeSSNumber;"

Set rstEmployee = New ADODB.Recordset
rstEmployee.CursorType = adOpenKeyset
rstEmployee.LockType = adLockOptimistic
rstEmployee.Open cmd, cnn1, , adCmdText

Do While Not rstEmployee.EOF
Debug.Print rstEmployee!SSNumber
Loop

Close cnn1
Click to expand...
Click to expand...
 
Ken,

Thanks for the quich reply but there are spaces if you look carefully. This
string does work as the recordsource property for my adodc objet and
populates a datagrid with no problem. I cut and pasted the string which
works directly into this message.

Bob ..
^




Ken Snell said:
For starters, you are not putting spaces between the different words. Add a
space at the end of each text string on each code line.

For example,

"From REmployee " & _

Your words are running together and SQL/ACCESS do not know how to read the
statement.

--

Ken Snell
<MS ACCESS MVP>

Bob M. said:
I get an error "Run-time error '2147467259 (80004005)':

Syntax error in union query."

Code is below... can anyone shed some light on this problem??????
Thanks in advance.... Bob M.
+_+_+_+_+_+_+_+_+_+_+_+_+_+_+_+_


Dim cnn1 As ADODB.Connection
Dim rstEmployee As ADODB.Recordset
Dim strcnn As String
Dim cmd As String

' Open a connection without using a Data Source Name (DSN).
' with this connection... multiple recordsets can be opened
Set cnn1 = New ADODB.Connection
cnn1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " &
DataBasePathAndFileName & ";Persist Security Info=False"
cnn1.ConnectionTimeout = 30
cnn1.Open

cmd = "(SELECT [REmployee.EmployeeSSNumber, REmployee.FileId," & _
"REmployee.CompanyID, REmployee.RecordType, REmployee.StateCode," & _
"REmployee.EmployeeFirstName, REmployee.EmployeeLastName,
REmployee.QuarterEnding]" & _
"From REmployee" & _
"WHERE (((REmployee.EmployeeSSNumber) In" & _
"(SELECT [EmployeeSSNumber] FROM [REmployee] As Tmp" & _
"GROUP BY [EmployeeSSNumber] HAVING Count(*)>1 ))" & _
"AND ((REmployee.FileId)=""Fl_u015""))" & _
"ORDER BY REmployee.EmployeeSSNumber;"

Set rstEmployee = New ADODB.Recordset
rstEmployee.CursorType = adOpenKeyset
rstEmployee.LockType = adLockOptimistic
rstEmployee.Open cmd, cnn1, , adCmdText

Do While Not rstEmployee.EOF
Debug.Print rstEmployee!SSNumber
Loop

Close cnn1
Click to expand...
Click to expand...
 
Thanks again Ken,

I did put in all the spaces with no luck. Then I thought perhaps the
cancantenated string was too long. I will keep you posted either late
tonight or tomorrow... thanks again. Bob.


Ken Snell said:
I also note that you're missing an ending ) in the last line.

The error message is saying that it thinks your query is a UNION query, when
in fact it's not. That is the tipoff that it seems to be misreading your
query's SQL.


--

Ken Snell
<MS ACCESS MVP>

Bob M. said:
Ken,

Thanks for the quich reply but there are spaces if you look carefully. This
string does work as the recordsource property for my adodc objet and
populates a datagrid with no problem. I cut and pasted the string which
works directly into this message.

Bob ..
^




Add
Click to expand...
Source=
"
&
DataBasePathAndFileName & ";Persist Security Info=False"
cnn1.ConnectionTimeout = 30
cnn1.Open

cmd = "(SELECT [REmployee.EmployeeSSNumber, REmployee.FileId," & _
"REmployee.CompanyID, REmployee.RecordType,
Click to expand...
Click to expand...
REmployee.StateCode,"
&
_
"REmployee.EmployeeFirstName, REmployee.EmployeeLastName,
REmployee.QuarterEnding]" & _
"From REmployee" & _
"WHERE (((REmployee.EmployeeSSNumber) In" & _
"(SELECT [EmployeeSSNumber] FROM [REmployee] As Tmp" & _
"GROUP BY [EmployeeSSNumber] HAVING Count(*)>1 ))" & _
"AND ((REmployee.FileId)=""Fl_u015""))" & _
"ORDER BY REmployee.EmployeeSSNumber;"

Set rstEmployee = New ADODB.Recordset
rstEmployee.CursorType = adOpenKeyset
rstEmployee.LockType = adLockOptimistic
rstEmployee.Open cmd, cnn1, , adCmdText

Do While Not rstEmployee.EOF
Debug.Print rstEmployee!SSNumber
Loop

Close cnn1
Click to expand...
Click to expand...
Click to expand...
 
See Douglas's reply. Also, I am not sure why you have the opening
parenthesis at the beginning of the SQL String which creates imbalance in
the parentheses. Try the simplified SQL String:

cmd = "SELECT EmployeeSSNumber, REmployee.FileId, " & _
" CompanyID, RecordType, StateCode, " & _
" EmployeeFirstName, EmployeeLastName, QuarterEnding " & _
" FROM REmployee " & _
" WHERE (EmployeeSSNumber In " & _
" ( SELECT [EmployeeSSNumber] FROM [REmployee] As Tmp " & _
" GROUP BY [EmployeeSSNumber] HAVING Count(*)>1 ) ) " & _
" AND (FileId= ""Fl_u015"" )" & _
"ORDER BY EmployeeSSNumber"

HTH
Van T. Dinh
MVP (Access)
 
Sorry. Still need some more spaces. Try again:

cmd = "SELECT EmployeeSSNumber, REmployee.FileId, " & _
" CompanyID, RecordType, StateCode, " & _
" EmployeeFirstName, EmployeeLastName, QuarterEnding " & _
" FROM REmployee " & _
" WHERE (EmployeeSSNumber In " & _
" ( SELECT [EmployeeSSNumber] FROM [REmployee] As Tmp " & _
" GROUP BY [EmployeeSSNumber] HAVING Count(*)>1 ) ) " & _
" AND (FileId= ""Fl_u015"" ) " & _
" ORDER BY EmployeeSSNumber"

HTH
Van T. Dinh
MVP (Access)
 
To make things easier for you, I strongly recommend you not just study
the VBA code. Put a breakpoint after the code and examine the string
generated. You can even paste this into the SQL view and find out if
it works. This should make problems like you have here easy to find.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Hey guys... Thanks so much. Van... I put in your code and it worked.

I HAVE ONE PROBLEM THOUGH.

Below is actual output from query (except frist 2 lines):... problem is
stated after sample data.....

Rec# FileID SS# Lastname Firstname
======|======|========|============|==========
Rec# 20 Fl_u015 229806158 RORER JOHN
Rec# 21 Fl_u015 229806158 RORER JOHN
Rec# 22 Fl_u015 239605327 CRUZ ISMAEL
Rec# 23 Fl_u015 239605327 CRUZ ISMAEL
Rec# 24 Fl_u015 242415467 VANDERHAAR KIRK
Rec# 25 Fl_u015 242415467 VANDERHAAR KIRK
Rec# 26 Fl_u015 254905806 SWAIN BERT
Rec# 27 Fl_u015 254905806 SWAIN BERT
Rec# 28 Fl_u015 261252957 FULLERTON MICHAEL
Rec# 29 Fl_u015 261588101 PEREZ MARGARET
Rec# 30 Fl_u015 261840282 DASH LORRAINE


You will notice that there are duplicate soc sec. numbers until rec# 28.
Upon inspection of the database I was stumped. There is a previos run with
FILEID named fl_u014. The query picked up duplicate ss#'s from the fl_U014
and erroneously populated the recordset with it. I only want duplicate
ss#'s for one particular FILEID (fl_u015) which is hard coded into the SQL
statement at this tiome but will be a varible in the future. I don't know
SQL good enough to figure out what conditional is missing and from where in
the statement.

Thanks again everyone for all your help. ..... BOB
 
Am I understanding correctly that the SSN where count is greater than zero
also should be filtered by the FL_u015 criterion? If yes, try this:

cmd = "SELECT EmployeeSSNumber, REmployee.FileId, " & _
" CompanyID, RecordType, StateCode, " & _
" EmployeeFirstName, EmployeeLastName, QuarterEnding " & _
" FROM REmployee " & _
" WHERE (EmployeeSSNumber In " & _
" ( SELECT [EmployeeSSNumber] FROM [REmployee] As Tmp " & _
" WHERE FileId= ""Fl_u015"" " & _
" GROUP BY [EmployeeSSNumber] HAVING Count(*)>1 ) ) " & _
" AND (FileId= ""Fl_u015"" )" & _
"ORDER BY EmployeeSSNumber"
 
cmd = "SELECT EmployeeSSNumber, REmployee.FileId, " & _
" CompanyID, RecordType, StateCode, " & _
" EmployeeFirstName, EmployeeLastName, QuarterEnding " & _
" FROM REmployee " & _
" WHERE (EmployeeSSNumber In " & _
" ( SELECT [EmployeeSSNumber] FROM [REmployee] As Tmp " & _
" WHERE FileId= """ & Variable & """ " & _
" GROUP BY [EmployeeSSNumber] HAVING Count(*)>1 ) ) " & _
" AND (FileId= """ & Variable & """ )" & _
"ORDER BY EmployeeSSNumber"

or

cmd = "SELECT EmployeeSSNumber, REmployee.FileId, " & _
" CompanyID, RecordType, StateCode, " & _
" EmployeeFirstName, EmployeeLastName, QuarterEnding " & _
" FROM REmployee " & _
" WHERE (EmployeeSSNumber In " & _
" ( SELECT [EmployeeSSNumber] FROM [REmployee] As Tmp " & _
" WHERE FileId= " & Chr(34) & Variable & Chr(34) & _
" GROUP BY [EmployeeSSNumber] HAVING Count(*)>1 ) ) " & _
" AND (FileId= " & Chr(34) & Variable & Chr(34) & " )" & _
"ORDER BY EmployeeSSNumber"

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Bob M. said:
Ken,

Thanks again. That worked perfect. Now how do I change the string
"fl_u015" to a literal so the the filename can change? Do I use tick
'marks'?

Thanks so much.


Ken Snell said:
Am I understanding correctly that the SSN where count is greater than zero
also should be filtered by the FL_u015 criterion? If yes, try this:

cmd = "SELECT EmployeeSSNumber, REmployee.FileId, " & _
" CompanyID, RecordType, StateCode, " & _
" EmployeeFirstName, EmployeeLastName, QuarterEnding " & _
" FROM REmployee " & _
" WHERE (EmployeeSSNumber In " & _
" ( SELECT [EmployeeSSNumber] FROM [REmployee] As Tmp " & _
" WHERE FileId= ""Fl_u015"" " & _
" GROUP BY [EmployeeSSNumber] HAVING Count(*)>1 ) ) " & _
" AND (FileId= ""Fl_u015"" )" & _
"ORDER BY EmployeeSSNumber"


--

Ken Snell
<MS ACCESS MVP>

Bob M. said:
Hey guys... Thanks so much. Van... I put in your code and it worked.

I HAVE ONE PROBLEM THOUGH.

Below is actual output from query (except frist 2 lines):... problem is
stated after sample data.....

Rec# FileID SS# Lastname Firstname
======|======|========|============|==========
Rec# 20 Fl_u015 229806158 RORER JOHN
Rec# 21 Fl_u015 229806158 RORER JOHN
Rec# 22 Fl_u015 239605327 CRUZ ISMAEL
Rec# 23 Fl_u015 239605327 CRUZ ISMAEL
Rec# 24 Fl_u015 242415467 VANDERHAAR KIRK
Rec# 25 Fl_u015 242415467 VANDERHAAR KIRK
Rec# 26 Fl_u015 254905806 SWAIN BERT
Rec# 27 Fl_u015 254905806 SWAIN BERT
Rec# 28 Fl_u015 261252957 FULLERTON MICHAEL
Rec# 29 Fl_u015 261588101 PEREZ MARGARET
Rec# 30 Fl_u015 261840282 DASH LORRAINE


You will notice that there are duplicate soc sec. numbers until rec# 28.
Upon inspection of the database I was stumped. There is a previos run with
FILEID named fl_u014. The query picked up duplicate ss#'s from the fl_U014
and erroneously populated the recordset with it. I only want duplicate
ss#'s for one particular FILEID (fl_u015) which is hard coded into the SQL
statement at this tiome but will be a varible in the future. I don't know
SQL good enough to figure out what conditional is missing and from
Click to expand...
where
in
the statement.

Thanks again everyone for all your help. ..... BOB
Click to expand...
Click to expand...
Click to expand...
 
Ken,

Thanks again. That worked perfect. Now how do I change the string
"fl_u015" to a literal so the the filename can change? Do I use tick
'marks'?

Thanks so much.


Ken Snell said:
Am I understanding correctly that the SSN where count is greater than zero
also should be filtered by the FL_u015 criterion? If yes, try this:

cmd = "SELECT EmployeeSSNumber, REmployee.FileId, " & _
" CompanyID, RecordType, StateCode, " & _
" EmployeeFirstName, EmployeeLastName, QuarterEnding " & _
" FROM REmployee " & _
" WHERE (EmployeeSSNumber In " & _
" ( SELECT [EmployeeSSNumber] FROM [REmployee] As Tmp " & _
" WHERE FileId= ""Fl_u015"" " & _
" GROUP BY [EmployeeSSNumber] HAVING Count(*)>1 ) ) " & _
" AND (FileId= ""Fl_u015"" )" & _
"ORDER BY EmployeeSSNumber"


--

Ken Snell
<MS ACCESS MVP>

Bob M. said:
Hey guys... Thanks so much. Van... I put in your code and it worked.

I HAVE ONE PROBLEM THOUGH.

Below is actual output from query (except frist 2 lines):... problem is
stated after sample data.....

Rec# FileID SS# Lastname Firstname
======|======|========|============|==========
Rec# 20 Fl_u015 229806158 RORER JOHN
Rec# 21 Fl_u015 229806158 RORER JOHN
Rec# 22 Fl_u015 239605327 CRUZ ISMAEL
Rec# 23 Fl_u015 239605327 CRUZ ISMAEL
Rec# 24 Fl_u015 242415467 VANDERHAAR KIRK
Rec# 25 Fl_u015 242415467 VANDERHAAR KIRK
Rec# 26 Fl_u015 254905806 SWAIN BERT
Rec# 27 Fl_u015 254905806 SWAIN BERT
Rec# 28 Fl_u015 261252957 FULLERTON MICHAEL
Rec# 29 Fl_u015 261588101 PEREZ MARGARET
Rec# 30 Fl_u015 261840282 DASH LORRAINE


You will notice that there are duplicate soc sec. numbers until rec# 28.
Upon inspection of the database I was stumped. There is a previos run with
FILEID named fl_u014. The query picked up duplicate ss#'s from the fl_U014
and erroneously populated the recordset with it. I only want duplicate
ss#'s for one particular FILEID (fl_u015) which is hard coded into the SQL
statement at this tiome but will be a varible in the future. I don't know
SQL good enough to figure out what conditional is missing and from where in
the statement.

Thanks again everyone for all your help. ..... BOB
Click to expand...
Click to expand...
 
Back
Top