[AccessD] Subquery

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