By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,462 Members | 2,360 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,462 IT Pros & Developers. It's quick & easy.

Left Join Error

100+
P: 116
i'm working in access2003 trying to bring three large tables(sys_sysobjects, sys_syscolumns, and dbo_pskeydefn) into one table filtered on by fieldname of a fourth table(table_name_master)



Expand|Select|Wrap|Line Numbers
  1. SELECT TABLE_NAME_MASTER.F2 AS SRCE_APP_VERSION,
  2.  TABLE_NAME_MASTER.F3 AS CUST_ID,
  3. sys_sysobjects.name AS TABLE_NAME,
  4. sys_syscolumns.name AS COLUMN_NAME,
  5. sys_syscolumns.colid AS COLUMN_ID,
  6. switch( dbo_PSKEYDEFN.KEYPOSN IS NULL,0,999, dbo_PSKEYDEFN.KEYPOSN) AS KEY_POSITION,
  7. switch(sys_syscolumns.xtype = 48 or 52 or 56 or 127, 'I',
  8. sys_syscolumns.xtype = 62 or 106 or 108,'N',
  9. sys_syscolumns.xtype = 65,'L',
  10. sys_syscolumns.xtype = 61,'D',
  11. sys_syscolumns.xtype = 189,'DT',
  12. sys_syscolumns.xtype = 35 or 167 or 175 or 231 or 239,'C',
  13. 999,'UNKN') AS DATA_TYPE,
  14. switch(sys_syscolumns.xtype = 35,sys_syscolumns.length,
  15. sys_syscolumns.xtype=167 or 175 or 231 or 239,sys_syscolumns.length,
  16. 999,sys_syscolumns.xprec) as DATA_PRECISION
  17. FROM (((TABLE_NAME_MASTER INNER JOIN sys_sysobjects ON TABLE_NAME_MASTER.F1 = sys_sysobjects.name)
  18. INNER JOIN
  19. sys_syscolumns ON sys_sysobjects.id = sys_syscolumns.id)
  20. LEFT JOIN
  21. dbo_PSKEYDEFN ON (sys_syscolumns.name = dbo_PSKEYDEFN.fieldname AND dbo_PSKEYDEFN.INDEXID='_' AND dbo_PSKEYDEFN.RECNAME = MID(sys_sysobjects.name,4,30) ))
  22. 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.fieldname in this section of code.

Expand|Select|Wrap|Line Numbers
  1. LEFT JOIN
  2. dbo_PSKEYDEFN ON (sys_syscolumns.name = dbo_PSKEYDEFN.fieldname AND dbo_PSKEYDEFN.INDEXID='_' AND dbo_PSKEYDEFN.RECNAME = MID(sys_sysobjects.name,4,30) ))
  3. 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
Feb 28 '07 #1
Share this Question
Share on Google+
4 Replies


NeoPa
Expert Mod 15k+
P: 31,186
Eric,
Your problem is that JOINs should follow the format :
Expand|Select|Wrap|Line Numbers
  1. 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?
Feb 28 '07 #2

NeoPa
Expert Mod 15k+
P: 31,186
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.
Feb 28 '07 #3

100+
P: 116
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.
Feb 28 '07 #4

NeoPa
Expert Mod 15k+
P: 31,186
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 :)
Feb 28 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.