Switch/Case statment in access

Q:

Microsoft Access, Database programming, CASE statement, Access 2003

how i can use Switch/Case Satatment in Mic. Access 2003, am working on database as follow:

Field Name: Directions
in this field i need to write codes like: TAm

Field Name: Translation
here i need to read TAm as T=Take, Am= Morning.

the problem i have more than 200 codes, which they have to be togther in the translation field

any help??

ASKED: Aug 20 2008 11:10 AM GMT

_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

RELATED QUESTIONS

A:

RATE THIS ANSWER

0
Click to Vote:

0
0

You can go many different ways; so without knowing your 200 codes, I can give a preferred suggestion; but it may not work for your situation. If the characters are a consitent length, you could use queries and tables to get what you need. Example:

Make a table called ActionTable and add columns Action and Description. Create rows like this:
Action Description
T Take
G Give

Make a table called TimeTable and add columns ActionTime and Description. Create rows like this:
ActionTime Description
Am Morning
PM Night

Now build one query that separates the Translation column into 2 columns from your table where the data entry is being done.

Select *, left(Translation,1) As Action, Mid(Translation,2,Len(Translation)) As ActionTime SomeTable

Now build a second query that joins the first query to the tables created earlier:

select * from query1 inner join ActionTable on query1.Action = ActionTable.Action inner join TimeTable on query1.ActionTime on TimeTable.ActionTime

This second query will allow for the descriptions of each code to be displayed. This way you can have your codes in a table rather than "hard Coded" using Case or IIF

Last Answered: Aug 21 2008 3:05 PM GMT by Randym

1395 pts.

View History

A:

You can go many different ways; so without knowing your 200 codes, I can give a preferred suggestion; but it may not work for your situation.  If the characters are a consitent length, you could use queries and tables to get what you need.  Example:

Make a table called ActionTable and add columns Action and Description.  Create rows like this:
Action   Description
T            Take
G            Give

Make a table called TimeTable and add columns ActionTime and Description.  Create rows like this:
ActionTime   Description
Am                 Morning
PM                 Night

Now build one query that separates the Translation column into 2 columns from your table where the data entry is being done.

Select *, left(Translation,1) As Action, Mid(Translation,2,Len(Translation)) As ActionTime SomeTable

Now build a second query that joins the first query to the tables created earlier:

select * from query1 inner join ActionTable on query1.Action = ActionTable.Action inner join TimeTable on query1.ActionTime on TimeTable.ActionTime

This second query will allow for the descriptions of each code to be displayed.  This way you can have your codes in a table rather than "hard Coded" using Case or IIF

RSS - Discussions Help

Discuss This Answer:

_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

Darkblue46 15 pts. | Aug 24 2008 8:34AM GMT

thanks for your suggestion, but i believe as what you said its not working in my case, here i’ll try to explain more.

in my database, i have created a shourtcut for some words Like:
T=take
Tab= tablet(s)
C=capsule(s)
D=day(s)
Dy=daily
OD=once daily
TSP=teaspoonful
Bid=twice daily
Tid=three times daily
Qid=four times daily
Sw=shake will
Ins=insert

in the Form View; i have on field named Instruction, and in this field i need to type only the shortcut words Like:
T C OD
T BID

so, what i need is to view and save the Translation of that for each record.

Randym 1395 pts. | Aug 25 2008 5:53PM GMT

You could do this based on what I said earlier except instead of using the Left and Mid functions, you need to make your own function. This is kind of tough to explain in words but I’ll give it a try.

You make a function whose parameters passed in are the code (i.e. translation code like “T C OD” or “T BID”) and the spacing number that comes after the code. For example: GetTranslation(”T C OD”, 1) would return “T”. GetTranslation(”T C OD”, 2) would return “C” and GetTranslation(”T C OD”, 3) would return “OD”.
The function could use the Instr function that would find the position of the space based on the second parameter passed in. And then find the position of the space that is one less than the second parameter passed in. Then what is between those positions is the code you are looking for. Of course, you need to make special considerations for the first and last codes.

Now with this method, you would only need one table with all the possible codes. Then a query that would make as many codes columns that you could have in one translation. Lets say that you would only have at maximum 5 codes in one translation. Your query would look like:

Select *, GetTranslation(TranslationCode, 1) As TransCode1, GetTranslation(TranslationCode, 2) As TransCode2, GetTranslation(TranslationCode, 3) As TransCode3, GetTranslation(TranslationCode, 4) As TransCode4, GetTranslation(TranslationCode, 5) As TransCode5 from Table

Now a second query to join the code table:
In design view, bring in your first query and bring in the CodeTable 5 times. Then left join each TransCode from the first query (1 through 5) to its respective CodeTable (CodeTable_1, CodeTable_2….) This will allow you to get the description from the CodeTable. (You would use outer joins beacuse some functions may not return anything that matches a code because there wasn’t a code in that position)

The hardest part is to make the function. But it can be done.

Browse IT Answers by Topic