Sub select on a MS Access table within Crystal 8.5

[fancyface]

Hi, I am trying to do a sql subselect using "not exists" within Crystal 8.5. I'm exporting the data out of our database using Access 2003 and then opening it up within Crystal 8.5 and would like to do a "not exists" subselect utilizing the following code: Is this possible?
SELECT
NF_EXECU.`Const ID`, NF_EXECU.`Fin Type`
FROM
`NF_EXECU` NF_EXECU
Where NOT EXISTS
(SELECT NF_EXECU2.`Const ID’
FROM`NF_EXECU` NF_EXECU2
WHERE NF_EXECU.`Const ID` = NF_EXECU2.`Const ID` AND NF_EXECU2.`Fin Type’ = ‘Senior Executives’)
ORDER BY
NF_EXECU.`Const ID` ASC

ODBC error: [Microsoft][ODBC Microsoft Access Driver]Syntax error in string in query expression 'NOT EXISTS (SELECT NF_EXECU2.'Const ID'
FROM 'NF_EXECU' NF_EXECU2
WHERE NF_EXECU'CONST ID' = NF_EXECU2.'CONST ID'

Any insight would be greatly appreciated. Thanks in advance.

 

[lbass]

Where are you trying to enter this code?

-LB

 

[fancyface]

Hi, I was trying to enter it directly into the Show SQL Query area.

 

[fancyface]

Sorry - within Crystal 8.5

 

[lbass]

I tested this, and it worked fine, and then I noticed you were missing a space after "from" in the subquery. When I removed the space from my test report, I got the same error. I just changed your alias table so I could see more clearly what you had done--no need to change that.

SELECT
NF_EXECU.`Const ID`, NF_EXECU.`Fin Type`
FROM
`NF_EXECU` NF_EXECU
Where NOT EXISTS
(
SELECT A.`Const ID'
FROM[red]space here[/red]`NF_EXECU` NF_EXECU2
WHERE NF_EXECU.`Const ID` = A.`Const ID` AND
A.`Fin Type' = 'Senior Executives'
)
ORDER BY
NF_EXECU.`Const ID` ASC

-LB

 

[fancyface]

Hi, thanks for your help. I made the change regarding the space but I am now getting an error message stating:

[Microsoft][ODBC Microsoft Access Driver]Too few parameters.Expected1

 

[fancyface]

Hi, I got it to work. I just put in an easy parameter and query runs. Thanks for your help.