Reporting out of a *.mdb within Crystal Reports

[fancyface]

Hi all, I am hoping someone can help me. I have exported query results from RE into a Microsoft Access 2000 table and then opened it in Crystal 8.5 and I am trying to customize the sql to tell me who has both Manager codes. I have been able to do this in the past (with Crystal 7/sql tables) with the use of "Exist" sub selects.

Data:

1016096 Moves Manager

1016096 Former Moves Manager



When I try and use the following SQL:



SELECT

NFMOV.`Constituent ID`, NFMOV.`Assigned Solicitor Type`

FROM

`NFMOV` NFMOV

WHERE

NOT EXISTS

( SELECT NFMOV2.`Constituent ID`

FROM `NFMOV` NFMOV2

WHERE NFMOV.`Constituent ID` = NFMOV2.`Constituent ID` and

NFMOV.`Assigned Solicitor Type` = NFMOV2.`Assigned Solicitor Type`

AND NFMOV2.`Assigned Solicitor Type` = 'Former Moves Manager')

and EXISTS

( SELECT NFMOV2.`Constituent ID`

FROM `NFMOV` NFMOV2

WHERE NFMOV.`Constituent ID` = NFMOV2.`Constituent ID` and

NFMOV.`Assigned Solicitor Type` = NFMOV2.`Assigned Solicitor Type` and

NFMOV2.`Assigned Solicitor Type` = 'Moves Manager')

ORDER BY

NFMOV.`Constituent ID` ASC

I always get one of the above rows returned. It is like both rows are not recognized. I have tried aliasing the 2nd subselect with NFMOV3. Nothing seems to work.

Thanks in advance.

 

[1DMF]

you may find a response if you post in the SQL forum, as this is really a SQL question.

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!