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?
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));