[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