SQL query on two task lists in Access - how to get the correct “Assigned to”?

Question

As you all probably know, a Sharepoint task list has an "Assigned To" column. When I import a task list in Access, I get a table for the list and another table, "UserInfo" with the users for that list (those are the same users listed under Permissions for that list in Sharepoint). This UserInfo table has data such as the user's id, title, account, email, department and so on.

When I run the following query:

SELECT *

FROM FirstList

UNION ALL

SELECT *

FROM SecondList;

The "Assigned To" field changes to the ID of the person from UserInfo. Their name is under the "Title" field of the UserInfo table.

How should I modify the SQL query to place the title in the "Assigned To" column?

EDIT: here is an example

In FirstList I have a row with the following values: "Do this" "John Doe" "Not Started" corresponding to the "Title", "Assigned To" and "Status" columns. In UserInfo I have a row with "30" "John Doe" "f2rdf" as values for "ID", "Title" and "Account".

After I do the union, instead of John Doe I would see the value 30. So how do I specify that I want to see the value of the title, not the value of the ID? Something like [Assigned To].Title? (which unfortunately doesn't work)

DanK DanK · Accepted Answer · 2014-08-21T20:20:52

A Union query combines two like datasets. A requirement of a Union is that the number of columns are the same in both sets and that the datatype of each column of the first set matches the datatype of the matching column in the second dataset.

Based on your description of the problem it sounds like you are comparing two datasets that have the same number of columns with all of the same datatypes but with metadata in your second dataset that is out of order (or does not match) from your first dataset.

If you want your columns in your FirstList to match the columns in your SecondList then you will need to explicitly specify the order rather than relying on the SELECT * syntax. So in other words if the columns of your FirstList are ColA, ColB, ColC and the columns in your SecondList are ColB, ColC, ColA then you would want to change your query to read:

SELECT ColA, ColB, ColC
FROM FirstList
UNION ALL
SELECT ColA, ColB, ColC
FROM SecondList;

Otherwise (assuming the datatypes are all the same), you could end up having ColB data falling under your ColA column, ColC data in your ColB, etc....

SQL query on two task lists in Access - how to get the correct “Assigned to”?

1 Answers