sql - using IIF function one parameter / field to be displayed basis combination of Multiple fields -

sql - using IIF function one parameter / field to be displayed basis combination of Multiple fields -

i need help on ms access. below 3 tables used.

logic:

i need query should fetch vatrate available in vat_inclusion basis combination of gl, branch , domicile (in same table). example:

vat_inclusion branch|local_gl_start|local_gl_end|domicile|vatrate ------+--------------+------------+--------+------- 21 |40000 |50000 |uk |9

details of domicile: fetched basis local client id grt table , local client id in domicile table. grt not contain domicile domicile does.

for reference have prepared query (given below @ end) facing issues.

output in grt table format:

branch|local_gl| local_cust_id|vat rate ------+--------+--------------+-------- 21 | 40010 | cust_223 |9

the output should have info grt contains branch 21 , local gl between range given in vat_inclusion (here 40010).

grt table (source /raw file)

branch|local_gl| local_cust_id

vat_inclusion table: gl range selected

branch|local_gl_start|local_gl_end|domicile|vatrate

domicile table

local_cust_id|domicile

select grt.*,iif(grt.branch=vat_inclusion.branch) , (grt.local_gl between at_inclusion.local_gl_start , at_inclusion.local_gl_start) , (vat_inclusion.domicile=domicile_out.domicile),vat_inclusion.vatrate, "7") vat_inclusion, grt, (select grt.local_cust_id, domicile.local_cust_id, domicile.domicile grt, domicile grt.local_cust_id=domicile.local_cust_id) domicile_out grt.branch=vat_inclusion.branch , grt.local_gl between at_inclusion.local_gl_start , at_inclusion.local_gl_start , vat_inclusion.domicile=domicile_out.domicile

request help in resolving issue. not showing vat rate desired. showing 7 vat rate all.

your sql had couple spelling mistakes (at instead of vat), , ")' before 'and' needs removed. seek following:

select grt.*, iif([grt].[branch]=[vat_inclusion].[branch] , ([grt].[local_gl] between [vat_inclusion].[local_gl_start] , [vat_inclusion].[local_gl_start]) , ([vat_inclusion].[domicile]=[domicile_out].[domicile]),[vat_inclusion].[vatrate],"7") expr1 vat_inclusion, grt, (select grt.local_cust_id, domicile.local_cust_id, domicile.domicile grt, domicile grt.local_cust_id=domicile.local_cust_id) domicile_out (((grt.branch)=[vat_inclusion].[branch]) , ((grt.local_gl) between [vat_inclusion].[local_gl_start] , [vat_inclusion].[local_gl_start]) , ((vat_inclusion.domicile)=[domicile_out].[domicile]));

sql ms-access