Jeff B
jeff.developer at gmail.com
Thu Jan 3 10:15:08 CST 2013
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