How to construct a join query, access ask for parameter ?

S

Stein.Andersen

Hi
Acces is asking for a parameter, and I cant understand why ???

I want to make a query for an LEFT JOIN betweem two tables B and C, and a INNER JOIN between the resulting rows and a third table.
The tables called is in the following called A, B, C (name of tables/fields changed) and the SQL used:

SELECT TABB.mid, tg.layr,
IIF(ISNULL(TABC.rid),"udef",mr.LCROOMID) AS RId,
IIF(ISNULL(TABC.rname),"udef",mr.LCROOMNAME) AS RName,
TABC.gross,TABC.net,TABC.id,
TABB.pid, TABB.handle, TABB.gtype,
TABA.gid, TABA.inode, TABA.x AS, TABA.y AS, TABA.z AS
FROM TABA INNER JOIN (TABB LEFT JOIN TABC ON TABC.fid=TABB.pfid) ON TABA.gid=TABB.gid
WHERE TABB.mid=19 And TABB.layr="COMMON" AND (TABB.LNSOURCETYPE=1 Or TABB.LNSOURCETYPE=2)
ORDER BY TABB.llgrelementtype, TABB.LNGRELEMENTID, TABB.lnnodeorder;

When using the query in access it is asked for a value for TABB.mid, even if it is given in the where-clause.
When using the SQL from program, error about missing parameters is given.

The same statement using oracles mechanisms for JOIN is working proberly, so I assume that it is som aspect
of the join-mechanism in Access i have missed.

Any help would be appreciated.

Thanks
 
V

Van T. Dinh

"AS" is a reserved word in JET SQL and I am not sure of your use of AS in:

TABA.x AS, TABA.y AS, TABA.z AS

Usually AS is followed by an alias which you used in the 2nd line of the SQL
String posted but you didn't have aliases in the above portion of the SQL.

--
HTH
Van T. Dinh
MVP (Access)


Hi
Acces is asking for a parameter, and I cant understand why ???

I want to make a query for an LEFT JOIN betweem two tables B and C, and a
INNER JOIN between the resulting rows and a third table.
The tables called is in the following called A, B, C (name of tables/fields
changed) and the SQL used:

SELECT TABB.mid, tg.layr,
IIF(ISNULL(TABC.rid),"udef",mr.LCROOMID) AS RId,
IIF(ISNULL(TABC.rname),"udef",mr.LCROOMNAME) AS RName,
TABC.gross,TABC.net,TABC.id,
TABB.pid, TABB.handle, TABB.gtype,
TABA.gid, TABA.inode, TABA.x AS, TABA.y AS, TABA.z AS
FROM TABA INNER JOIN (TABB LEFT JOIN TABC ON TABC.fid=TABB.pfid) ON
TABA.gid=TABB.gid
WHERE TABB.mid=19 And TABB.layr="COMMON" AND (TABB.LNSOURCETYPE=1 Or
TABB.LNSOURCETYPE=2)
ORDER BY TABB.llgrelementtype, TABB.LNGRELEMENTID, TABB.lnnodeorder;

When using the query in access it is asked for a value for TABB.mid, even if
it is given in the where-clause.
When using the SQL from program, error about missing parameters is given.

The same statement using oracles mechanisms for JOIN is working proberly, so
I assume that it is som aspect
of the join-mechanism in Access i have missed.

Any help would be appreciated.

Thanks
 
S

Stein.Andersen

Hi
AS is as You say AS is a reserved word, I seems that my editing did remove
to litle, the "actual SQL" used is:

SELECT
TABB.mid, tg.layr,
IIF(ISNULL(TABC.rid),"udef",TABC.rid) AS RId,
IIF(ISNULL(TABC.rname),"udef",TABC.rname) AS RName,
TABC.gross,TABC.net,TABC.id,
TABB.pid, TABB.handle, TABB.gtype,
TABA.gid, TABA.inode, TABA.x, TABA.y, TABA.z
FROM TABA INNER JOIN (TABB LEFT JOIN TABC ON TABC.fid=TABB.pfid) ON
TABA.gid=TABB.gid
WHERE TABB.mid=19 And TABB.layr="COMMON" AND (TABB.LNSOURCETYPE=1 Or
TABB.LNSOURCETYPE=2)
ORDER BY TABB.gtype, TABB.gid, TABB.norder;
 
Top