Switch() Function Too Complex

D

Dana

Help!!! and thanks in advance to anyone who can help me.

I am trying to use the switch function to evaluate various
city names and set a specific county name for them.
However, it seems I've used too many expressions because I
get #error in what should be the county column. Please
help. What other structure could I use to set the county
to a specific name if the city matches the expression?
Please give an example with at least three counties using
my example below. I am new to queries and such so I need
to take baby steps. My example:

SELECT [CLIENT].[PriCity], Sum([TRIPARCHIVE].[Clients]) AS
ADAPass, Switch([CLIENT].[PriCity] In
("Alameda","Albany","Berkeley","Emeryville","Oakland","Pied
mont"),"NORTH ALAMEDA COUNTY",[CLIENT].[PriCity] In ("San
Leandro","Hayward","Castro Valley","San Lorenzo"),"CENTRAL
ALAMEDA COUNTY",[CLIENT].[PriCity] In
("Dublin","Livermore","Pleasanton","Union
City","Fremont","Newark"),"SOUTH ALAMEDA COUNTY",[CLIENT].
[PriCity] In ("El Cerrito","El
Sobrante","Kensington","Point Richmond","Richmond","San
Pablo"),[CLIENT].[PriCity] In
("Antioch","Concord","Crockett","Danville","Hercules","Lafa
yette","Orinda","Pinole","Pittsburg","Pleasant
Hill","Rodeo"),"CONTRA COSTA COUNTY Outside Coordinated
Service Area",[CLIENT].[PriCity] In ("Daly
City","Milpitas","San Francisco","San Jose","Santa
Clara"),"CITIES OUTSIDE ALAMEDA and CONTRA COSTA
COUNTIES",True,"OTHER") AS County
FROM CLIENT LEFT JOIN TRIPARCHIVE ON [CLIENT].[Id]=
[TRIPARCHIVE].[Clientid]
WHERE ((([TRIPARCHIVE].[Status])="s"))
GROUP BY [CLIENT].[PriCity];
 
D

Duane Hookom

Access is a relational database. You need to take advantage of this. I would
create a table of unique cities. Include a field for the county. You can
then add this "lookup" table to your query and join city fields.
Your current query maintains "data" in a complex expression. You should be
managing data in tables.
 
D

Dana

Er um, thanks for the extra information on how I 'need' to
use Access.

For those of you who can offer help I guess I should have
mentioned that I'm using Access to query fixed SQL
tables. I don't want to have to create a separate table
in Access and maintain that separately outside the SQL
database. I just need a way to assign cities to counties
and have all others assigned to an 'other' county. I
thought switch would work but I have too many expressions
I suppose.

Thanks!
 
K

Kelvin

Your expression looks fine, but I think its too long. Most functions have a
limit of 255 characters. How about abbreviating your counties then using a
lookup table to match the abbreviation to the full name. You wouldn't have
to maintain this table since the list of counties probably won't change. If
it does, you'd have to change the query anways. Or you could just use
another switch function to convert the abbreviation.

Kelvin
 
M

Michel Walsh

Hi,



Exactly. It is far easier to do that association in a table, inside Access
(or inside any relational database).


CitiesCounties ' table name
City County ' fields
Alameda NORTH ALAMEDA COUNTY
Albany NORTH ALAMEDA COUNTY
Berkeley NORTH ALAMEDA COUNTY
Dublin SOUTH ALAMEDA COUNTY
.... ' data sample



Then, the query is:

SELECT PriCity, SUM(clients), LAST( Nz(County, "OTHER") )
FROM Client LEFT JOIN CitiesCounties ON Client.Pricity = CitiesCounties.City
GROUP BY PriCity

ok, I have removed your join with triparchive, but observe how the query is
now very simple.... and maintenance is a charm... if you need to add a city,
no need to edit the query, just the TABLE, where the data should be.



Hoping it may help,
Vanderghast, Access MVP

Dana said:
Er um, thanks for the extra information on how I 'need' to
use Access.

For those of you who can offer help I guess I should have
mentioned that I'm using Access to query fixed SQL
tables. I don't want to have to create a separate table
in Access and maintain that separately outside the SQL
database. I just need a way to assign cities to counties
and have all others assigned to an 'other' county. I
thought switch would work but I have too many expressions
I suppose.

Thanks!
Click to expand...
 
D

Duane Hookom

If you don't have control over the tables then I would create a generic
function that can be used anywhere and easily maintained.

Function GetCounty(strCity as String) as String
Select Case strCity
Case "Alameda","Albany","Berkeley","Emeryville","Oakland","Piedmont"
GetCounty = "NORTH ALAMEDA COUNTY"
Case "San Leandro","Hayward","Castro Valley","San Lorenzo"
GetCounty="CENTRAL ALAMEDA COUNTY"
Case "Dublin","Livermore","Pleasanton","Union
City","Fremont","Newark"
GetCounty="SOUTH ALAMEDA COUNTY"
Case "El Cerrito","El Sobrante","Kensington","Point
Richmond","Richmond","San Pablo"
'etc
End Select
End Function

THis would be much easier to maintain.

--
Duane Hookom
MS Access MVP


Dana said:
Er um, thanks for the extra information on how I 'need' to
use Access.

For those of you who can offer help I guess I should have
mentioned that I'm using Access to query fixed SQL
tables. I don't want to have to create a separate table
in Access and maintain that separately outside the SQL
database. I just need a way to assign cities to counties
and have all others assigned to an 'other' county. I
thought switch would work but I have too many expressions
I suppose.

Thanks!
Click to expand...
 
D

Duane Hookom

Your original posting also contains an error in that it doesn't include a
county name following ",[CLIENT].
[PriCity] In ("El Cerrito","El
Sobrante","Kensington","Point Richmond","Richmond","San
Pablo")

--
Duane Hookom
MS Access MVP


Dana said:
Er um, thanks for the extra information on how I 'need' to
use Access.

For those of you who can offer help I guess I should have
mentioned that I'm using Access to query fixed SQL
tables. I don't want to have to create a separate table
in Access and maintain that separately outside the SQL
database. I just need a way to assign cities to counties
and have all others assigned to an 'other' county. I
thought switch would work but I have too many expressions
I suppose.

Thanks!
Click to expand...
 
Top