<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD><TITLE>Message</TITLE>
<META http-equiv=Content-Type content="text/html; charset=us-ascii">
<META content="MSHTML 6.00.2800.1226" name=GENERATOR></HEAD>
<BODY>
<DIV><SPAN class=180301316-07102003><FONT face=Arial color=#0000ff size=2>Bad
news. Still no records returned. Thanks for the idea.</FONT></SPAN></DIV>
<DIV></DIV>
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left><FONT face=Tahoma
size=2>-----Original Message-----<BR><B>From:</B> Yeatman, Tony
[mailto:Tony.Yeatman@creditlyonnais.co.uk] <BR><B>Sent:</B> Tuesday, October 07,
2003 10:56 AM<BR><B>To:</B> 'Access Developers discussion and problem
solving'<BR><B>Subject:</B> RE: [AccessD] Subquery returns to many or no
records<BR><BR></FONT></DIV>
<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>