 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / November 2006AND IF formula in Access Query
I'm trying to do this formula in access query as a expression to create a new field and having hard time, can anyone help me please? IF[dbo.NTH_ACTIVITY_DEFINITION].ACTIVITY_DEF_DID=[dbo.NTH_ACTIVITY_DEFINITION].ACTIVITY_DEF_DID AND [dbo_NTH_RPT_CONTROLS].CONTROLS_DID=[dbo_NTH_RPT_CONTROLS].CONTROLS_DID AND [dbo_NTH_RPT_CTRL_TESTINGS].STEP_INSTANCE_ID not equal to [dbo_NTH_RPT_CTRL_TESTINGS].STEP_INSTANCE_ID) then Take the first instance of [dbo_NTH_RPT_CTRL_TESTINGS].STEP_INSTANCE_ID, otherwise [dbo_NTH_RPT_CTRL_TESTINGS].STEP_INSTANCE_ID) To me, what you have here makes no sense. Something is wrong: Your first comparison compares the same thing. Since [dbo.NTH_ACTIVITY_DEFINITION].ACTIVITY_DEF_DID must equal itself, IF[dbo.NTH_ACTIVITY_DEFINITION].ACTIVITY_DEF_DID=[dbo.NTH_ACTIVITY_DEFINITION].ACTIVITY_DEF_DID is always going to be true. Your next comparison [dbo_NTH_RPT_CONTROLS].CONTROLS_DID=[dbo_NTH_RPT_CONTROLS].CONTROLS_DID has the same problem. It will also always be true. Your third comparision [dbo_NTH_RPT_CTRL_TESTINGS].STEP_INSTANCE_ID not equal to [dbo_NTH_RPT_CTRL_TESTINGS].STEP_INSTANCE_ID) will never be true. Not sure what you are trying to do. > I'm trying to do this formula in access query as a expression to create > a new field and having hard time, can anyone help me please? [quoted text clipped - 10 lines] > [dbo_NTH_RPT_CTRL_TESTINGS].STEP_INSTANCE_ID, otherwise > [dbo_NTH_RPT_CTRL_TESTINGS].STEP_INSTANCE_ID) Already responded to in another newsgroup. If you must post the same question to more than one 'group, put all of the newsgroups you are posting to in the "To:" field. That way, a response in one newsgroup shows up in all. Regards Jeff Boyce Microsoft Office/Access MVP I'm trying to do this formula in access query as a expression to create a new field and having hard time, can anyone help me please? IF[dbo.NTH_ACTIVITY_DEFINITION].ACTIVITY_DEF_DID=[dbo.NTH_ACTIVITY_DEFINITION].ACTIVITY_DEF_DID AND [dbo_NTH_RPT_CONTROLS].CONTROLS_DID=[dbo_NTH_RPT_CONTROLS].CONTROLS_DID AND [dbo_NTH_RPT_CTRL_TESTINGS].STEP_INSTANCE_ID not equal to [dbo_NTH_RPT_CTRL_TESTINGS].STEP_INSTANCE_ID) then Take the first instance of [dbo_NTH_RPT_CTRL_TESTINGS].STEP_INSTANCE_ID, otherwise [dbo_NTH_RPT_CTRL_TESTINGS].STEP_INSTANCE_ID) Good Morning and thanks everyone, below is a real life example with what I'm trying to do: ACTIVITY_DEF_DID CONTROLS_DID STEP_INSTANCE_ID CONTROL_RESULT 10013 A 100 Effective 10045 B 215 Ineffective 10045 B 419 Ineffective 10045 B 500 Effective 10045 D 515 Effective What I'm trying to do is to create two other column: a. Initial CONTROL_RESULT :(where the logic has to be ACTIVITY_DEF_DID=ACTIVITY_DEF_DID and CONTROLS_DID=CONTROLS_DID and STEP_INSTANCE_ID is not equal STEP_INSTANCE_ID then take first CONTROL_RESULT i.e (first occurance for that Activity) otherwise just take the CONTROL_RESULT b.Retest CONTROL_RESULT: rest of the logic will be same but instead of first occurance of the CONTROL_RESULT it has to be last occurance of the CONTROL_RESULT or leave it NULL The result should look like this: ACTIVITY_DEF_DID CONTROLS_DID STEP_INSTANCE_ID Intl CONTROL_RESULT Rtest_CONTROL_RESULT 10013 A 100 Effective 10045 B 215 Ineffective Effective 10045 D 515 Effective Hope this helps. > Already responded to in another newsgroup. If you must post the same > question to more than one 'group, put all of the newsgroups you are posting [quoted text clipped - 20 lines] > [dbo_NTH_RPT_CTRL_TESTINGS].STEP_INSTANCE_ID, otherwise > [dbo_NTH_RPT_CTRL_TESTINGS].STEP_INSTANCE_ID) > Good Morning and thanks everyone, below is a real life example with > what I'm trying to do: [quoted text clipped - 50 lines] > > [dbo_NTH_RPT_CTRL_TESTINGS].STEP_INSTANCE_ID, otherwise > > [dbo_NTH_RPT_CTRL_TESTINGS].STEP_INSTANCE_ID) You are describing using the "first" and the "last" occurance of some item. Access stores data in an internally-determined sequence. Unless you specify how to determine "first" and "last", Access will decide, and it will only rarely match what you expected. How are YOU determining "first" and "last"? Regards Jeff Boyce Microsoft Office/Access MVP > Good Morning and thanks everyone, below is a real life example with > what I'm trying to do: [quoted text clipped - 56 lines] > > [dbo_NTH_RPT_CTRL_TESTINGS].STEP_INSTANCE_ID, otherwise > > [dbo_NTH_RPT_CTRL_TESTINGS].STEP_INSTANCE_ID) Jeff, This is where I'm having problem, in these scenerio the STEP_Instance_ID will be the determining factor, like Lower value will be the initial I think in crystal "X" resembles the initial but not sure in Access. Like if we have 4 data how in access I can ask to give the 1st and the last in formula? > You are describing using the "first" and the "last" occurance of some item. > Access stores data in an internally-determined sequence. Unless you specify [quoted text clipped - 68 lines] > > > [dbo_NTH_RPT_CTRL_TESTINGS].STEP_INSTANCE_ID, otherwise > > > [dbo_NTH_RPT_CTRL_TESTINGS].STEP_INSTANCE_ID) Again, "first" and "last" have meaning to you, because you have decided in which order you are sorting the items. But Access doesn't know that unless you specify sorting in THAT order. What are you using to sort? Regards Jeff Boyce Microsoft Office/Access MVP Jeff, This is where I'm having problem, in these scenerio the STEP_Instance_ID will be the determining factor, like Lower value will be the initial I think in crystal "X" resembles the initial but not sure in Access. Like if we have 4 data how in access I can ask to give the 1st and the last in formula? Jeff Boyce wrote: > You are describing using the "first" and the "last" occurance of some > item. [quoted text clipped - 74 lines] > > > [dbo_NTH_RPT_CTRL_TESTINGS].STEP_INSTANCE_ID, otherwise > > > [dbo_NTH_RPT_CTRL_TESTINGS].STEP_INSTANCE_ID) |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.