Access to SQL Query

Tags:
join
SQL
Access
Query
May 12, 2010 at 11:35:01
Specs: Windows XP
I am trying to convert a query from Access 2007 into SQL 2005. The original access query looks like this:

UPDATE (Collector_Move_Table INNER JOIN dbo_UVW_SR_ACCOUNT ON Collector_Move_Table.AccountID = dbo_UVW_SR_ACCOUNT.AccountID) INNER JOIN dbo_Bunch ON Collector_Move_Table.Move_To = dbo_Bunch.Description

SET Collector_Move_Table.[Exception] = False, dbo_UVW_SR_ACCOUNT.QueueID = [dbo_Bunch]![BunchID]

WHERE (((Collector_Move_Table.Queue) Like "Dia*" Or (Collector_Move_Table.Queue) Like "Ski*"));

I am getting a syntax error for the '(' on the first line but don't know of any other way to convey the double inner join for the statement. Does anyone know how this should be written to run in SQL?


See More: Access to SQL Query

Report •

#1
June 23, 2010 at 09:43:34
Looks like noone else is going to, so I'll take a stab... I'm not completely sure if this will work, as I don't really have a sample set of data to work with... Nevertheless, here goes. The first script is a straight conversion of your Access query, which results in an error in SQLServer2005, as you described. The second one successfully completes the syntax check. I would highly recommend backing up your data prior to executing, in case it did not work as expected.

--Straight Conversion of your original code.
UPDATE	DatabaseName.dbo.Collector_Move_Table CMT INNER JOIN
		DatabaseName.dbo.UVW_SR_ACCOUNT USA
			ON CMT.AccountID = USA.AccountID INNER JOIN
		DatabaseName.dbo.Bunch B
			ON CMT.Move_To = B.Description
SET		CMT.Exception = 'False',
		USA.QueueID = B.BunchID
WHERE	CMT.Queue Like 'Dia*'
		Or CMT.Queue Like 'Ski*'

--Modified version, using joins within the where statement.
UPDATE	DatabaseName.dbo.Collector_Move_Table
SET		CMT.Exception = 'False',
		USA.QueueID = (SELECT	B.BunchID
						FROM	DatabaseName.dbo.Collector_Move_Table CMT INNER JOIN
								DatabaseName.dbo.UVW_SR_ACCOUNT USA
								ON CMT.AccountID = USA.AccountID INNER JOIN
								DatabaseName.dbo.Bunch B
								ON CMT.Move_To = B.Description
						WHERE	CMT.Queue Like 'Dia*'
								Or CMT.Queue Like 'Ski*')
WHERE	CMT.Queue Like 'Dia*'
		Or CMT.Queue Like 'Ski*'

Good luck!

Select * From Users Where Clue > 0;
0 rows affected


Report •
Start a discussion
Related Solutions
See More ↓


Ask Question