AND IF formula in Access Query

  • Thread starter Thread starter sadkadir
  • Start date Start date
S

sadkadir

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_DEFINITI­ON].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_DEFINITI­ON].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.
 
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_DEFINITI­ON].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.
 
Good Morning and thanks everyone, below is a real life example with
what I'm trying to do:

ACT_DEF_DID CTRL_DID STEP_INS_ID CTRL_RSLT
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. Intl CTRL_RSLT :(where the logic has to be
ACT_DEF_DID=ACT_DEF_DID and CTRL_DID=CTRL_DID and
STEP_INS_ID is not equal STEP_INS_ID then take first
CTRL_RSLT i.e (first occurance for that Activity) otherwise just
take the CTRL_RSLT
b.Rtest_CTRL_RSLT: rest of the logic will be same but instead of
first occurance of the CTRL_RSLT it has to be last occurance of
the CTRL_RSLT or leave it null

The result should look like this:

ACT_DEF_DID CTRL_DID STEP_INS_ID Intl CTRL_RSLT Rtest_CTRL_RSLT
10013 A 100 Effective
10045 B 215 neffective Effective
10045 D 515 Effective

Hope this helps.

Jeff said:
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_DEFINITI­ON].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)
Click to expand...
Click to expand...
 
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:

ACT_DEF_DID CTRL_DID STEP_INS_ID CTRL_RSLT
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. Intl CTRL_RSLT :(where the logic has to be
ACT_DEF_DID=ACT_DEF_DID and CTRL_DID=CTRL_DID and
STEP_INS_ID is not equal STEP_INS_ID then take first
CTRL_RSLT i.e (first occurance for that Activity) otherwise just
take the CTRL_RSLT
b.Rtest_CTRL_RSLT: rest of the logic will be same but instead of
first occurance of the CTRL_RSLT it has to be last occurance of
the CTRL_RSLT or leave it null

The result should look like this:

ACT_DEF_DID CTRL_DID STEP_INS_ID Intl CTRL_RSLT Rtest_CTRL_RSLT
10013 A 100 Effective
10045 B 215 neffective
Effective
10045 D 515 Effective

Hope this helps.

Jeff said:
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_DEFINITI­ON].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)
Click to expand...
Click to expand...
 
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 said:
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:

ACT_DEF_DID CTRL_DID STEP_INS_ID CTRL_RSLT
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. Intl CTRL_RSLT :(where the logic has to be
ACT_DEF_DID=ACT_DEF_DID and CTRL_DID=CTRL_DID and
STEP_INS_ID is not equal STEP_INS_ID then take first
CTRL_RSLT i.e (first occurance for that Activity) otherwise just
take the CTRL_RSLT
b.Rtest_CTRL_RSLT: rest of the logic will be same but instead of
first occurance of the CTRL_RSLT it has to be last occurance of
the CTRL_RSLT or leave it null

The result should look like this:

ACT_DEF_DID CTRL_DID STEP_INS_ID Intl CTRL_RSLT Rtest_CTRL_RSLT
10013 A 100 Effective
10045 B 215 neffective
Effective
10045 D 515 Effective

Hope this helps.

Jeff said:
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_DEFINITI­ON].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)
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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:

ACT_DEF_DID CTRL_DID STEP_INS_ID CTRL_RSLT
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. Intl CTRL_RSLT :(where the logic has to be
ACT_DEF_DID=ACT_DEF_DID and CTRL_DID=CTRL_DID and
STEP_INS_ID is not equal STEP_INS_ID then take first
CTRL_RSLT i.e (first occurance for that Activity) otherwise just
take the CTRL_RSLT
b.Rtest_CTRL_RSLT: rest of the logic will be same but instead of
first occurance of the CTRL_RSLT it has to be last occurance of
the CTRL_RSLT or leave it null

The result should look like this:

ACT_DEF_DID CTRL_DID STEP_INS_ID Intl CTRL_RSLT Rtest_CTRL_RSLT
10013 A 100 Effective
10045 B 215 neffective
Effective
10045 D 515 Effective

Hope this helps.

Jeff said:
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_DEFINITI­ON].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)
Click to expand...
Click to expand...
Click to expand...
 
Back
Top