Multiple IIfs (buts and maybes)

[BarryNeaves]

I am trying to write an sql statement in Access 97 to check two identical fields in separate tables and responding from a different table if Null, or from possibly two tables if true.

My crap code currently looks like this...

IIf([adult_education_extract]![Title]= " " or [casual_supply_extract]![title]=" ",[payroll_data_extract]![title],([title]![title_code] or [title_1]![title_code])) AS Title

I am looking at Title in Adult Ed and Casual Supply, if entry in there, return mapping code from Title or Title_1, if not return entry from payroll extract. Make sense? sounded better in my head....

Not pretty or working... It currently returns some answers (-1), although incorrect...

My joins are as follows:

FROM ((((payroll_data_extract LEFT JOIN Casual_Supply_extract ON payroll_data_extract.[NI Number] = Casual_Supply_extract.Nino) LEFT JOIN adult_education_extract ON payroll_data_extract.[NI Number] = adult_education_extract.NINUMB) LEFT JOIN Title ON Casual_Supply_extract.title = Title.description) LEFT JOIN Title AS Title_1 ON adult_education_extract.TITLE = Title_1.description);

I have tried using a single "Title mapping table" but get the error:
"The SQL statment could not be executed because it contains ambiguous outer joins. To force one of the joins to be performed first, create a separate query that performs the first join and then include the query in your sql statment."

Can anyone help??


Kind regards.

Barry Neaves

 

[ecniv]

I am trying to write an sql statement in Access 97 to check two identical fields in separate tables and responding from a different table if Null, or from possibly two tables if true.

My crap code currently looks like this...

IIf([adult_education_extract]![Title]= " " or [casual_supply_extract]![title]=" ",[payroll_data_extract]![title],([title]![title_code] or [title_1]![title_code])) AS Title

This translates to:
If adult education = " " or Casual = " "
Then use the payroll title
Else (title or title1) <<< this is where the -1 is coming from.

My joins are as follows:

FROM ((((payroll_data_extract LEFT JOIN Casual_Supply_extract ON payroll_data_extract.[NI Number] = Casual_Supply_extract.Nino) LEFT JOIN adult_education_extract ON payroll_data_extract.[NI Number] = adult_education_extract.NINUMB) LEFT JOIN Title ON Casual_Supply_extract.title = Title.description) LEFT JOIN Title AS Title_1 ON adult_education_extract.TITLE = Title_1.description);

Can anyone help??

Kind regards.

Barry Neaves

IIf(nz(len([adult_education_extract].[Title],1)<2 or nz(len([casual_supply_extract].[title],1)<2,[payroll_data_extract].[title],iif(nz(len([title].[title_code],1)<2,[title_1].[title_code],[title].[title_code])) AS Title

I think that will do what you want... I've put in nz(field,1)<2 'cause you put title=" " (which is length of 1).

As I put up above, I dunno why you have title and title_1 tables in twice, especially if they are the same table, but I guess that is in the joins (which I haven't checked).

Hope this helps towards your goal.


Vince

 

[BarryNeaves]

Thanks for your response...

I have tried using a single "Title mapping table" but get the error:
"The SQL statment could not be executed because it contains ambiguous outer joins. To force one of the joins to be performed first, create a separate query that performs the first join and then include the query in your sql statment."

Maybe it is the way I created the joins, I don't know.

Any thoughts??

Barry Neaves

 

[ecniv]

FROM ((((
payroll_data_extract LEFT JOIN Casual_Supply_extract ON payroll_data_extract.[NI Number] = Casual_Supply_extract.Nino)
LEFT JOIN adult_education_extract ON payroll_data_extract.[NI Number] = adult_education_extract.NINUMB)
LEFT JOIN Title ON Casual_Supply_extract.title = Title.description)
LEFT JOIN Title AS Title_1 ON adult_education_extract.TITLE = Title_1.description)

OK.
Hmmm.
Now in the links you are joining to the title table via the full name/description, but you want the ID/code field returned.

As the Payroll table is on the left most of all the joins, wouldn't this have the title code you need?
Otherwise, which is more prominant, the casual or the adult ed?

if the payroll has the code, why look in other tables?
If its empty, I guess that there isn't a persons details table which links to the title?
If there ain't then the title and title_1 are the best ways, but perhaps a rename to cstitle (table linked to the casual supply) and adtitle (table linked to the adult ed) and if payroll has the code, use that or another aliased table of prtable to get the code.


Vince