[AccessD] Subquery returns to many or no records

Tried your suggestion with no change in the result. I checked and there
are 21 records on the subject date. I want to sum the records that meet
the subquery criteria. Thanks

-----Original Message-----
From: Gustav Brock [mailto:gustav at cactus.dk] 
Sent: Tuesday, October 07, 2003 10:30 AM
To: Access Developers discussion and problem solving
Subject: Re: [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

_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com