Create a sepearte column baesd on the data in other columns

[JEEVAN34]

Hi! I have a database with all the countries and thier states Information. I Need to write a query to a create a new column(Shortcut) specifying C/S for each Country first line and only S for other lines. Refer to the attachments

[CJ_London]

it can be done, but you need a column to indicate which is first - you cannot rely on the order in which it is displayed (which is an excel attribute) to determine first.

From your example this would appear to be the GDP2 column. Assuming this is the case then a query like this would do the job

SELECT O.*, iif(F.gdp2 is null,"S","C/S") as Shortcut
FROM tblCountries O 
    LEFT JOIN 
        (SELECT Country, max(GDP2) FROM tblCountries GROUP BY Country) F 
            ON O.Country=F.Country AND O.GDP2=F.GDP2

[ranman256]

no. You may do it in a table, but not in a query.

[JEEVAN34]

hello Ajax,
Thanks for your suggestion. But i need to create a new column based on Countries and Cities columns. Is it possible??

[CJ_London]

As ranman says, if you want to create a new column you would need to add the new column to the table either manually, through tabledefs or use sql - see this link

My suggested query was based on country and gdp2. Not sure about cities, do you mean states? either way you would still need some definition of order to determine 'first'. You cannot base it purely on the first one in the list since this is random.

[CJ_London]

hit the wrong button! -- Sql link

http://www.w3schools.com/sql/sql_alter.asp