CODEWITHSUNDEEP
sqlms-access
user2416710
user2416710

Reputation: 53

To select a unique value from a column having multiple values MS Access

Table Structure is like this Table Name:Employee

**Employee_Id   APPRS_TY_CD**
540589                2
540589               UNK
1952938               2
1952938              UNK
2488178               1
2488178              UNK
3818934               1
3818934              UNK
5402944               1

If a Employee ID has APPRS_TY_CD as (UNK AND a value) then APPRS_TY_CD should be a value and not UNK. If APPRS_TY_CD is not UNK for an Employee ID then that value should be populated as it is.

My final output should look like this.

**Employee_Id   APPRS_TY_CD**
540589                2
1952938               2
2488178               1
3818934               1
5402944               1

I'm using MS Access.

Upvotes: 0

Views: 96

Answers (1)

talegna
talegna

Reputation: 2403

This should be fairly simple as numbers are considered "lower" than strings you can use an aggregate function, I've created an SQL Fiddle here (note this is Sql Server but the code should be the same as it's not using proprietary features). Given your data you could use the MIN function from SQL to get the APPRS_TY_CD for each user. Here is my suggested code:

SELECT 
    Employee_Id
    , MIN(APPRS_TY_CD) APPRS_TY_CD
FROM 
    Employee
GROUP BY 
    Employee_Id

The results returned are (you should be able toe execute the fiddle yourself to prove this):

EMPLOYEE_ID    APPRS_TY_CD
540589         2
1952938        2
2488178        1
3818934        1
5402944        1

Upvotes: 2

Related Questions