[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
>
>
>