Link to home
Start Free TrialLog in
Avatar of Larry S
Larry S

asked on

Access join syntax when converting to T-SQL query

Hi,

I am recreating some Access queries in SQL Server.  Exporting the Access query is easy enough.  And while there are plenty of syntax differences between Access and T-SQL, I understand those differences fairly well.  Except one.  I've seen this in a couple of different queries now.  I understand that Access uses parentheses with joins, that is not my issue.  However, this query seems to have a nested join and I have questions about it.

First question. You can clearly see the first table in my From Clause, but why doesn't an "ON clause" follow immediately after?  Instead, the "On Clause" seems to be the very bottom row of code I posted below.  

FROM dbo.table1
      INNER JOIN ((((dbo.rpt_SalesHistory
      INNER JOIN [CorrectionList] ON dbo.rpt_SalesHistory.ItemClass = [CorrectionList].ItemClass)
      INNER JOIN [RegionalManagerCorrect] ON (dbo.rpt_SalesHistory.MGPSalesPerson = [RegionalManagerCorrect].MGPSalesPerson)
                  AND (dbo.rpt_SalesHistory.CustomerShortName = [RegionalManagerCorrect].[Business Unit Name])
                  AND (dbo.rpt_SalesHistory.business_unit = [RegionalManagerCorrect].[Business Unit]))
      INNER JOIN [SalesGroupCorrection] ON dbo.rpt_SalesHistory.CustomerShortName = [SalesGroupCorrection].[Customer Short Name])
      INNER JOIN [PrimaryVendor] ON dbo.rpt_SalesHistory.OrrPN = [PrimaryVendor].ITEMNMBR)
      
      ON ([PrimaryVendor].PRIMVNDR = dbo.table1.VENDORID) AND (dbo.table1.ITEMNMBR = [PrimaryVendor].ITEMNMBR)

I've tried joining these tables in SQL a number of different ways, but I cannot get anywhere near the row count that I get in access, so my I haven't joined them correctly yet.  Does anyone know how to interpret this as a SQL From Clause?
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

Not abundantly certain, but I've had success deconstructing the nested nature of Access JOINS, not to mention all the extra parentheses, and rewrite it as separate JOIN..ON clauses.

Might have to add a section to this article, which would be a good read for you right now .. Migrating your Access Queries to SQL Server Transact-SQL
Avatar of Larry S
Larry S

ASKER

Hi Jim,

Thanks for responding.  I actually stumbled across your article while researching possible solutions online.  Great info in there!
ASKER CERTIFIED SOLUTION
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Larry S

ASKER

Thank you, Jim.  I think this is going to work.  Much appreciated.  And yeah...this Access DB wasn't built by me.  I have a lot of clean up to do regarding spaces in column names (and table names).  Thanks so much!