[AccessD] Subquery

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