Using Switch function properly

[Student Databaser]

I need help with this. I don't know what needs to change in order to make it
work.

SELECT DEMOGRAPHIC.ORGANIZATION_TYPE
SWITCH ((( DEMOGRAPHIC.ORGANIZATION_TYPE=1,"State Health Department",
DEMOGRAPHIC.ORGANIZATION_TYPE=2,"Local Health Department",
DEMOGRAPHIC.ORGANIZATION_TYPE=3,"Other",
DEMOGRAPHIC.ORGANIZATION_TYPE=4,"Other",
DEMOGRAPHIC.ORGANIZATION_TYPE=5,"Other",
DEMOGRAPHIC.ORGANIZATION_TYPE=6,"Public Health CBO",
DEMOGRAPHIC.ORGANIZATION_TYPE=7,"Other",
DEMOGRAPHIC.ORGANIZATION_TYPE=8,"Other",
DEMOGRAPHIC.ORGANIZATION_TYPE=9,"Other",
DEMOGRAPHIC.ORGANIZATION_TYPE=10,"Other",
DEMOGRAPHIC.ORGANIZATION_TYPE=11,"Other")));

 

[Ken Snell \(MVP\)]

You're missing a comma in the first line, assuming that you want to show the
original value and the "switched" value; you also need an alias for the
"switched" value:

SELECT DEMOGRAPHIC.ORGANIZATION_TYPE,
SWITCH ((( DEMOGRAPHIC.ORGANIZATION_TYPE=1,"State Health Department",
DEMOGRAPHIC.ORGANIZATION_TYPE=2,"Local Health Department",
DEMOGRAPHIC.ORGANIZATION_TYPE=3,"Other",
DEMOGRAPHIC.ORGANIZATION_TYPE=4,"Other",
DEMOGRAPHIC.ORGANIZATION_TYPE=5,"Other",
DEMOGRAPHIC.ORGANIZATION_TYPE=6,"Public Health CBO",
DEMOGRAPHIC.ORGANIZATION_TYPE=7,"Other",
DEMOGRAPHIC.ORGANIZATION_TYPE=8,"Other",
DEMOGRAPHIC.ORGANIZATION_TYPE=9,"Other",
DEMOGRAPHIC.ORGANIZATION_TYPE=10,"Other",
DEMOGRAPHIC.ORGANIZATION_TYPE=11,"Other")))
AS TypeDescription;


May I suggest that you create a table (call it tblOrganizationType), and put
records in it that are the descriptions for the types:

ORGANIZATION_TYPE OrganizationDescription
1 State Health
Department
2 Local Health
Department
3 Other
4 Other
5 Other
6 Public Health CBO
7 Other
8 Other
9 Other
10 Other
11 Other


Then you can join this table into your query and make it much easier to type
and to maintain the different values:

SELECT DEMOGRAPHIC.ORGANIZATION_TYPE,
Nz(tblOrganizationType.OrganizationDescription, "NO VALUE IN TABLE")
AS Organization_Description
FROM DEMOGRAPHIC LEFT JOIN tblOrganizationType
ON DEMOGRAPHIC.ORGANIZATION_TYPE =
tblOrganizationType.OrganizationType;


I've used a LEFT JOIN above so that you'll never lose records should you not
have the type value in the table. The Nz function will "warn" you when you
are missing a type value in the table.

--

Ken Snell
<MS ACCESS MVP>

 

[Student Databaser]

Thanks Ken. But this returned a syntax error about the commas in the
expression (I don't think it is referring to the one i added though) Any
other suggestions?

I chose not to build a separate table because the query that i am attempting
to run is specific to one report. If i did create any table it would look
something like this for example 1 = State Health Department, 2=Local Health
Department, 3=Academic, 4=Health Plan 5=Hospital/Health Care Provider,
6=Community Based Organization, 7=OTHER/NOT SPECIFIED, 8=Private/Non Profit,
9=Specify, 10=Industry, 11=Federal Agency

For some reports i need to group together rows into one other category. But
for other reports i need the categories to remain the same.

I guess i am trying to avoid adding more tables and instead just manipulate
the data to get what i want using queries.

 

[Michel Walsh]

You need a FROM clause.

It would be preferable to use another table, which will make the lookup,
rather than having hard coded constant in your code, as Ken suggested.

The 'lookup' table have *two* fields, isn't it? one with a number and the
second field with the description string associated to the number in the
first field.


Vanderghast, Access MVP

 

[Student Databaser]

SELECT DEMOGRAPHIC.PRACTICE AREA, DEMOGRAPHIC.ORGANIZATION_TYPE,
SWITCH (((ORGANIZATION_TYPE="1","State Health Department",
DEMOGRAPHIC.ORGANIZATION_TYPE="2","Local Health
Department",ORGANIZATION_TYPE="3","Other",
DEMOGRAPHIC.ORGANIZATION_TYPE="4","Other",ORGANIZATION_TYPE="5","Other",ORGANIZATION_TYPE="6","Public
Health CBO",
ORGANIZATION_TYPE="7","Other",ORGANIZATION_TYPE="8","Other",ORGANIZATION_TYPE="9","Other",ORGANIZATION_TYPE="10","Other",
ORGANIZATION_TYPE="11","Other"))) AS ORGANIZATION_TYPE
FROM DEMOGRAPHIC;

Is this closer? It has the AS and the FROM - granted they are probably not
in the right places.

Please help

 

[Michel Walsh]

Should be ok, unless the organization_type is a numerical field, rather than
alphanumerical field, in which case it would be preferable to use = 1,
instead of the actual ="1", as example (and also for 2, 3, ... 11)

Vanderghast, Access MVP

 

[Student Databaser]

I am confused with how to use commas and ( ) and [ ] in a SWITCH. I think
this is why my query is still not working but i can't figure it out.

Should it be something like SWITCH ( exp 1, answ 1), (exp 2, answ 2), ( . . .

Or SWITCH ([exp 1, exp 2], [exp 1, exp 2], . . . .

Or just use commas and not parentheses or brackets like i had it before?

 

[Michel Walsh]

Just like you did before. You can test it in the Debug Immediate Window:


? Switch(1=2, "one", 2=2, "two", 3=2, "three", true, "something else")
two
? Switch(1=5, "one", 2=5, "two", 3=5, "three", true, "something else")
something else



Vanderghast, Access MVP

I am confused with how to use commas and ( ) and [ ] in a SWITCH. I think
this is why my query is still not working but i can't figure it out.

Should it be something like SWITCH ( exp 1, answ 1), (exp 2, answ 2), ( .
. .

Or SWITCH ([exp 1, exp 2], [exp 1, exp 2], . . . .

Or just use commas and not parentheses or brackets like i had it before?

Should be ok, unless the organization_type is a numerical field, rather
than
alphanumerical field, in which case it would be preferable to use = 1,
instead of the actual ="1", as example (and also for 2, 3, ... 11)

Vanderghast, Access MVP