Tim Coughlin Tim Coughlin - 7 months ago 16
SQL Question

Access Query for Ranking/Assigning Priority Values

I am doing data conversion from a previous system that was keyed in without validation rules. I am working with a table of Emergency Contacts, and trying to assign a primary contact with (

Y
/
N
) when the field is blank or duplicated (i.e. someone puts
Y
or
N
for multiple contacts I want to arbitrarily assign primary). I will also assign a new column with an alphabetic sequence (
a
,
b
,
c
, etc.) based on the priority which was designated in the other column.

Every ID must only have 1 Priority 'Y'.

Current Table:

+--------+---------+----------+
| id | fname | pri_cont |
+--------+---------+----------+
| 001000 | Rox | Y |
| 001000 | Dan | N |
| 001002 | May | Y |
| 001007 | Lee | Y |
| 001007 | Clive | Y |
| 001008 | Max | Y |
| 001008 | Kim | N |
| 001013 | Sam | Y |
| 001013 | Ann | |
| 001014 | Nat | Y |
| 001018 | Bruce | Y |
| 001018 | Mel | |
| 001020 | Wilson | Y |
| 001022 | Goi | Y |
| 001022 | Adele | N |
| 001022 | Gary | N |
+--------+---------+----------+


What I want:

+--------+---------+----------+----------+
| id | fname | pri_cont | priority |
+--------+---------+----------+----------+
| 001000 | Rox | Y | a |
| 001000 | Dan | N | b |
| 001002 | May | Y | a |
| 001007 | Lee | Y | a |
| 001007 | Clive | N | b |
| 001008 | Max | Y | a |
| 001008 | Kim | N | b |
| 001013 | Sam | Y | a |
| 001013 | Ann | N | b |
| 001014 | Nat | Y | a |
| 001018 | Bruce | Y | a |
| 001018 | Mel | N | b |
| 001020 | Wilson | Y | a |
| 001022 | Goi | Y | a |
| 001022 | Adele | N | b |
| 001022 | Gary | N | c |
+--------+---------+----------+----------+


How can I do that?

Answer

Well, as I see it your cleanup requires several queries (please note queries assume Emergency Contacts table has a unique autonumber, dbID):

One Select Query to count Y and N instances. Also, query can calculate Priority column using the Chr ASCII conversion of numbers to letters.:

SELECT t1.ID, t1.fname, t1.pri_cont, 

     (SELECT Count(*) 
      FROM EmergContacts t2 
      WHERE t1.dbID >= t2.dbID AND t1.ID = t2.ID 
        AND t1.pri_cont = t2.pri_cont AND t1.pri_cont = 'Y') AS YCount, 

     (SELECT Count(*) 
      FROM EmergContacts t3 
      WHERE t1.dbID >= t3.dbID AND t1.ID = t3.ID 
        AND t1.pri_cont = t3.pri_cont AND t1.pri_cont = 'N') AS NCount,

     (SELECT Chr(Count(t2.ID) + 96) 
      FROM EmergContacts t2 
      WHERE t1.dbID >= t2.dbID AND t1.ID = t2.ID) AS Priority

FROM EmergContacts AS t1;

With output such as below:

ID   |  fname    |  pri_cont |  YCount | NCount | Priority
1000 |  Rox      |  Y        |  1      | 0      | a
1000 |  Dan      |  N        |  0      | 1      | b
1002 |  May      |  Y        |  1      | 0      | a
1007 |  Lee      |  Y        |  1      | 0      | a
1007 |  Clive    |  Y        |  2      | 1      | b
1008 |  Max      |  Y        |  1      | 0      | a
1008 |  Kim      |  N        |  0      | 1      | b
1013 |  Sam      |  Y        |  1      | 0      | a
1013 |  Ann      |           |  0      | 1      | b
1014 |  Nat      |  Y        |  1      | 0      | a
1018 |  Bruce    |  Y        |  1      | 0      | a
1018 |  Mel      |           |  0      | 1      | b
1020 |  Wilson   |  Y        |  1      | 0      | a
1022 |  Goi      |  Y        |  1      | 0      | a
1022 |  Adele    |  N        |  0      | 1      | b
1022 |  Gary     |  N        |  0      | 2      | c

From there you run three update queries:

To clean up Nulls:

UPDATE EmergContacts 
SET pri_cont = 'N'
WHERE pri_cont Is Null;

To clean up IDs with more than 1 Ys:

UPDATE EmergContacts 
SET pri_cont = 'N'
WHERE ID IN (SELECT ID FROM EmergContPrCount WHERE YCount > 1)
  AND fName IN (SELECT fName FROM EmergContPrCount WHERE YCount > 1);

And to clean up IDs with no Ys:

UPDATE EmergContacts 
SET pri_cont = 'Y'
WHERE (ID IN (SELECT ID FROM EmergContPrCount WHERE YCount = 0)
  AND fName IN (SELECT Max(fName) FROM EmergContPrCount WHERE YCount = 0));