Access query error ("syntax error in from clause")

Question

$consulta3 = "SELECT * FROM Dept INNER JOIN Userinfo INNER JOIN Checkinout
                     on Dept.DeptName = '$departamento'
                     where Dept.Deptid = Userinfo.Deptid AND Userinfo.Name = Checkinout.name";

the thing im trying to do with this query is: i have a table called Dept where it has all the deparments names and ids, User info that has user name, a department id and a id. Also i have a 3rd table called checkinout that has user id, with a time.

So i want (using the deparment's name) to bring all the data from the Checkinout table from the people that belongs to that deparment, but when i try that query it gives me an error:

syntax error in from clause. sql state 37000 in sqlexecdirect

and i cant solve it. Im using a Acces Data base.

Thank you very much for you help.

your missing an on clause in the first join ?

user557846
– user557846

2013-09-23 19:52:32 +00:00
Commented Sep 23, 2013 at 19:52 — user557846
– user557846, Commented Sep 23, 2013 at 19:52

HansUp · Accepted Answer · 2013-09-23 20:18:49Z

2

Access absolutely requires parentheses in the FROM clause of any query which includes more that one join. If you have Access available, create and test a new query in the query designer. One of the reasons the designer is useful is that it knows the rules for parentheses which keep the db engine happy.

Start with a query similar to this. Don't worry about filtering based on Dept.DeptName at this point. Just make sure the joins are set up correctly.

SELECT *
FROM
    (Dept
    INNER JOIN Userinfo
    ON Dept.Deptid = Userinfo.Deptid)
    INNER JOIN Checkinout
    ON Userinfo.Name = Checkinout.name

After you have the joins set up correctly, add in your filter constraint (WHERE Dept.DeptName ...).

answered Sep 23, 2013 at 20:18

HansUp

97.3k11 gold badges80 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user2690047 Over a year ago

its not working for me: "SELECT * FROM (Dept INNER JOIN Userinfo ON Dept.Deptid = Userinfo.Deptid) INNER JOIN Checkinout ON Userinfo.Name = Checkinout.name where Dept.Name= '$departamento' "

HansUp Over a year ago

Does it work without the WHERE clause when tested within an Access application session?

Klas Lindbäck · Accepted Answer · 2013-09-23 19:55:56Z

0

You have reversed the inner join and where clauses. You should write the SQL like this:

SELECT * 
  FROM Dept 
 INNER JOIN Userinfo ON Dept.Deptid = Userinfo.Deptid
 INNER JOIN Checkinout ON Userinfo.Name = Checkinout.name"
 WHERE Dept.DeptName = '$departamento'

answered Sep 23, 2013 at 19:55

Klas Lindbäck

33.4k5 gold badges61 silver badges85 bronze badges

Comments

Sergej Panic · Accepted Answer · 2013-09-23 19:55:57Z

0

Probably should be:

SELECT * FROM Dept 
INNER JOIN Userinfo ON Dept.Deptid = Userinfo.Deptid 
INNER JOIN Checkinout on Userinfo.Name = Checkinout.name 
where Dept.DeptName = '$departamento'

answered Sep 23, 2013 at 19:55

Sergej Panic

8395 silver badges8 bronze badges

Comments

Vahid Hallaji · Accepted Answer · 2013-09-23 20:39:40Z

0

I think it should be :

SELECT * FROM (Dept 
INNER JOIN Userinfo ON (Dept.Deptid = Userinfo.Deptid)) 
INNER JOIN Checkinout ON (Userinfo.Name = Checkinout.name)
WHERE Dept.DeptName = '$departamento'

edited Sep 23, 2013 at 20:39

answered Sep 23, 2013 at 19:55

Vahid Hallaji

7,5576 gold badges46 silver badges52 bronze badges

Comments

user2690047 · Accepted Answer · 2013-10-11 14:52:32Z

0

what i decided to do is to use 2 different queries:

$consulta=  "SELECT * FROM Dept INNER JOIN Userinfo 
         ON Userinfo.Deptid = Dept.Deptid
         where Dept.DeptName = '$departamento'";

and

$consulta2 = "SELECT  *  FROM Checkinout, Userinfo
    where Checkinout.Userid = '$userid' AND  Userinfo.userid = '$userid' AND 
    Checkinout.Checktime BETWEEN CDate('$fecha1') AND CDate('$fecha2')";

The first one to select all the people that belong to one deparment, and the second one to find all the INs and OUTs of one person. Both in a while to be able to look each register of the first result and check with the other query

still have problems with the search, its not working fine, im trying to do a full query where it searchs in the 3 tables at the same time but its giving me a error:

$toda_consulta= "SELECT * 
                     FROM Dept 
                     INNER JOIN Userinfo ON (Dept.Deptid = Userinfo.Deptid)
                     INNER JOIN Checkinout ON (Checkinout.Userid = Userinfo.Userid)
                     WHERE Dept.DeptName = '$departamento' AND 
                           Checkinout.Checktime BETWEEN CDate('$fecha_inicio') AND CDate('$fecha_fin')";

error: Warning: odbc_exec() [function.odbc-exec]: SQL error: [Microsoft][Controlador ODBC Microsoft Access] sintax error (missing operator) in the expression '(Dept.Deptid = Userinfo.Deptid) INNER JOIN Checkinout ON (Checkinout.Userid = Userinfo.Userid)'., SQL state 37000 in SQLExecDirect in C:\wamp\www\casa 7-9-13\calcula_departamento1.php on line 51

than you for your help

edited Oct 11, 2013 at 14:52

answered Oct 8, 2013 at 21:09

user2690047

1331 gold badge1 silver badge12 bronze badges

2 Comments

Gord Thompson Over a year ago

If this is the approach that ultimately solved your problem then you should accept this answer. (It's okay to accept your own answer.)

HansUp Over a year ago

You need extra parentheses in the FROM clause because it includes more than one join. See my answer for an example.

Collectives™ on Stack Overflow

Access query error ("syntax error in from clause")

5 Answers 5

2 Comments

Comments

Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

2 Comments

Comments

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related