[AccessD] Subquery

And so you do!  That's the trouble with writing air SQL on the fly, so to
speak.  I left out the FROM condition in the subquqery. Try changing this:

(SELECT [qry Produced Gas CO2 Analysis].*) As qry2
to this:
(SELECT [qry Produced Gas CO2 Analysis].* FROM [qry Produced Gas CO2
Analysis]) As qry2

On Wed, Jan 4, 2012 at 1:05 PM, Kaup, Chester <Chester_Kaup at kindermorgan.com
> wrote:

> No harm done. I just appreciate the help. When I put it all together I get
> something like this. I get the following message when I try to the run the
> query.
>
> "Query input must contain at least one table or query."
>
> SELECT [qry Produced Gas CO2 Analysis].Sample_Date,
> [qry Produced Gas CO2 Analysis].Meter_ID,
> [qry Produced Gas CO2 Analysis].[Co2 Cut]
> FROM [qry Produced Gas CO2 Analysis]
> WHERE ((([qry Produced Gas CO2 Analysis].Sample_Date)=
> (Select Max(qry2.Sample_Date) from
> (SELECT [qry Produced Gas CO2 Analysis].*)  As qry2
>  WHERE qry2.Meter_ID=[qry Produced Gas CO2 Analysis].Meter_ID)));
>
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com [mailto:
> accessd-bounces at databaseadvisors.com] On Behalf Of Charlotte Foust
> Sent: Tuesday, January 03, 2012 6:28 PM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Subquery
>
> Sorry, sloppy on my part.  It should be
>
> WHERE [qry Produced Gas CO2 Analysis].Sample_Date=(Select
> Max(qry2.Sample_Date) from (SELECT [qry Produced Gas CO2 Analysis].*)  As
> qry2 WHERE qry2.Meter_ID=[qry Produced Gas CO2 Analysis].Meter_ID)
>
> Try that.  Think of the subquery (qry2) as a table that produces a specific
> kind of result. So this part is the subquery:   (SELECT [qry Produced Gas
> CO2 Analysis].*) As qry2
>
> Charlotte Foust
> On Tue, Jan 3, 2012 at 12:43 PM, Kaup, Chester <
> Chester_Kaup at kindermorgan.com> wrote:
>
> > I tried to run your SQL and got the following message. I have worked
> > on it but cannot see the problem. I need someone smarter then me to
> > see the problem. Thanks.
> >
> > Syntax error in query expression
> > '[qry Produced Gas CO2 Analysis].Sample_Date=(Select Max(Sample_Date)
> > from ([qry Produced Gas CO2 Analysis] As qry2  WHERE
> > qry2.Meter_ID=[qry Produced Gas CO2 Analysis].Meter_ID ))'.
> >
> > -----Original Message-----
> > From: accessd-bounces at databaseadvisors.com [mailto:
> > accessd-bounces at databaseadvisors.com] On Behalf Of Charlotte Foust
> > Sent: Tuesday, January 03, 2012 1:47 PM
> > To: Access Developers discussion and problem solving
> > Subject: Re: [AccessD] Subquery
> >
> > So you need to add an and to your WHERE to filter on the meter_ID, but
> > you aren't using a subquery correctly.  You need to alias the second
> > instance of the query so you can filter the aliased instance down to
> > the matching Meter_ID .
> >
> > SELECT [qry Produced Gas CO2 Analysis].Sample_Date, [qry Produced Gas
> > CO2 Analysis].Meter_ID, [qry Produced Gas CO2 Analysis].[Co2 Cut] FROM
> > [qry Produced Gas CO2 Analysis] WHERE [qry Produced Gas CO2
> > Analysis].Sample_Date=(Select Max(Sample_Date) from ([qry Produced Gas
> > CO2 Analysis] As qry2  WHERE qry2.Meter_ID=[qry Produced Gas CO2
> > Analysis].Meter_ID ))
> >
> > **WARNING** this is air SQL, so haven't tested it.  Running from
> > Access you have to wrap a subquery in parens and the editor will
> > convert those to .[ ] when the query runs.
> >
> > Charlotte Foust
> > On Tue, Jan 3, 2012 at 10:33 AM, Kaup, Chester <
> > Chester_Kaup at kindermorgan.com> wrote:
> >
> > > I am trying to do a subquery to retrieve some data but am thinking
> > > it may not be possible. Here is the query that runs correctly. The
> > > problem is
> > that
> > > there is a sample date for each meter_id but of course the subquery
> > returns
> > > only the max date not a max date for each meter_id. I am thinking 2
> > > separate queries.
> > >
> > > SELECT [qry Produced Gas CO2 Analysis].Sample Date, [qry Produced
> > > Gas CO2 Analysis].Meter_ID, [qry Produced Gas CO2 Analysis].[Co2
> > > Cut] FROM [qry Produced Gas CO2 Analysis] WHERE [qry Produced Gas
> > > CO2 Analysis].Sample_Date=(Select
> > Max(Sample_Date)
> > >  from [qry Produced Gas CO2 Analysis]);
> > >
> > > Chester Kaup
> > > Engineering Technician
> > > Kinder Morgan CO2 Company, LLP
> > > Office (432) 688-3797
> > > FAX (432) 688-3799
> > >
> > >
> > > No trees were killed in the sending of this message. However a large
> > > number of electrons were terribly inconvenienced.
> > >
> > >
> > > --
> > > 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
>
>
> >
> >
> >
> >
> > --
> > 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
>
>
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
>
>
> Website: http://www.databaseadvisors.com
>
>
>