[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/64b25c9c/attachment-0001.html>