Displaying The Same Fields As Different Names From The Same Table -access 2010

I have EmployeeName; it is from the Employee table. The Employee table holds ALL Employees in the organization and the Employee table references the primary key of the Position tab

Solution 1:

In its simplest form you can distill the Employee-Supervisor relationship down to three tables:

[Employee]

EmpNo  EmpFirstName  EmpLastName
-----  ------------  -----------
    1  Montgomery    Burns      
    2  Homer         Simpson    

[Hourly]

EmpNo  ISLNo
-----  -----
    2      1

[ISL]

ISLNo  ProgramSupervisor_EmpNo  ISLName                  
-----  -----------------------  -------------------------
    1                        1  Springfield Nuclear Plant

If you put them together in a query that looks like this

it produces results like this:

Employee_LastName  Employee_FirstName  ISLName                    Supervisor_LastName  Supervisor_FirstName
-----------------  ------------------  -------------------------  -------------------  --------------------
Simpson            Homer               Springfield Nuclear Plant  Burns                Montgomery          

"But wait a minute!" I hear you say, "There are four tables in that query. Where did the [Supervisor] table come from?"

That is just another instance of the [Employee] table that uses [Supervisor] as its alias. A table can appear in a query more than once provided that we use aliases to specify the instance to which we are referring when we talk about [EmpLastName], [EmpFirstName], etc..

The SQL for the above query shows the second instance Employee AS Supervisor on the second-last line:

SELECT 
    Employee.EmpLastName AS Employee_LastName, 
    Employee.EmpFirstName AS Employee_FirstName, 
    ISL.ISLName, 
    Supervisor.EmpLastName AS Supervisor_LastName, 
    Supervisor.EmpFirstName AS Supervisor_FirstName
FROM 
    (
        Employee 
        INNERJOIN 
        (
            Hourly 
            INNERJOIN 
            ISL 
                ON Hourly.ISLNo = ISL.ISLNo
        ) 
            ON Employee.EmpNo = Hourly.EmpNo
    ) 
    INNERJOIN 
    Employee AS Supervisor 
        ON ISL.ProgramSupervisor_EmpNo = Supervisor.EmpNo