Access Search Query - Union, Distinct, and Order By Priority

Goal:  Have a single text box where user is allowed to enter single or multiple search terms (seperated by spaces) and run query to return "search results" showing best matches first.

I have a table containing several pieces of information related to a person, and I'll use these four fields as an example: first name, last name, social security number, tax id number.  I want the user to be able to search (with one box) for any of these terms, and return exact matches first, followed by near matches second.  I'm generating this sql in vb.net by parsing the search box, splitting the terms on spaces, and then dynamically creating an sql statement.  I've got it working pretty good, but I'm not quite there yet.

The code block that follows helps illustrate what I'm trying to do (one term search of "Rob" in this example).  This query matches all Persons who's first name or last name IS just "Rob" and places those as a priority (=1), but it also selects all Persons who's first name STARTS WITH "Rob" and gives those a priority of 2.  So everything is great here, but now I get TWO records for both types of match (exact, and first letters).  I want to eliminate the duplicate matches, but still have the priority in the sort order.

I tried making this part of a sub query, as in the second example.  But Access doesn't like the combination of the DISTINCT along with the ORDER BY and a term that isn't included in the SELECT DISTINCT.  How do I get around this, or is there a better way to accomplish what I'm trying to do (I sure would like it to be all one query and not have to manually create a data table or temporary table to hold this information).

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:

' Example #1
 
SELECT * FROM (
SELECT 1 as Priority, FirstName, LastName, SSN, TID FROM People 
WHERE FirstName='Rob' OR LastName='Rob' OR SSN='Rob' OR TID='Rob'
UNION ALL 
SELECT 2 as Priority, FirstName, LastName, SSN, TID FROM People
WHERE FirstName LIKE 'Rob%' OR LastName LIKE 'Rob%' OR SSN LIKE 'Rob%' OR TID LIKE 'Rob%'
) ORDER BY Priority, LastName, FirstName
 
' Example #2
 
SELECT DISTINCT FirstName, LastName, SSN, TID FROM (
SELECT 1 as Priority, FirstName, LastName, SSN, TID FROM People 
WHERE FirstName='Rob' OR LastName='Rob' OR SSN='Rob' OR TID='Rob'
UNION ALL 
SELECT 2 as Priority, FirstName, LastName, SSN, TID FROM People
WHERE FirstName LIKE 'Rob%' OR LastName LIKE 'Rob%' OR SSN LIKE 'Rob%' OR TID LIKE 'Rob%'
) ORDER BY Priority, LastName, FirstName