Try:

UPDATE UniqueSups
SET ssn =
 (SELECT DISTINCT N.ssn
  FROM tblNonNormalized AS N
  WHERE N.supervisorname = UniqueSups.name
   AND N.ssn IS NOT NULL)

This can only work if you have a single SSN for each unique name in
your non-normalized table. Otherwise you'll get an error and you'll
have to do some more data cleansing.

Huh?  Post your table structure.  field names? types? meanings?

union queries are about as hard as falling down.  The *only* trick is
that you need union-compatible fields (generally of the same type).

Say you have two tables, tblA and tblB, with structures like this:

CREATE TABLE tblA(
SSN   Text(9) PRIMARY KEY,
Firstname  Text(20),
Lastname  Text(25),
...
)

and

CREATE TABLE tblB(
SocSecNo  Text(9) PRIMARY KEY,
FName Text(20),
LName Text(25),
...
)

Union is no big deal - you just have to alias the fields in one table
so they map right...

SELECT SSN, FirstName, LastName
FROM tblA
UNION ALL
SELECT SocSecNo as SSN, FName as FirstName, LName as LastName
FROM tblB
ORDER BY SSN;

(aliasing is the [FieldName] AS (alias) stuff.

IF the database really is not normalized, you *may* need to normalize
it to get it to work... so you might want to post the relevant parts of
the database structure and what you need to do with the data.  Whether
you normalize will depend on several factors, and without knowing some
more about the thing, it's hard to tell what to advise.  Could be a
huge undertaking for very little payoff... but then it might be worth
it or relatively painless...