Help with update query and IIf statement

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have 6 fields,
[Item#],[prcmp_code],[comp_grp_cd],[comp_grp_val],[billing],[result] (see
datatype below)
I need help with writing a iif statement to update my table results field.
What I need is, If [billing] = “n†and [comp_grp_cd] is > 1 (has more than
one incidence) and the Item# is the same then update [results] = "non
exception"
Thanks

[Item] [prcmp_code] [comp_grp_cd] [comp_grp_val]
[billing] [result]
201-10068302 IXCP1 EQ 82281 b
201-10068302 TSP MRC LI 7065261 n
201-10068302 MULTI LI 7065261 b
201-10068302 CPA DED LL 82283 n
201-10068302 PRI BER DX LL 9081026 b
201-10068302 MULTI LL 8045602 b
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

What do you mean "the Item# is the same"? The same as what?

What do you mean by "[comp_grp_cd] is > 1"? In your example data that
column holds a string ("LI", "EQ", "LL"). Do you mean the count of the
code per Item#?

What's the Primary Key of this table?

You don't need an IIf() function in the SET clause, you need a good
WHERE clause.

Try this (just a guess):

UPDATE tableA
SET result = 'non-exception'
WHERE billing = 'n'
AND EXISTS (SELECT A.comp_grp_cd FROM tableA As A
WHERE A.[Item#] = tableA.[Item#]
GROUP BY A.comp_grp_cd
HAVING COUNT(*) > 1)

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQmVAk4echKqOuFEgEQJTuwCfVSS3WtRQHqkEHS6MiDSAxV8TZREAoOkc
BCgJNWatQff11d/bWZ5u/Ch9
=a2xJ
-----END PGP SIGNATURE-----
 
Hi MG,
I tired the example you gave but it update "0" records. I think it is on
the right track of what I am trying to accomplish.
The Item is a one to many field, you can have it listed more than once with
all the other field having different values.
Yes, the [comp_grp_cd] is a string and you can have "LL" listed 3 times once
being "b"(billing) and "n"(non-billing), so this would have a count of 3 and
i need the "n" result updated to "non-exception"
I hope the example below helps..

[Item#][prcmp_code] [comp_grp_cd][comp_grp_val][billing][result]
123 IXCP1 EQ 1111 b
123 TSP MRC LI 1111 n
123 MULTI LI 1111 b
123 CPA DED LL 1111 n
123 PRI BER DX LL 1111 b
123 MULTI LL 1111 n


MGFoster said:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

What do you mean "the Item# is the same"? The same as what?

What do you mean by "[comp_grp_cd] is > 1"? In your example data that
column holds a string ("LI", "EQ", "LL"). Do you mean the count of the
code per Item#?

What's the Primary Key of this table?

You don't need an IIf() function in the SET clause, you need a good
WHERE clause.

Try this (just a guess):

UPDATE tableA
SET result = 'non-exception'
WHERE billing = 'n'
AND EXISTS (SELECT A.comp_grp_cd FROM tableA As A
WHERE A.[Item#] = tableA.[Item#]
GROUP BY A.comp_grp_cd
HAVING COUNT(*) > 1)

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQmVAk4echKqOuFEgEQJTuwCfVSS3WtRQHqkEHS6MiDSAxV8TZREAoOkc
BCgJNWatQff11d/bWZ5u/Ch9
=a2xJ
-----END PGP SIGNATURE-----
I have 6 fields,
[Item#],[prcmp_code],[comp_grp_cd],[comp_grp_val],[billing],[result] (see
datatype below)
I need help with writing a iif statement to update my table results field.
What I need is, If [billing] = “n†and [comp_grp_cd] is > 1 (has more than
one incidence) and the Item# is the same then update [results] = "non
exception"
Thanks

[Item] [prcmp_code] [comp_grp_cd] [comp_grp_val]
[billing] [result]
201-10068302 IXCP1 EQ 82281 b
201-10068302 TSP MRC LI 7065261 n
201-10068302 MULTI LI 7065261 b
201-10068302 CPA DED LL 82283 n
201-10068302 PRI BER DX LL 9081026 b
201-10068302 MULTI LL 8045602 b
Click to expand...
Click to expand...
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

It worked for me: I made a test table using the data you provided in
the first post & my UPDATE worked as you specified - updated 3 rows,
those that had billing equal to 'n' and more than one comp_grp_cd for
the same Item#.

Something different is happening on your side.

My side: Access 2002 using ANSI SQL-92 option on WinXP Pro box.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQmVNEIechKqOuFEgEQKNPwCgs1Tdfh0BV0OUQQzFZ/FxR3lAeRkAn3An
bgoL8dZhNEV0evW524TpgsBa
=bY+r
-----END PGP SIGNATURE-----
Hi MG,
I tired the example you gave but it update "0" records. I think it is on
the right track of what I am trying to accomplish.
The Item is a one to many field, you can have it listed more than once with
all the other field having different values.
Yes, the [comp_grp_cd] is a string and you can have "LL" listed 3 times once
being "b"(billing) and "n"(non-billing), so this would have a count of 3 and
i need the "n" result updated to "non-exception"
I hope the example below helps..

[Item#][prcmp_code] [comp_grp_cd][comp_grp_val][billing][result]
123 IXCP1 EQ 1111 b
123 TSP MRC LI 1111 n
123 MULTI LI 1111 b
123 CPA DED LL 1111 n
123 PRI BER DX LL 1111 b
123 MULTI LL 1111 n


:

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

What do you mean "the Item# is the same"? The same as what?

What do you mean by "[comp_grp_cd] is > 1"? In your example data that
column holds a string ("LI", "EQ", "LL"). Do you mean the count of the
code per Item#?

What's the Primary Key of this table?

You don't need an IIf() function in the SET clause, you need a good
WHERE clause.

Try this (just a guess):

UPDATE tableA
SET result = 'non-exception'
WHERE billing = 'n'
AND EXISTS (SELECT A.comp_grp_cd FROM tableA As A
WHERE A.[Item#] = tableA.[Item#]
GROUP BY A.comp_grp_cd
HAVING COUNT(*) > 1)

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQmVAk4echKqOuFEgEQJTuwCfVSS3WtRQHqkEHS6MiDSAxV8TZREAoOkc
BCgJNWatQff11d/bWZ5u/Ch9
=a2xJ
-----END PGP SIGNATURE-----
I have 6 fields,
[Item#],[prcmp_code],[comp_grp_cd],[comp_grp_val],[billing],[result] (see
datatype below)
I need help with writing a iif statement to update my table results field.
What I need is, If [billing] = “n†and [comp_grp_cd] is > 1 (has more than
one incidence) and the Item# is the same then update [results] = "non
exception"
Thanks

[Item] [prcmp_code] [comp_grp_cd] [comp_grp_val]
[billing] [result]
201-10068302 IXCP1 EQ 82281 b
201-10068302 TSP MRC LI 7065261 n
201-10068302 MULTI LI 7065261 b
201-10068302 CPA DED LL 82283 n
201-10068302 PRI BER DX LL 9081026 b
201-10068302 MULTI LL 8045602 b
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Need help with an Update query statement ... 4

Back
Top