<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=US-ASCII">
<META NAME="Generator" CONTENT="MS Exchange Server version 5.5.2653.12">
<TITLE>RE: [AccessD] Subquery returns to many or no records</TITLE>
</HEAD>
<BODY>
<P><FONT SIZE=2>Try </FONT>
</P>
<P><FONT SIZE=2>(SELECT MAX(BOPD) </FONT>
<BR> <FONT SIZE=2>From T9DATA_CATDAILYPRODM </FONT>
<BR> <FONT SIZE=2>where USRFLD1 ="Y"</FONT>
<BR> <FONT SIZE=2>)</FONT>
</P>
<P><FONT SIZE=2>Tony.</FONT>
</P>
<P><FONT SIZE=2>-----Original Message-----</FONT>
<BR><FONT SIZE=2>From: Kaup, Chester A [<A HREF="mailto:kaupca@chevrontexaco.com">mailto:kaupca@chevrontexaco.com</A>]</FONT>
<BR><FONT SIZE=2>Sent: 07 October 2003 16:51</FONT>
<BR><FONT SIZE=2>To: Access Developers discussion and problem solving</FONT>
<BR><FONT SIZE=2>Subject: RE: [AccessD] Subquery returns to many or no records</FONT>
</P>
<BR>
<P><FONT SIZE=2>Tried your suggestion with no change in the result. I checked and there</FONT>
<BR><FONT SIZE=2>are 21 records on the subject date. I want to sum the records that meet</FONT>
<BR><FONT SIZE=2>the subquery criteria. Thanks</FONT>
</P>
<P><FONT SIZE=2>-----Original Message-----</FONT>
<BR><FONT SIZE=2>From: Gustav Brock [<A HREF="mailto:gustav@cactus.dk">mailto:gustav@cactus.dk</A>] </FONT>
<BR><FONT SIZE=2>Sent: Tuesday, October 07, 2003 10:30 AM</FONT>
<BR><FONT SIZE=2>To: Access Developers discussion and problem solving</FONT>
<BR><FONT SIZE=2>Subject: Re: [AccessD] Subquery returns to many or no records</FONT>
</P>
<BR>
<P><FONT SIZE=2>Hi Chester</FONT>
</P>
<P><FONT SIZE=2>> When I try to run the following query access displays the error </FONT>
<BR><FONT SIZE=2>> message subquery returned multiple records</FONT>
</P>
<P><FONT SIZE=2>> SELECT DISTINCT T9DATA_CATDAILYPRODM.ORAACTDT, </FONT>
<BR><FONT SIZE=2>> T9DATA_CATDAILYPRODM.BOPD FROM T9DATA_CATDAILYPRODM WHERE </FONT>
<BR><FONT SIZE=2>> (((T9DATA_CATDAILYPRODM.ORAACTDT)=#10/1/2003#) AND </FONT>
<BR><FONT SIZE=2>> ((T9DATA_CATDAILYPRODM.BOPD)=(SELECT BOPD from T9DATA_CATDAILYPRODM </FONT>
<BR><FONT SIZE=2>> where USRFLD1 ="Y")) AND ((T9DATA_CATDAILYPRODM.AREADESC)="TEXAS GAS")</FONT>
</P>
<P><FONT SIZE=2>> AND ((T9DATA_CATDAILYPRODM.SUPVNM)="TGF FORT STOCKTON" Or </FONT>
<BR><FONT SIZE=2>> (T9DATA_CATDAILYPRODM.SUPVNM)="TGF KERMIT"));</FONT>
</P>
<P><FONT SIZE=2>> If I change the query to the following no records are returned</FONT>
</P>
<P><FONT SIZE=2>> SELECT DISTINCT T9DATA_CATDAILYPRODM.ORAACTDT, </FONT>
<BR><FONT SIZE=2>> T9DATA_CATDAILYPRODM.BOPD FROM T9DATA_CATDAILYPRODM WHERE </FONT>
<BR><FONT SIZE=2>> (((T9DATA_CATDAILYPRODM.ORAACTDT)=#10/1/2003#) AND </FONT>
<BR><FONT SIZE=2>> ((T9DATA_CATDAILYPRODM.BOPD)=(SELECT SUM(BOPD) from </FONT>
<BR><FONT SIZE=2>> T9DATA_CATDAILYPRODM where USRFLD1 ="Y")) AND </FONT>
<BR><FONT SIZE=2>> ((T9DATA_CATDAILYPRODM.AREADESC)="TEXAS GAS") AND </FONT>
<BR><FONT SIZE=2>> ((T9DATA_CATDAILYPRODM.SUPVNM)="TGF FORT STOCKTON" Or </FONT>
<BR><FONT SIZE=2>> (T9DATA_CATDAILYPRODM.SUPVNM)="TGF KERMIT"));</FONT>
</P>
<P><FONT SIZE=2>> What am I missing here. There should be records returned.</FONT>
</P>
<P><FONT SIZE=2>Not if sum doesn't equal one record and the remaining are all zero ...</FONT>
</P>
<P><FONT SIZE=2>Try this using this condition as the last in the Where clause:</FONT>
</P>
<P><FONT SIZE=2> .. AND T9DATA_CATDAILYPRODM.BOPD IN (SELECT BOPD from</FONT>
<BR><FONT SIZE=2>T9DATA_CATDAILYPRODM</FONT>
<BR><FONT SIZE=2> where USRFLD1 ="Y");</FONT>
</P>
<P><FONT SIZE=2>/gustav</FONT>
</P>
<P><FONT SIZE=2>_______________________________________________</FONT>
<BR><FONT SIZE=2>AccessD mailing list</FONT>
<BR><FONT SIZE=2>AccessD@databaseadvisors.com</FONT>
<BR><FONT SIZE=2><A HREF="http://databaseadvisors.com/mailman/listinfo/accessd" TARGET="_blank">http://databaseadvisors.com/mailman/listinfo/accessd</A></FONT>
<BR><FONT SIZE=2>Website: <A HREF="http://www.databaseadvisors.com" TARGET="_blank">http://www.databaseadvisors.com</A></FONT>
</P>
<BR>
<P><FONT SIZE=2>_______________________________________________</FONT>
<BR><FONT SIZE=2>AccessD mailing list</FONT>
<BR><FONT SIZE=2>AccessD@databaseadvisors.com</FONT>
<BR><FONT SIZE=2><A HREF="http://databaseadvisors.com/mailman/listinfo/accessd" TARGET="_blank">http://databaseadvisors.com/mailman/listinfo/accessd</A></FONT>
<BR><FONT SIZE=2>Website: <A HREF="http://www.databaseadvisors.com" TARGET="_blank">http://www.databaseadvisors.com</A></FONT>
</P>
<CODE><FONT SIZE=3><BR>
<BR>
*************************************************************************<BR>
The information in this internet E-mail is confidential and is intended <BR>
solely for the addressee. Access, copying or re-use of information in it <BR>
by anyone else is unauthorized. Any views or opinions presented are <BR>
solely those of the author and do not necessarily represent those of<BR>
Credit Lyonnais or any of its affiliates. The information contained herein<BR>
is recorded for business purposes and use of services is monitored to<BR>
protect both the company and its individual users. If you are not the <BR>
intended recipient please contact postmaster@creditlyonnais.co.uk<BR>
*************************************************************************<BR>
</FONT></CODE>
</BODY>
</HTML>