Prev: linked tables query
Next: Subdatasheet records not updating totals
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