Kaup, Chester
Chester_Kaup at kindermorgan.com
Wed Jan 4 16:42:33 CST 2012
Well I got it to run now but I get only 11 of the 22 meter-id's. Only the ones with the max date of all meter_ids show up. Here is what the SQL looks like. Thanks a lot for you time and trouble looking at this. 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].* 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: Wednesday, January 04, 2012 3:50 PM To: Access Developers discussion and problem solving Subject: Re: [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 > > > -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com