help with query on Sharepoint list

[cdoyle]

Hi,

I just moved my Access tables over to Sharepoint. I'm now using Access and linking to these lists. Basically Sharepoint is going to be the backend, and users can use Access to run reports if needed.

Here is the problem I'm running into.
I have a query that uses an alias, it worked great when everything was in Access.

The purpose of the query, is to display the name of the supervisor of each person.

Here is the code.

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;

But now that I've moved it to sharepoint, it's not working right at all. Sometimes it's listing empoyees multiple times, other times it's not listing them at all. Not sure what's going on.

one thing I noticed is, when in Access when the query ran. The alias column would say 'Supervisor' the alias I gave it.

But now running this with the tables in Sharepoint, the column is named 'EmployeeName' so it has 2 columns with this name. It's like it's no longer recognizing the alias.

Is there something different that needs to be done, with tables in Sharepoint.

or is there a better way to create this query?

Thanks

 

[jal]

That's a strange query you've got there. I don't follow the logic. One of the tables is used twice in the query, which is called a self-join. In a typical self-join, the table is INNER JOINED to iself. Instead you have it LEFT-JOINED to itself. I've never seen that approach. Either this is a mistake, or you are highly advanced at writing queries.

What's this query supposed to do?

 

[jal]

Ok, I think it's starting to make sense now...

 

[jal]

I'm guessing this query needs rewriting but you'll have to explain what it's supposed to do.

 

[jal]

Later it suddenly occurred to me what this query is doing and how it works, and it looks right to me. Seems you are simply listing employee records and each record in the result set is to show that employee's supervisor name. The query now looks correct to me.

Odd you say it's giving you different result sets. A query should always return the same result set, unless the table has changed. Does it ever give you the right results?

 

[cdoyle]

Sorry I didn't get back to you until today, I didn't get the email notifications.

yes, that's how the query is suppose to work, it lists each employee and then lists a supervisor that is associated to that employee.

When I have the tables in access it worked great, then I moved them over to Sharepoint. In doing so, it creates a new primary key column for each table. So I thought, ah OK thats's what's wrong. I'll have to redo these, since the ID's do not match anymore.

So I wiped out everything I had in the supervisor column, and and started adding them back in using the primary key that sharepoint created. I did a few, then all of a sudden when I ran the query it came up with a recordset total of like 30k!! There should only be about 160 total.

So I'm stumped to why it works fine when they are access tables, but when trying to do it with a sharepoint list it messes up.

I'm overlooking something, I just know but can't figure out what it is.

 

[cdoyle]

It should be possible to use an alias still right?
I still don't get why when I run the query against the sharepoint lists, that the alias column displays the table column name instead of the alias name.

 

[cdoyle]

I went over to the MS forums, and found someone else having a very similar problem with using an alias against a sharepoint list. Instead of the alias name showing, it's showing the column name.

http://www.microsoft.com/office/com...&p=1&tid=b798ccd3-9aad-440b-ac07-8eea36f8e1ff

Hopefully this isn't a bug with Sharepoint, or Access. I really need this to work.

 

[cdoyle]

I was wondering if anyone here has access to Sharepoint?

If so, could you do a test for me. Create a list, then link to it in Access.
create a query, just a simple select query.

Now add a column, and give it an alias.
When you run the query, does it display the alias name, or the table field name?

I did this, and again it displays the table field name. It seems to just ignore the alias, and I'm not sure why.

 

[cdoyle]

I've tried everything I can think of, nobody on the MS site will even respond to my post.

This is really upsetting, I spent all this time working on this, and going to have to tell them it won't work because a simple query won't run properly.

It has to be some sort of bug in either Sharepoint or Access or how the 2 work together. The query runs fine when the tables are within Access but doesn't work at all when the 'tables' are 'lists' in Sharepoint.

I even tried creating a simple 3 column 'list' and doing the same query. Doesn't work, it lists rows multiple times.
Then I took that same sample 'list' imported it into Access (not linked) ran the same query on it, and then it worked.

 

[Tibs]

I don't know if this will still be of help to you but there is something that you can try out: (IMPORTANT: Before doing this, it would be best if you tried it out on a test database so you won't be wasting too much effort on something that doesn't work for you) 1. Rebuild your database in Access in a local environment (meaning you copy your existing data from the table linked to SharePoint to a new table in a new Access file), 2. make your queries in your new Access file, 3. Publish to SharePoint. This means you will have to delete your existing data in SharePoint so you can re-publish at the same site.

The logic here is that your query will work only if it was already there before you published it to SharePoint. If you made the query AFTER your tables have been linked, then you will experience the 'bug' you described.

Also in my little experience, i think primary keys do not work well with SharePoint . So stay away from it if you intend to use SharePoint as your data store. As you may already know, i think you can still achieve your goal even without defining a Primary Key field.

Hope this will solve your problem. Good luck!

 

[cdoyle]

Hi Tibs, thanks for replying to this post.

Unfortunately tho, I had already tried what you suggested and it doesn't work
I've tried it both way actually, first creating the query to run against the access tables, then publish.

Then also, creating the query to run against sharepoint lists after they were created.

Same thing each time.


Here is what I eventually had to do, and I just hate it but it works I guess.
What I did was create a local table in the front end, then when the db opens the startup form has some code that basically copies everything from that sharepoint list down to the temp table. Then I use that for the query.

Then when I close the db, it deletes everything from that table.

I just hate doing that, but it was the only way to get the query to work. It just sucks that you have to do this type of stuff. A bunch of extra un-needed work, to get something that should just work on it own.

 

[Tibs]

Hi cdoyle. Too bad it didn't work for you. I experienced the bug that you described before and it was resolved with the method that i told you. Guess it really is a bug...

What i eventually did was use Excel as front-end. From there, I used VBA to query the data from the Access database file by SQL. When the Access file is being opened by Excel, it automatically synchronizes with the SharePoint list before the query is performed. This ensures that the data that you are getting are the latest from the SharePoint list.

I agree that your work-around is an extra work that shouldn't be necessary in the first place. Hopefully someone from Microsoft will be able to fix it...

 

[cdoyle]

I agree that your work-around is an extra work that shouldn't be necessary in the first place. Hopefully someone from Microsoft will be able to fix it...

So far I can't even get anyone at MS to admit there is a problem
I posted on the MS forums about it, and one of the MVP's posted and said "Not sure why it would do that" said he had a meeting with 26 of MVP's the next day would would ask about it and would report back. Never heard another word on it.

Is there a place to officially report bugs to MS?