Left Join Error

[phytorion]

i'm working in access2003 trying to bring three large tables(sys_syso bjects, sys_syscolumns, and dbo_pskeydefn) into one table filtered on by fieldname of a fourth table(table_nam e_master)

SELECT TABLE_NAME_MASTER.F2 AS SRCE_APP_VERSION,
 TABLE_NAME_MASTER.F3 AS CUST_ID,
sys_sysobjects.name AS TABLE_NAME,
sys_syscolumns.name AS COLUMN_NAME,
sys_syscolumns.colid AS COLUMN_ID,
switch( dbo_PSKEYDEFN.KEYPOSN IS NULL,0,999, dbo_PSKEYDEFN.KEYPOSN) AS KEY_POSITION,
switch(sys_syscolumns.xtype = 48 or 52 or 56 or 127, 'I',
sys_syscolumns.xtype = 62 or 106 or 108,'N',
sys_syscolumns.xtype = 65,'L',
sys_syscolumns.xtype = 61,'D',
sys_syscolumns.xtype = 189,'DT',
sys_syscolumns.xtype = 35 or 167 or 175 or 231 or 239,'C',
999,'UNKN') AS DATA_TYPE,
switch(sys_syscolumns.xtype = 35,sys_syscolumns.length,
sys_syscolumns.xtype=167 or 175 or 231 or 239,sys_syscolumns.length,
999,sys_syscolumns.xprec) as DATA_PRECISION
FROM (((TABLE_NAME_MASTER INNER JOIN sys_sysobjects ON TABLE_NAME_MASTER.F1 = sys_sysobjects.name)
INNER JOIN
sys_syscolumns ON sys_sysobjects.id = sys_syscolumns.id)
LEFT JOIN
dbo_PSKEYDEFN ON (sys_syscolumns.name = dbo_PSKEYDEFN.fieldname AND dbo_PSKEYDEFN.INDEXID='_' AND dbo_PSKEYDEFN.RECNAME = MID(sys_sysobjects.name,4,30) ))
ORDER BY sys_sysobjects.name;

The major chunk of the code runs fine but access gives me the error join expression not supported when i try to add in sys_syscolumns. name = dbo_PSKEYDEFN.f ieldname in this section of code.

LEFT JOIN
dbo_PSKEYDEFN ON (sys_syscolumns.name = dbo_PSKEYDEFN.fieldname AND dbo_PSKEYDEFN.INDEXID='_' AND dbo_PSKEYDEFN.RECNAME = MID(sys_sysobjects.name,4,30) ))
ORDER BY sys_sysobjects.name;

Any help you could give me would be most appreciated. I started working with Access last week and have just been learning as i go.

Eric

[NeoPa]

Eric,
Your problem is that JOINs should follow the format :

TX JOIN TY ON TX.FieldA = TY.FieldB AND TX.FieldC = TY.FieldD

Other restrictions applied should be in a WHERE clause. Does this make sense to you?

[NeoPa]

Just to clarify, Functions which take fields from one of the input sources do not qualify, nor do literal values.
Only the fields defined in the record sources can be used. The rest belongs in a WHERE clause.
You may find it easier to design this first in the design window. This will automatically restrict you from doing invalid things.

[phytorion]

Just to clarify, Functions which take fields from one of the input sources do not qualify, nor do literal values.
Only the fields defined in the record sources can be used. The rest belongs in a WHERE clause.
You may find it easier to design this first in the design window. This will automatically restrict you from doing invalid things.

Yeah i finally got it to work. its definitly picky about what it allows you to do =Þ. Thanks for the help.

[NeoPa]

The restrictions make sense though.
What you were trying to do indicated a lack of understanding of what is actually going on. You're better off that it tells you now rather than allowing further confusion.
Glad you got it sorted though :)