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.
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.