Need help with running Query against Sharepoint Lists

[Chris]

Hi,
I'm stumped to why this doesn't work now.

We originally had our staff calendar in Access, I've been asked to move our
tables to Sharepoint.

So I've done this, and relinked the tables/lists to our Access Front End.

I have almost everything working, but now I'm running into a problem with
one of my queries.

This query would display every employee, and then list the supervisor for
the employee.

To do this, I used the EmployeeName table twice in the query. ONce to get
the employee name, and then used an alias to get the supervisor name.

The query looked like this
SELECT EmployeeName.EmployeeName, Team_Name.Team,
EmployeeName_1.EmployeeName AS Supervisor
FROM (EmployeeName LEFT JOIN Team_Name ON EmployeeName.DeptID =
Team_Name.ID) LEFT JOIN EmployeeName AS EmployeeName_1 ON
EmployeeName.Supervisor_Manager = EmployeeName_1.ID;

When I run this query against the tables in Access, it worked great. But
now running it against the linked Sharepoint tables, it doesn't work at all.
It will sometimes list employees multiple times, one time I got a records
set of 30k, when we only have a 160 total staff.

One thing I noticed is, when I run the query in Access. The alias
(supervisor) appears as a column header. But when I run it against
Sharepoint, it doesn't list supervisor anymore, it says EmployeeName. So
it's acting like it's not accepting it as an alias.

Any ideas how to fix this?

Thanks

 

[Chris]

I wanted to link to a similar post I foun
http://www.microsoft.com/office/com...&p=1&tid=b798ccd3-9aad-440b-ac07-8eea36f8e1ff


I've tried everything I can think of to make this query work.
I thought maybe something happened in the moving of my table from Access to
Sharepoint.

So I created a brand new list within Sharepoint. Then linked to it in
Access.
This new list had 3 columns ID, name, supervisor.

Now in Access, I created a query like this.

SELECT test.name_test, test_1.name_test AS supervisor
FROM test LEFT JOIN test AS test_1 ON test.supervisor_test = test_1.ID;

I added about 5 records, on a few of the records I enterd an ID in the
supervisor field.

Ran the query, and again it doesn't display the alias names. It lists the
same employee 30+ times, and just doesn't work.

Does anyone here know what is going on? It shouldn't do this, and not sure
where else to go for help.

 

[Chris]

Is there anyone here?
I thought for sure posting on the Microsoft forum would at least get a
response.

I would post a sample db that has the query that works fine on the Access
tables, but don't see a way to add attachments here.

 

[Chris]

Am I posting in the wrong forum for this?

Where are the MVP's?
I posted this over a week ago, and not 1 response from anyone.

I would really appreciate some help on this, I think this is a bug with how
sharepoint and Access work together. Would like to know how to query the
sharepoint list and get the same results I would get when I query an Access
table.

 

[Bob Barrows]

If someone knew the answer I'm sure they would have replied. I've never
played with SP lists in Access so I don't know the answer.

 

[David W. Fenton]

If someone knew the answer I'm sure they would have replied. I've
never played with SP lists in Access so I don't know the answer.

I don't actually think there's much active work with SharePoint
among experienced Access developers, precisely because you give up
so much when you move data to SharePoint.

 

[Chris]

that's true, it's a shame that MS has been pushing Sharepoint so much.
Claiming it's the answer for getting your data to the web.
But then make it so SP doesn't even support referential integrity, and
pretty much everything has to be done with a 'work around' or a bunch of
extra work just to get something to work.

With my problem with the query, what I had to do is when the db opens. I
have some code that basically copies the sharepoint list into a temp table
into my frontend.. Then I use that to do my joins. Then when the db closes
it deletes the temp table.

It's a terrible thing to have to do but I've found no other way to get the
self joins to work against the sharepoint lists.