Nested select SQL within an Update query rotated across multiple columns

[modernshoggoth]

G'day all, thanks in advance for reading.

I've got two tables, one that's full of data regularly externally updated called "tblEmployeeLicences":

EmployeeID,Name,Licences
1001,Bill,Drivers
1001,Bill,Forklift
1002,Ted,Drivers
1002,Ted,Forklift
1002,Ted,Crane

The other table is one that is depended upon by an ID-card printing program, and as such only allows a single row per employee, called "IDProjectData":
EmployeeID,Name,Licence01,Licence02,Licence03...16

I've developed a nested updated/select query to update only the first Licence for each person (it's trivial to get all of them with slight variations on it):

UPDATE IDProjectData 
RIGHT JOIN [SELECT TOP 1 Licences 
FROM tblEmployeeLicences 
WHERE EmployeeID = [IDProjectData].[EmployeeID] ]. AS Results 
ON IDProjectData.EmployeeID=Results.EmployeeID 
SET IDProjectData.IDWLicence01 = Results.Licences;

The above successfully updates the first licence into the IDProjectData table, but the query prompts the user to enter in the CandidateNo for BOTH tables upon running it.

How do I restructure the query so that it just rolls over them all, updating the 'Licence01' column for each employee?
Or should I be going about this a different way?

[modernshoggoth]

Update - the query is now as follows

UPDATE IDProjectData LEFT JOIN [SELECT TOP 1 Licences
FROM tblEmployeeLicences
WHERE EmployeeID = [IDProjectData].[EmployeeID] ]. AS Results ON IDProjectData.EmployeeID =Results.EmployeeID SET IDProjectData.IDWLicence01 = Results.Licences;

It still prompts for both EmployeeID's but now it updates ALL employees with that particular Employee's first Licence - how do I get it to look at each employees and update their own first Licence?

[FishVal]

Hello.

It looks like you have two copies of the same data which generally is not a good idea.
Since you update [IDProjectData] with values from [tblEmployeeLicences] I guess data primary source is the latter table.
So, why don't you want to use crosstab query to get dynamically pivotted data from [tblEmployeeLicences]?

[modernshoggoth]

I'm aware that two copies of the same data isn't the best way to do things, but the ID-card software this is designed for uses the [IDProjectData] table, and is very strict about how data is accessed.

I haven't used a crosstab query because I've primarily dealt with SQL up until now - crosstabs and pivots are mostly new to me, and I can't figure out a way to get it to update pivot'd data.

[FishVal]

@modernshoggoth
What does it mean ?

[modernshoggoth]

The software that accesses the database can only use THAT particular table, and can only use certain cells in certain ways.
What I'm trying to to develop is a way to replicate data from other tables into that table. The way the data arrives into these other tables is also out of my control - therefore I must make a way to put data in this other table via some form of the above update query, or some sort of updating Crosstab or pivot query - and I've no idea where to get started on CT's or Pivots when it comes to updating data rather than simply selecting it.

[FishVal]

• First of all it is very probable that the software connecting to [IDProjectData] table could connect to crosstab query with the same name. Check it please.
• Crosstab query being not updateable will give a unupdateable join with [IDProjectData]. So, I suspect it is not possible to update [IDProjectData] this way.
• [IDProjectData] could be a temporary table created from crosstab query via SELECT INTO.
• [IDProjectData] could be updated via recordset.

[modernshoggoth]

Unfortunately it doesn't like working from a selected crosstab query - I've tried that. The program refuses to open, giving nothing in the way of error messages =(

I haven't tried the other possible solutions - I've just solved it now. I've used VBA to parse and execute SQL to be fed back into the main update query. It's slow, but it doesn't need to be fast.

Thanks, guys!

[NeoPa]

That may be your only solution :(

I'm pretty sure that subqueries in SQL stop it being updatable too.

It's sometimes possible to find sneaky ways around these restrictions, but if you have it processing in code and speed is not a big issue, then that seems like a suitable solution.