UPDATE query - Macros??? Visual Basic??? SQL??? Help!

[Student Databaser]

Ok so i need to populate a field [Work] with the contents of two other fields
in this table [ORGANIZATION_TYPE] and [UNDERSERVED_AREA].

Essentially this is what i need it do do, however i know i can't combine all
these together. I probably need to write some code for it, but i've never
written any codes other than a little SQL. . . HELP!

UPDATE DEMOGRAPHIC
SET Work = 1 WHERE ORGANIZATION_TYPE = 2
SET Work = 2 WHERE ORGANIZATION_TYPE = 1
SET Work = 3 WHERE ORGANIZATION_TYPE = 6
SET Work = 4 WHERE ORGANIZATION_TYPE = 3
SET Work = 5 WHERE ORGANIZATION_TYPE = 5
SET Work = 6 WHERE ORGANIZATION_TYPE = 4
SET Work = 7 WHERE ORGANIZATION_TYPE = 11
SET Work = 8 WHERE UNDERSERVED_AREA = 2
SET Work = 9 WHERE UNDERSERVED_AREA = 5
SET Work = 10 WHERE UNDERSERVED_AREA = 3
SET Work = 11 WHERE UNDERSERVED_AREA = 6
SET Work = 13 WHERE UNDERSERVED_AREA = 7
SET Work = 14 WHERE UNDERSERVED_AREA = 8
SET Work = 16 WHERE UNDERSERVED_AREA = 10
SET Work = 17 WHERE ORGANIZATION_TYPE = 10
SET Work = 18 WHERE ORGANIZATION_TYPE = 8
SET Work = 19 WHERE ORGANIZATION_TYPE = 7 or UNDERSERVED_AREA = 11

 

[KARL DEWEY]

You need a translation table. Post the SQL of your update query as you now
have it.

 

[John Spencer]

You might try a switch expression

Set Work = Switch(Organization_Type=2,1,Organization_Type=1,2,
Organization_Type=6,3,..., Underserved_Area=2,8, Underserved_Area=5,9,
...., Organization_Type=7,19, Underserved_Area=11,19)

If that does not work then you are going to need to build a function to
do this. Normally I would build a conversion table, but since you have
two different fields involved, that would get tricky.

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