Query to Extract data from 1 Field into 4

[stepashka]

Hi

I am trying to extract the data from a field in a Table called Status. the data is split into 4 categories and is in numerical form 1=Active, 3=Closed, 4=Deleted and 5=Cancelled these Status numbers relate to unique records in another Field called Tag. I want to with the aid of a query, add 4 new fields funnily enough they will be called Active, Closed, Deleted and Cancelled and all of the 1's will be in the Active field the 3's in the Closed Field and so on.

I think it can be done with expressions but I do not know which ones or how

Hope someone can help me

 

[sonny]

Let me get this straight,

You have fields that are numeric and you want to convert them (display as) a text description? And you want to do this with a query?

Is this correct?

 

[stepashka]

Sonny

The data in the Status field is just a list of 1's 3's 4's and 5's nothing else. but there are about 1300 entries, I just want to sort them out into 4 seperate fields one for the 1's new FieldName ACTIVE, one for the 3's new FieldNAME CLOSED, one for the 4's new FieldName Deleted and lastly one for the 5,s new FieldName Cancelled. If then all the 1's could be then shown in the new field A the 3's as C, the 4's as D and the 5's as C all the better.

stepashka

 

[Rich]

Have you tried a CrosstabQuery, other than that Multiple Iif statements will give the look you require. You certainly don't need to add further fields to your table

 

[stepashka]

Rich

OK but how can I change the Numbers to Text? I plan to concatenate the Tag field to the Status field so that it looks like this.... (A)10003752 this would be an (A)ctive Tag (D)10003752 this would be the same Tag with a (D)eleted status or even (C)10003752 for the Cancelled Status

 

[Rich]

Create a lookup table which has the text value for each number, then provided you've set the relationship correctly just add the lookup table to your existing query, you can then display the text and not the numeric value

 

[stepashka]

I am afraid I am at a very basic user level with Access so a lot of things like Lookup tables are still a bit of a mystery to me can you give me an explanation of how I would create this table using the data I mentioned earlier in the thread?

S