Sharepoint and MS Access help with Query

[illegaloperation1]

Hi,
I'm hoping someone here can help, I've tried every Access forum I can find, and even the MS Community and haven't received any responses.

I have an MS Access db, that I've been asked to move the backend to Sharepoint. So I exported all my tables to sharepoint, but I still need to have the ability to run my reports, and use my forms etc. So I linked my now sharepoint 'lists' into Access.

Here is the problem I'm running into.

I have a table named employeename, it has 3 columns. ID, employeename, supervisor_id.

In the supervisor_id field, I put the ID of the employee the person reports too.

When I had everything in Access, I had a query that would list out each employee and then display if they had a supervisor listed.

It was similar to this.

SELECT E1.employeename, E2.employeename AS supervisor
FROM test E1
LEFT JOIN test AS E2 ON E1.supervisor = E2.ID;

The query we had worked great in Access, but now when I run the exact same query against the Sharepoint lists (which were my Access tables to start with), it doesn't work at all. It will list employees hundreds of times, and it makes no sense at all. I also noticed that it no longer displays the alias name in the column heading. It says employeename in both columns, not sure if this is causing the duplicate rows??

Anyone know how to get around this?
I sure hope someone has some ideas how to make this work.

 

[illegaloperation1]

I forgot to mention, if I import the lists back into Access (not link) and run my query. It works fine again!!

So something to do with Sharepoint, but can't figure out what it is.