How to do Multiple update in one query

[bird lover]

I use access 2003. Field [County] has different data, ie. Kings, Queens,
Bronx, Manhattan and Brooklyn. (yes, I'm from Brooklyn). Field [claim no]
has varied data.
In one update query, can I create five update fields, ie.

field [Claim no] [claim No]
[claim no]
update to Bk &" " &[claim No] Qu & " "&[claim no] Bx&"
"&[claim no]
Criteria [county]!Brooklyn [county]!Queens
[county]!Bronx

q1. Can I put multiple updates in one query, with each column having
separate criteria. (I would prefer not having to make five separate queries
if I can do it in one)
Q2. What is the correct syntax for Criteria, I tried [county]!"kings" but
get an error.

 

[Douglas J. Steele]

One approach would be to use the Switch function:

UPDATE MyTable
SET [ClaimNo] = Switch([County] = "Brooklyn", "BK", [County] = "Queens",
"QU", [County] = "Kings", "KI", [County] = "Bronx", "BX", [County] =
"Manhattan", "MA") & [ClaimNo]

 

[bird lover]

I keep getting zeros in field [claim no]. My procedure is make an update
query, with field equal claim no; . In the update section, I put "SET
[ClaimNo]"=Switch([County]="Queens","QU",[County]="Kings","KI",[County]="Bronx","BX",[County]="Manhattan","MA") & [ClaimNo]
It does not reject, but simply puts zeros in claim no.

One approach would be to use the Switch function:

UPDATE MyTable
SET [ClaimNo] = Switch([County] = "Brooklyn", "BK", [County] = "Queens",
"QU", [County] = "Kings", "KI", [County] = "Bronx", "BX", [County] =
"Manhattan", "MA") & [ClaimNo]

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I use access 2003. Field [County] has different data, ie. Kings, Queens,
Bronx, Manhattan and Brooklyn. (yes, I'm from Brooklyn). Field [claim
no]
has varied data.
In one update query, can I create five update fields, ie.

field [Claim no] [claim No]
[claim no]
update to Bk &" " &[claim No] Qu & " "&[claim no] Bx&"
"&[claim no]
Criteria [county]!Brooklyn [county]!Queens
[county]!Bronx

q1. Can I put multiple updates in one query, with each column having
separate criteria. (I would prefer not having to make five separate
queries
if I can do it in one)
Q2. What is the correct syntax for Criteria, I tried [county]!"kings"
but
get an error.

 

[Douglas J. Steele]

Sorry, what I gave you is the actual SQL that Access would generate.

To create the query using the query builder, you'd put

Switch([County] = "Brooklyn", "BK", [County] = "Queens", "QU", [County] =
"Kings", "KI", [County] = "Bronx", "BX", [County] = "Manhattan", "MA") &
[ClaimNo]

in the Update To row under the ClaimNo field.


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I keep getting zeros in field [claim no]. My procedure is make an update
query, with field equal claim no; . In the update section, I put "SET
[ClaimNo]"=Switch([County]="Queens","QU",[County]="Kings","KI",[County]="Bronx","BX",[County]="Manhattan","MA")
& [ClaimNo]
It does not reject, but simply puts zeros in claim no.

One approach would be to use the Switch function:

UPDATE MyTable
SET [ClaimNo] = Switch([County] = "Brooklyn", "BK", [County] = "Queens",
"QU", [County] = "Kings", "KI", [County] = "Bronx", "BX", [County] =
"Manhattan", "MA") & [ClaimNo]

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I use access 2003. Field [County] has different data, ie. Kings,
Queens,
Bronx, Manhattan and Brooklyn. (yes, I'm from Brooklyn). Field [claim
no]
has varied data.
In one update query, can I create five update fields, ie.

field [Claim no] [claim No]
[claim no]
update to Bk &" " &[claim No] Qu & " "&[claim no]
Bx&"
"&[claim no]
Criteria [county]!Brooklyn [county]!Queens
[county]!Bronx

q1. Can I put multiple updates in one query, with each column having
separate criteria. (I would prefer not having to make five separate
queries
if I can do it in one)
Q2. What is the correct syntax for Criteria, I tried [county]!"kings"
but
get an error.