We help IT Professionals succeed at work.

Access join syntax when converting to T-SQL query

110 Views
Last Modified: 2017-03-10
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?
Comment
Watch Question

Jim HornSQL Server Data Dude
CERTIFIED EXPERT
Most Valuable Expert 2013
Author of the Year 2015

Commented:
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

Author

Commented:
Hi Jim,

Thanks for responding.  I actually stumbled across your article while researching possible solutions online.  Great info in there!
SQL Server Data Dude
CERTIFIED EXPERT
Most Valuable Expert 2013
Author of the Year 2015
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
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!

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.