[AccessD] Subquery

It's doing what the sql is telling it to do because you're filtering on the
Max date expression. The query needs to be rebuilt based on a couple of
things.  What grouping are you using in the base query?  Is it Meter_ID,
Sample_Date, CO2_Cut?  You don't have a GROUP BY clause in the subquery SQL
you posted originally, and without it you're never going to get what you
need. In the Subquery, you need to group on Meter_ID and return the Max
Date by Meter_ID.  Then you need to join the base query to your subquery on
Meter_ID

The subquery should look like this:

(SELECT [qry Produced Gas CO2 Analysis,Meter_ID, MAX([qry Produced Gas CO2
Analysis.Sample_Date) As MaxDate FROM [qry Produced Gas CO2 Analysis] GROUP
BY [qry Produced Gas CO2 Analysis].Meter_ID) As qry2

That will give you the max Sample_Date for each Meter_ID.

Then you join the subquery to the base query like this:

SELECT [qry Produced Gas CO2 Analysis].Meter_ID,
[qry Produced Gas CO2 Analysis].[Co2 Cut], qry2.MaxDate.
FROM [qry Produced Gas CO2 Analysis]
RIGHT JOIN
(SELECT [qry Produced Gas CO2 Analysis,Meter_ID, MAX([qry Produced Gas CO2
Analysis.Sample_Date) As MaxDate FROM [qry Produced Gas CO2 Analysis] GROUP
BY [qry Produced Gas CO2 Analysis].Meter_ID) As qry2

ON [qry Produced Gas CO2 Analysis].Meter_ID = qry2.MeterID AND [qry
Produced Gas CO2 Analysis].Sample_Date = qry2.MaxDate

ORDER BY  [qry Produced Gas CO2 Analysis].Meter_ID, [qry Produced Gas CO2
Analysis].[Co2 Cut], qry2.MaxDate

That will give you the Meter_ID, the Max Date and the CO2 Cut for that
Sample Date by Meter_ID.  Is that what you wanted?

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]);


--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd


Website: http://www.databaseadvisors.com