CODEWITHSUNDEEP
sqlms-access
Mik0r
Mik0r

Reputation: 201

How to change results in Access query?

I have a table, we'll call it users. 

id username type

The type field is a two character type, so each user either has US, MO, AD. Not sure why the original developer did this, but I cannot change this method, unfortunately.

1 mike US
2 Tim AD
3 mark MO

Instead of returning US, AD... etc types in Access, I'd like the query to replace the US with USER, replace MO with MODERATOR, and replace AD with ADMIN.

Is this possible?

Kind of like SELECT * FROM USERS and then if statements within Access (if US, then USER). Not sure how to proceed?

Upvotes: 1

Views: 1040

Answers (5)

David-W-Fenton
David-W-Fenton

Reputation: 23067

I agree that you should have a table for this -- anything else verges on storing data in your SQL statements.

For completeness, let me mention Switch() and Choose(). The former takes an arbitrary number of value pairs, the latter takes an index and a list of values. The former works for strings or numbers, as with CASE WHEN, while Choose() works only with numbers.

Both are of limited utility, since use of them amounts to storing a lookup table in your SQL, but it's good to know about these things for cases where it's simply not feasible to add a lookup table.

Upvotes: 0

Conrad Frix
Conrad Frix

Reputation: 52645

If you had another table called user type that looked like this

UserType (typeid, description) 
US  USER 
AD  ADMIN 
MO  MODERATOR

it would be trivial 

 SELECT users.id, users.username, UserType.description as type
 FROM 
    USERS 
    INNER JOIN UserType
    ON USERS.Type = UserType.TypeID;

you could also use a VBA function, or nested IIF

You could also change your Look up Display Control to a Combo Box and set the row source type to value list

Upvotes: 2

Eugen Constantin Dinca
Eugen Constantin Dinca

Reputation: 9130

You can create a table for the US to USER, MO to MODERATOR etc mappings and use it in a join.
A lot easier to maintain, extend (i.e. different languages) than hard-coding with the drawback that you need to maintain your mapping table.

Upvotes: 1

Bobby D
Bobby D

Reputation: 2159

Sure thing! Try something along these lines: select [id], [username], iif([type] = "US", "User", iif([type] = "MO", "Moderator", iif([type] = "AD", "Admin", "Unknown Type" ) )

(Here's a link to the IIf function)

All things considered, though, you may want to normalize your data with a lookup table. So, for example, create a second table called, "UserType" with two columns: [Code] and [Name]. Then populate it with three records: "US", "User" "MO", "Moderator" "AD", "Admin"

Then, within query designer, include both tables and join them by dragging the "type" column from your source table to the "[Code]" column on the [UserType] table. Then, select the [id] and [username] columns from the first table, and the [name] column from the lookup table. That way, if you need to add new user types, you don't have to worry about going back and modifying all of your queries.

Upvotes: 2

Jahan Zinedine
Jahan Zinedine

Reputation: 14874

Sql Server has a construct called Case When but it's not available in MS Access.

Here is a workaround

Upvotes: 1

Related Questions