Help on IIF Statement... please....

[bluefalcon904]

HI ... Can you also help? Im such a newbie in Ms Access and I need desperate
help.

Can you please help me translate below SQL statement into an IIF statement?

Select
A.SecIDFlag, A.SecID as MasterSecID,
case when A.SecIDFlag = 1 then
(select top 1 SecID from SecRefCode where RefCodeType='12' and
RefCode=A.Secid)
when A.SecIDFlag = 8 then
(select top 1 SecID from SecRefCode where RefCodeType='06' and
RefCode=A.Secid)
when A.SecIDFlag = 30 then
(select top 1 SecID from SecRefCode where RefCodeType='08' and
RefCode=A.Secid)
else null
end as NewSecID
from SecMaster A

Im not so familar on how to use the functions/module in Ms Access.

Hope you can help.
Thanks

 

[bluefalcon904 via AccessMonster.com]

Can some one please help??

HI ... Can you also help? Im such a newbie in Ms Access and I need desperate
help.

Can you please help me translate below SQL statement into an IIF statement?

Select
A.SecIDFlag, A.SecID as MasterSecID,
case when A.SecIDFlag = 1 then
(select top 1 SecID from SecRefCode where RefCodeType='12' and
RefCode=A.Secid)
when A.SecIDFlag = 8 then
(select top 1 SecID from SecRefCode where RefCodeType='06' and
RefCode=A.Secid)
when A.SecIDFlag = 30 then
(select top 1 SecID from SecRefCode where RefCodeType='08' and
RefCode=A.Secid)
else null
end as NewSecID
from SecMaster A

Im not so familar on how to use the functions/module in Ms Access.

Hope you can help.
Thanks

 

[Duane Hookom]

I think you need to pull the logic out of the expressions and put it into
the tables and data. Can't you create a join on SecMaster.SecID to
SecRefCode.RefCode and SecIDFlag to a field in SecRefCode?

 

[bluefalcon904 via AccessMonster.com]

Hi Duane,

I tried doing dobule Inner Join but keeps failing.
Im not sure where Im going...

I think you need to pull the logic out of the expressions and put it into
the tables and data. Can't you create a join on SecMaster.SecID to
SecRefCode.RefCode and SecIDFlag to a field in SecRefCode?

HI ... Can you also help? Im such a newbie in Ms Access and I need
desperate

[quoted text clipped - 22 lines]

Hope you can help.
Thanks

 

[Duane Hookom]

Are you storing the values 1, 8, 30 ,.... in your SecRefCode table?

--
Duane Hookom
MS Access MVP

Hi Duane,

I tried doing dobule Inner Join but keeps failing.
Im not sure where Im going...

I think you need to pull the logic out of the expressions and put it into
the tables and data. Can't you create a join on SecMaster.SecID to
SecRefCode.RefCode and SecIDFlag to a field in SecRefCode?

HI ... Can you also help? Im such a newbie in Ms Access and I need
desperate

[quoted text clipped - 22 lines]

Hope you can help.
Thanks

 

[bluefalcon904 via AccessMonster.com]

hi duane..

yes i am. one more thing.. secid in SecRefcode table is unique, however not
Refcode.
thank you

Are you storing the values 1, 8, 30 ,.... in your SecRefCode table?

Hi Duane,

[quoted text clipped - 10 lines]

 

[Duane Hookom]

I was thinking that you could use a join between the SecMaster and
SecRefCode tables without using subqueries.

--
Duane Hookom
MS Access MVP

hi duane..

yes i am. one more thing.. secid in SecRefcode table is unique, however
not
Refcode.
thank you

Are you storing the values 1, 8, 30 ,.... in your SecRefCode table?

Hi Duane,

[quoted text clipped - 10 lines]

Hope you can help.
Thanks

 

[bluefalcon904 via AccessMonster.com]

ill try to do that then... but how do you multiple join?

I was thinking that you could use a join between the SecMaster and
SecRefCode tables without using subqueries.

hi duane..

[quoted text clipped - 10 lines]

 

[Duane Hookom]

Your query can join on multiple tables. If you can't figure this out, come
back with your table structures and relationships as well as your desired
outcome.

--
Duane Hookom
MS Access MVP

ill try to do that then... but how do you multiple join?

I was thinking that you could use a join between the SecMaster and
SecRefCode tables without using subqueries.

hi duane..

[quoted text clipped - 10 lines]

Hope you can help.
Thanks

 

[bluefalcon904 via AccessMonster.com]

HI Duane,

Will do that then. Thank you so much.

Your query can join on multiple tables. If you can't figure this out, come
back with your table structures and relationships as well as your desired
outcome.

ill try to do that then... but how do you multiple join?

[quoted text clipped - 6 lines]