[AccessD] Subquery returns to many or no records

Bad news. Still no records returned. Thanks for the idea.
-----Original Message-----
From: Yeatman, Tony [mailto:Tony.Yeatman at creditlyonnais.co.uk] 
Sent: Tuesday, October 07, 2003 10:56 AM
To: 'Access Developers discussion and problem solving'
Subject: RE: [AccessD] Subquery returns to many or no records



Try 

(SELECT MAX(BOPD) 
        From T9DATA_CATDAILYPRODM 
        where USRFLD1 ="Y" 
        ) 

Tony. 

-----Original Message----- 
From: Kaup, Chester A [mailto:kaupca at chevrontexaco.com] 
Sent: 07 October 2003 16:51 
To: Access Developers discussion and problem solving 
Subject: RE: [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 


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



************************************************************************
*
The information in this internet E-mail is confidential and is intended 
solely for the addressee. Access, copying or re-use of information in it

by anyone else is unauthorized. Any views or opinions presented are 
solely those of the author and do not necessarily represent those of
Credit Lyonnais or any of its affiliates. The information contained
herein
is recorded for business purposes and use of services is monitored to
protect both the company and its individual users. If you are not the 
intended recipient please contact postmaster at creditlyonnais.co.uk
************************************************************************
*

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://databaseadvisors.com/pipermail/accessd/attachments/20031007/5a87e35a/attachment-0001.html>