Kaup, Chester
Chester_Kaup at kindermorgan.com
Thu Jan 3 11:28:47 CST 2013
The change below caused the query to ask for a parameter value for (t1.StatusDate). I was able to get it to work with the following changes. Had to use table names and another subquery. WHERE dbo_DSS_StatusChanges.StatusDate>(Select CDate([Allocation_Stats]![DateLast_S]) from Allocation_Stats) I got more records than I wanted so removed the CDate(INT(StatusDate)) from the query and ended with a query like this. Everything works now. Thanks for the assistance. SELECT t1.PID, Left([t1.PID],10) AS API10, t1.Completion_Name, WellStatus([Status]) AS StatusShort, CDate(Int([StatusDate])) AS Status_Date FROM dbo_DSS_StatusChanges AS t1 INNER JOIN (SELECT DISTINCT PID, min(StatusDate) AS lastdatetime FROM dbo_DSS_StatusChanges WHERE dbo_DSS_StatusChanges.StatusDate>(Select CDate([Allocation_Stats]![DateLast_S]) from Allocation_Stats) GROUP BY PID) AS t2 ON (t1.PID = t2.PID) AND (t1.StatusDate = t2.lastdatetime); -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Paul Hartland Sent: Thursday, January 03, 2013 10:27 AM To: Access Developers discussion and problem solving Subject: Re: [AccessD] Error in from clause try this quick (not tested) slightly modifed one SELECT t1.PID, Left([t1.PID],10) AS API10, t1.Completion_Name, WellStatus([Status]) AS StatusShort, CDate(Int([StatusDate])) AS Status_Date FROM dbo_DSS_StatusChanges AS t1 INNER JOIN ( SELECT DISTINCT PID, CDate(INT(StatusDate)), min(StatusDate) AS lastdatetime FROM dbo_DSS_StatusChanges WHERE (t1.StatusDate)>CDate([Allocation_Stats]![DateLast_S]) GROUP BY PID, CDate(INT(StatusDate))) AS t2 ON (t1.PID = t2.PID) AND (t1.Status_Date = t2.lastdatetime); Paul On 3 January 2013 16:15, Jeff B <jeff.developer at gmail.com> wrote: > Sorry, didn't realize you had a subquery in there > > Jeff Barrows > MCP, MCAD, MCSD > > Outbak Technologies, LLC > Racine, WI > jeff.developer at gmail.com > > > -----Original Message----- > From: accessd-bounces at databaseadvisors.com > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Kaup, > Chester > Sent: Thursday, January 03, 2013 10:10 AM > To: Access Developers discussion and problem solving > Subject: Re: [AccessD] Error in from clause > > Don't both the query and subquery each need a from statement or am I wrong? > > This is the query I had previously done before I determined I was > using the wrong source table > > SELECT t1.PID, Left([t1.PID],10) AS API10, t1.Completion_Name, > WellStatus([Status]) AS StatusShort, CDate(Int([StatusDate])) AS > Status_Date FROM dbo_DSS_LastStatus AS t1 INNER JOIN (SELECT DISTINCT > PID, CDate(INT(StatusDate)), max(StatusDate) AS lastdatetime FROM > dbo_DSS_LastStatus GROUP BY PID, CDate(INT(StatusDate))) AS t2 ON > (t1.PID = > t2.PID) AND (t1.StatusDate = t2.lastdatetime); > > -----Original Message----- > From: accessd-bounces at databaseadvisors.com > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jeff B > Sent: Thursday, January 03, 2013 9:55 AM > To: 'Access Developers discussion and problem solving' > Subject: Re: [AccessD] Error in from clause > > It also looks like you have 2 FROM statements: > > SELECT t1.PID, Left([t1.PID],10) AS API10, t1.Completion_Name, > WellStatus([Status]) AS StatusShort, CDate(Int([StatusDate])) AS > Status_Date > >>>>>FROM dbo_DSS_StatusChanges AS t1 INNER JOIN (SELECT DISTINCT > >>>>>PID, > CDate(INT(StatusDate)), > min(StatusDate) AS lastdatetime<<<<< > FROM dbo_DSS_StatusChanges > WHERE t1.StatusDate)>CDate([Allocation_Stats]![DateLast_S] > GROUP BY PID, CDate(INT(StatusDate))) AS t2 ON (t1.PID = t2.PID) AND > (t1.StatusDate = t2.lastdatetime); > > Jeff Barrows > MCP, MCAD, MCSD > > Outbak Technologies, LLC > Racine, WI > jeff.developer at gmail.com > > > -----Original Message----- > From: accessd-bounces at databaseadvisors.com > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Kaup, > Chester > Sent: Thursday, January 03, 2013 9:53 AM > To: Access Developers discussion and problem solving > Subject: Re: [AccessD] Error in from clause > > Unfortunately I am still getting the error with the ( ) > > -----Original Message----- > From: accessd-bounces at databaseadvisors.com > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jeff B > Sent: Thursday, January 03, 2013 9:43 AM > To: 'Access Developers discussion and problem solving' > Subject: Re: [AccessD] Error in from clause > > Right off the bat, I'd say look at your WHERE statement, you are > missing an opening ( and a closing ) > > WHERE HERE > t1.StatusDate)>CDate([Allocation_Stats]![DateLast_S] <HERE > > Jeff Barrows > MCP, MCAD, MCSD > > Outbak Technologies, LLC > Racine, WI > jeff.developer at gmail.com > > -----Original Message----- > From: accessd-bounces at databaseadvisors.com > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Kaup, > Chester > Sent: Thursday, January 03, 2013 9:41 AM > To: Access Developers discussion and problem solving > Subject: [AccessD] Error in from clause > > The following query is giving me an error in from clause message. It > highlights the > in the where statement. The subquery works ok on its own. > What am I missing? Thanks. > > SELECT t1.PID, Left([t1.PID],10) AS API10, t1.Completion_Name, > WellStatus([Status]) AS StatusShort, CDate(Int([StatusDate])) AS > Status_Date FROM dbo_DSS_StatusChanges AS t1 INNER JOIN (SELECT > DISTINCT PID, CDate(INT(StatusDate)), > min(StatusDate) AS lastdatetime > FROM dbo_DSS_StatusChanges > WHERE t1.StatusDate)>CDate([Allocation_Stats]![DateLast_S] > GROUP BY PID, CDate(INT(StatusDate))) AS t2 ON (t1.PID = t2.PID) AND > (t1.StatusDate = t2.lastdatetime); > -- > 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 > -- Paul Hartland paul.hartland at googlemail.com -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com