[AccessD] Update Query Access vs SQL Server

pcs at azizaz.com pcs at azizaz.com
Mon Dec 3 18:40:24 CST 2007


I have the following update query running in Access:

UPDATE tblRS_ReliefStaff AS T1 INNER JOIN
tmpRS_ReliefStaffByAreaID AS Q1 ON T1.EmployeeID =
Q1.EmployeeID SET T1.ServicingAreas =
IIf(Len([ServicingAreas])>0,[T1].[ServicingAreas] & ";" &
[Q1].[AreaID],[Q1].[AreaID]);

It will create values in the column ServicingAreas like this:
236;123;567;432;260;230

Running this query in SQL Server will only add the first
encountered instance of AreaID into column ServicingAreas, like
236

UPDATE tblRS_ReliefStaff 
SET 
ServicingAreas = 
CASE
	WHEN
		Len([ServicingAreas])>0
	THEN
		[T1].[ServicingAreas] + ';' + [Q1].[AreaID]
	ELSE
		[Q1].[AreaID]
	END
FROM
tblRS_ReliefStaff AS T1 
INNER JOIN 
tmpRS_ReliefStaffByAreaID AS Q1 
ON T1.EmployeeID = Q1.EmployeeID
END

How do I get the SQL Server query to add all encountered
values of AreaID of the inner join into the column
'ServicingAreas' ????


Regards
Borge



More information about the AccessD mailing list