MS Access Import data issue

[#1]

hi all,

i am importting 2 tables from same MS access and doing left join and using iF statement , but giving error. see below syntax:

ODBC CONNECT To [MS Access Database; DBQ=path];

raw:

select

category,

[country of issue],

[Coll Type],

[buss code],

if([buss code]='ufu', 'Europe',if([buss code]='afu','Asia','')) as Region

from table1;

left join

select

category,

value

from table2;

Gives error on:

-If statement syntax

-left join as ODBC Failed

please advise

thanks

[m_woolf]

The IF is QlikView syntax and can't be used inside a SQL Select statement.

Do a preceding load and use the IF there.

[#3]

as i mentioned the import is from MS Acess, thus Load will not work. i have to use Select

please advise

[m_woolf · Accepted solution]

Something like this:

raw:

load

     *,

if([buss code]='ufu', 'Europe',if([buss code]='afu','Asia','')) as Region;

select

category,

[country of issue],

[Coll Type],

[buss code],

from table1;

[Gysbert_Wassenaar]

MS-Access has the iif construct. Maybe this works:

     iif([buss code]='ufu', 'Europe',iif([buss code]='afu','Asia','')) as Region.

It's possible you need to use double quotes instead of single quotes.

Alternatively you can use a preceding load:

Table1

Load

     category,[country of issue],[Coll Type],[buss code],

     if([buss code]='ufu', 'Europe',if([buss code]='afu','Asia','')) as Region;

Select category,[country of issue],[Coll Type],[buss code]

from table1;

Why the left join fails is impossible to tell without more information. Do you also get an error if you load table two as a table like this:

Table2:

select category, value from table2;