From: dmills on 12 Mar 2010 09:32 Could Someone Tell Me What Is Wrong With This SQL... It keeps telling me that the FROM Clause is the syntax problem... SELECT tCurtran.TID, tCurtran.EDATE1, ICurtran.EDATE1, ICurtran.appsta FROM [Curtran] JOIN ICurtran on tCurtran.TID=ICurtran.TID WHERE tCurtran.appsta in (8,9,10,11) and ICurtran.EDATE1>tCurtran.EDATE1 and not exists (SELECT 1 from BCurtran WHERE BCurtran.edate1 between tCurtran.EDATE1 and ICurtran.EDATE1 and BCurtran.TID=tcurtran.TID) I have a table [curtran] i need 3 instances of the table matched to itself: tcurtran, icurtran, bcurtran Thanks in advance
From: Stefan Hoffmann on 12 Mar 2010 09:44 hi, On 12.03.2010 15:32, dmills wrote: > Could Someone Tell Me What Is Wrong With This SQL... It keeps telling me that > the FROM Clause is the syntax problem... > > SELECT tCurtran.TID, tCurtran.EDATE1, ICurtran.EDATE1, ICurtran.appsta > FROM [Curtran] > JOIN ICurtran on tCurtran.TID=ICurtran.TID You have specified another table name after the FROM: [Curtran] <> tCurtran btw, using table alias names would have avoided this: SELECT C.TID, C.EDATE1, I.EDATE1, I.appsta FROM Curtran C JOIN ICurtran I ON C.TID = I.TID ... mfG --> stefan <--
From: Jerry Whittle on 12 Mar 2010 09:58 You need to define the tables with something like: FROM [Curtran] AS tCurtran You'll need to do this on all 3 variations of Curtran. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "dmills" wrote: > Could Someone Tell Me What Is Wrong With This SQL... It keeps telling me that > the FROM Clause is the syntax problem... > > SELECT tCurtran.TID, tCurtran.EDATE1, ICurtran.EDATE1, ICurtran.appsta > FROM [Curtran] > JOIN ICurtran on tCurtran.TID=ICurtran.TID > WHERE tCurtran.appsta in (8,9,10,11) and ICurtran.EDATE1>tCurtran.EDATE1 and > not exists > (SELECT 1 from BCurtran WHERE BCurtran.edate1 between tCurtran.EDATE1 and > ICurtran.EDATE1 and BCurtran.TID=tcurtran.TID) > > I have a table [curtran] i need 3 instances of the table matched to itself: > tcurtran, icurtran, bcurtran > > Thanks in advance
From: John Spencer on 12 Mar 2010 10:00 You are not specifying the join type. Instead of JOIN you should have INNER JOIN, LEFT JOIN, or RIGHT JOIN. The most common type of join is an INNER JOIN. SELECT tCurtran.TID, tCurtran.EDATE1, ICurtran.EDATE1, ICurtran.appsta FROM [Curtran] INNER JOIN ICurtran on tCurtran.TID=ICurtran.TID WHERE tCurtran.appsta in (8,9,10,11) and ICurtran.EDATE1>tCurtran.EDATE1 and not exists (SELECT 1 from BCurtran WHERE BCurtran.edate1 between tCurtran.EDATE1 and ICurtran.EDATE1 and BCurtran.TID=tcurtran.TID) John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County dmills wrote: > Could Someone Tell Me What Is Wrong With This SQL... It keeps telling me that > the FROM Clause is the syntax problem... > > SELECT tCurtran.TID, tCurtran.EDATE1, ICurtran.EDATE1, ICurtran.appsta > FROM [Curtran] > JOIN ICurtran on tCurtran.TID=ICurtran.TID > WHERE tCurtran.appsta in (8,9,10,11) and ICurtran.EDATE1>tCurtran.EDATE1 and > not exists > (SELECT 1 from BCurtran WHERE BCurtran.edate1 between tCurtran.EDATE1 and > ICurtran.EDATE1 and BCurtran.TID=tcurtran.TID) > > I have a table [curtran] i need 3 instances of the table matched to itself: > tcurtran, icurtran, bcurtran > > Thanks in advance
From: De Jager on 13 Mar 2010 12:22 "dmills" <dmills(a)discussions.microsoft.com> wrote in message news:8F5A02FE-5AAF-456E-B262-E04EFF50B56D(a)microsoft.com... > Could Someone Tell Me What Is Wrong With This SQL... It keeps telling me > that > the FROM Clause is the syntax problem... > > SELECT tCurtran.TID, tCurtran.EDATE1, ICurtran.EDATE1, ICurtran.appsta > FROM [Curtran] > JOIN ICurtran on tCurtran.TID=ICurtran.TID > WHERE tCurtran.appsta in (8,9,10,11) and ICurtran.EDATE1>tCurtran.EDATE1 > and > not exists > (SELECT 1 from BCurtran WHERE BCurtran.edate1 between tCurtran.EDATE1 and > ICurtran.EDATE1 and BCurtran.TID=tcurtran.TID) > > I have a table [curtran] i need 3 instances of the table matched to > itself: > tcurtran, icurtran, bcurtran > > Thanks in advance
|
Pages: 1 Prev: linked tables query Next: Subdatasheet records not updating totals |