SELECT DISTINCT Users in Table 1 which don't exist in Table 2

Question

I have a table with 4 columns of user information. Each table has the following columns:

Username | Full_Name | Job_Name | Current_Job_Allowed

Table 1 includes all users and the Job_Name which they have permissions to view. This means that there are multiple lines of the same username in Table 1 with different Job_Name values.

Table 2 contains a list of all possible users.

Username      |Full_Name
--------------+-----------------
amunoz        |Andrew Munoz
csmith        |Carl Smith
cwatkins      |Cat Watkins
ggriffiths    |Garmin Griffiths
jcarr         |Jason Carr
jhothi        |Jark Hothi
jphillips     |Jim Phillips
lbradfield    |Lisa Bradfield
ntaylor       |Noria Taylor
rfelipe       |Ralf Felipe

Query 1 contains all users specified by a query parameter which I specify, i.e. 'KML_20160531'.

I would like to now select a DISTINCT list of all users which have a different Job_Name from the parameter I specify for Job_Name. For example Table 1 contains:

Username|Full_Name   |Job_Name    |Current_Job_Allowed
--------+------------+------------+----------------------
amunoz  |Andrew Munoz|KML_20160531|1
jcarr   |Jason Carr  |KML_20160531|1
rfelipe |Ralf Felipe |KML_20140531|1
amunoz  |Andrew Munoz|KML_20160431|1

I would then like to return the below when I enter 20160531 for Job_Name. This will return all possible new users for the Job_Name value I entered.

Username      |Full_Name
--------------+---------------
csmith        |Carl Smith
cwatkins      |Cat Watkins
ggriffiths    |Garmin Griffiths
jhothi        |Jark Hothi
jphillips     |Jim Phillips
lbradfield    |Lisa Bradfield
ntaylor       |Noria Taylor
rfelipe       |Ralf Felipe

Answer 1

This parameter query will show you which users have a Table 1 row with Job_Name matching the parameter value:

PARAMETERS which_job Text ( 255 );
SELECT t1.[Username], t1.Job_Name
FROM [Table 1] AS t1
WHERE t1.Job_Name=[which_job];

So you can use that as a subquery, left join Table 2 to the subquery, and select the rows where the "right side" is Null:

PARAMETERS which_job Text ( 255 );
SELECT t2.[Username]
FROM
    [Table 2] AS t2
    LEFT JOIN
        (
            SELECT t1.[Username]
            FROM [Table 1] AS t1
            WHERE t1.Job_Name=[which_job]
        ) AS sub
    ON t2.[Username] = sub.[Username]
WHERE sub.[Username] Is Null;

Assuming that query returns the correct rows, add the other field you want to see to SELECT t2.[Username].

You should not need DISTINCT unless Table 2 allows duplicate Username values, or Table 1 allows more than one row with the same combination of Username and Job_Name.