conditional update query

[mcnews]

how can i make this query

UPDATE tblCompare INNER JOIN AFRO05_06_IC ON tblCompare.LabID =
AFRO05_06_IC.LabID SET tblCompare.P1_MDG = "POS"
WHERE (((AFRO05_06_IC.P1)="1" And (AFRO05_06_IC.P1) Is Not Null));

let me update
tblCompare.P1_MDG = "NEG"
WHERE (((AFRO05_06_IC.P1)="2" And (AFRO05_06_IC.P1) Is Not Null));

in the same pass?

or can i?

tia,
mcnewsxp

 

[John Spencer]

Try the following.

BACKUP your data first or try it on a copy of the table to make sure.

UPDATE tblCompare INNER JOIN AFRO05_06_IC
ON tblCompare.LabID =AFRO05_06_IC.LabID
SET tblCompare.P1_MDG = IIF( AFRO05_06_IC.P1="1", "POS","NEG")
WHERE AFRO05_06_IC.P1 IN ("1","2")

Note that you don't need to test P1 to see if it is null. If it is null
then it won't be selected due to the first criteria. P1 In ('1","2")
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[mcnews]

UPDATE tblCompare INNER JOIN AFRO05_06_IC
ON tblCompare.LabID =AFRO05_06_IC.LabID
SET tblCompare.P1_MDG = IIF( AFRO05_06_IC.P1="1", "POS","NEG")
WHERE AFRO05_06_IC.P1 IN ("1","2")

very cool.
i wasn't aware of the IN() clause.
thanks.

 

[John Spencer]

Well, it really is just a shorthand way of building multiple Or criteria


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[mcnews]

Well, it really is just a shorthand way of building multiple Or criteria

how would the IN() look if i nested an iif()?

 

[John Spencer]

I'm sorry, I don't understand the question. Can you try to give me an
example?

Do you mean something like a calculated field, such as
IIF( [SomeField] in ("1","2"), True, False)

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[mcnews]

I'm sorry, I don't understand the question. Can you try to give me an
example?

Do you mean something like a calculated field, such as
IIF( [SomeField] in ("1","2"), True, False)

SET tblCompare.P1_MDG = IIF( AFRO05_06_IC.P1="1", "POS", IIF(
AFRO05_06_IC.P1="0","N/A", "NEG"))
WHERE AFRO05_06_IC.P1 IN ("1","2",????)

 

[John Spencer]

The basic structure is
IIF(Condition, True Response, False Response)

You can nest that up to seven times. For instance three conditions could be
nested like the following

IIF(Condition, True Response, IIF(Condition, True Response, IIF(Condition,
True Response, False Response)))

Another option is to use the Switch function which consists of a set of
conditions and responses. The first True returns the corresponding
response.

....
SET tblCompare.P1_MDG =
SWITCH( AFRO05_06_IC.P1="1", "POS",
AFRO05_06_IC.P1="0","N/A",
AFRO05_06_IC.P1 = ""2", "SomeOther",
TRUE, "Neg")

But when something starts getting this complicated, I think you need to
start looking a table that contains the two values in two columns and then
use that table to return the expanded value when it is needed.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I'm sorry, I don't understand the question. Can you try to give me an
example?

Do you mean something like a calculated field, such as
IIF( [SomeField] in ("1","2"), True, False)

SET tblCompare.P1_MDG = IIF( AFRO05_06_IC.P1="1", "POS", IIF(
AFRO05_06_IC.P1="0","N/A", "NEG"))
WHERE AFRO05_06_IC.P1 IN ("1","2",????)