[AccessD] Subquery returns to many or no records

Hi Chester

> When I try to run the following query access displays the error message
> subquery returned multiple records 

> SELECT DISTINCT T9DATA_CATDAILYPRODM.ORAACTDT, T9DATA_CATDAILYPRODM.BOPD
> FROM T9DATA_CATDAILYPRODM
> WHERE (((T9DATA_CATDAILYPRODM.ORAACTDT)=#10/1/2003#) AND
> ((T9DATA_CATDAILYPRODM.BOPD)=(SELECT BOPD from T9DATA_CATDAILYPRODM
> where USRFLD1 ="Y")) AND ((T9DATA_CATDAILYPRODM.AREADESC)="TEXAS GAS")
> AND ((T9DATA_CATDAILYPRODM.SUPVNM)="TGF FORT STOCKTON" Or
> (T9DATA_CATDAILYPRODM.SUPVNM)="TGF KERMIT"));

> If I change the query to the following no records are returned

> SELECT DISTINCT T9DATA_CATDAILYPRODM.ORAACTDT, T9DATA_CATDAILYPRODM.BOPD
> FROM T9DATA_CATDAILYPRODM
> WHERE (((T9DATA_CATDAILYPRODM.ORAACTDT)=#10/1/2003#) AND
> ((T9DATA_CATDAILYPRODM.BOPD)=(SELECT SUM(BOPD) from T9DATA_CATDAILYPRODM
> where USRFLD1 ="Y")) AND ((T9DATA_CATDAILYPRODM.AREADESC)="TEXAS GAS")
> AND ((T9DATA_CATDAILYPRODM.SUPVNM)="TGF FORT STOCKTON" Or
> (T9DATA_CATDAILYPRODM.SUPVNM)="TGF KERMIT"));

> What am I missing here. There should be records returned.

Not if sum doesn't equal one record and the remaining are all zero ...

Try this using this condition as the last in the Where clause:

  .. AND T9DATA_CATDAILYPRODM.BOPD IN (SELECT BOPD from T9DATA_CATDAILYPRODM
  where USRFLD1 ="Y");

/gustav