Solved

Could you check what is needed to make this perfectly running MSSQLServer query to run under Access?

Posted on 2016-08-22
19
65 Views
Last Modified: 2016-08-22
Hi Experts

Could you check what is needed to make this perfectly running MSSQLServer query to run under Access?

I need to make an Access query ro retrieve data  joined 03 times from the same table, accordingly to:

img001
So I used this query that perfectly runs in MS-SQLServer.

Could you check what is needed on this query  to perfectly run in Access:
SELECT tblCategory.CategoryName,
          aut.AutorName, 
          aut_med.AutorName,
          aut_enc.AutorName,
          tblBooks.BookNo,
          tblBooks.ISBN,
          tblBooks.Author,
          tblBooks.BookTitle,
          tblBooks.YearPublished,
          tblBooks.DateArrived,
          tblBooks.Price,
          tblBooks.Qty,
          tblBooks.Barrowed, ([Qty])-([Barrowed]) AS Available  
FROM (tblCategory 
      RIGHTJOIN tblBooks ON tblCategory.IndexCategory = tblBooks.CategoryIndex) 
      LEFT JOIN tblAutores aut ON tblBooks.AutorEspiritualIndex = aut.tblAutores.IndexAutores  
      LEFT JOIN tblAutores aut_med ON tblBooks.AutorMediumIndex = aut_med.IndexAutores 
      LEFT JOIN tblAutores aut_enc ON tblBooks.AutorEncarnadoIndex = aut_enc.IndexAutores;);

Open in new window


Thanks in advance!
0
Comment
Question by:Eduardo Fuerte
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
  • 4
  • +2
19 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 41765424
>RIGHTJOIN tblBooks ON
Needs to be a space between RIGHT and JOIN.
Also make sure that the RIGHT JOIN is what you intended to do.

>ON tblBooks.AutorEncarnadoIndex = aut_enc.IndexAutores;);
Typo, Remove the first semicolon

>FROM (tblCategory
Looks like this query has one left paren ( and two right parents ), should remove them all.

>Could you check what is needed on this query  to perfectly run in Access:
Are you running it in Access and getting an error?  That would help.

For some more general help going from SQL Server T-SQL to Access SQL you can read the article Migrating your Access Queries to SQL Server Transact-SQL and interpret accordingly.
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 41765481
Try this:
SELECT 
    tblCategory.CategoryName,
    aut.AutorName, 
    aut_med.AutorName,
    aut_enc.AutorName,
    tblBooks.BookNo,
    tblBooks.ISBN,
    tblBooks.Author,
    tblBooks.BookTitle,
    tblBooks.YearPublished,
    tblBooks.DateArrived,
    tblBooks.Price,
    tblBooks.Qty,
    tblBooks.Barrowed, ([Qty])-([Barrowed]) AS Available  
FROM 
    tblCategory 
    RIGHT JOIN tblBooks 
	   ON tblCategory.IndexCategory = tblBooks.CategoryIndex
    LEFT JOIN tblAutores aut 
	   ON tblBooks.AutorEspiritualIndex = aut.tblAutores.IndexAutores  
    LEFT JOIN tblAutores aut_med 
	   ON tblBooks.AutorMediumIndex = aut_med.IndexAutores 
    LEFT JOIN tblAutores aut_enc 
	   ON tblBooks.AutorEncarnadoIndex = aut_enc.IndexAutores;

Open in new window

0
 

Author Comment

by:Eduardo Fuerte
ID: 41765562
Hi

After corrections - reflected in @Zberteoc code

I had this error
img002
(Syntax error - missing operator in query)
0
Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

 
LVL 65

Expert Comment

by:Jim Horn
ID: 41765577
How many spaces are between LEFT and JOIN?  Should only be one.
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 41765580
Try this:
SELECT 
    tblCategory.CategoryName,
    aut.AutorName, 
    aut_med.AutorName,
    aut_enc.AutorName,
    tblBooks.BookNo,
    tblBooks.ISBN,
    tblBooks.Author,
    tblBooks.BookTitle,
    tblBooks.YearPublished,
    tblBooks.DateArrived,
    tblBooks.Price,
    tblBooks.Qty,
    tblBooks.Barrowed, ([Qty])-([Barrowed]) AS Available  
FROM 
    tblCategory 
    RIGHT JOIN tblBooks 
	   ON tblCategory.IndexCategory = tblBooks.CategoryIndex
    LEFT JOIN tblAutores as aut 
	   ON tblBooks.AutorEspiritualIndex = aut.tblAutores.IndexAutores  
    LEFT JOIN tblAutores as aut_med 
	   ON tblBooks.AutorMediumIndex = aut_med.IndexAutores 
    LEFT JOIN tblAutores as aut_enc 
	   ON tblBooks.AutorEncarnadoIndex = aut_enc.IndexAutores;

Open in new window

0
 
LVL 25

Accepted Solution

by:
jrb1 earned 400 total points
ID: 41765594
How about:

SELECT
    tblCategory.CategoryName,
    aut.AutorName,
    aut_med.AutorName,
    aut_enc.AutorName,
    tblBooks.BookNo,
    tblBooks.ISBN,
    tblBooks.Author,
    tblBooks.BookTitle,
    tblBooks.YearPublished,
    tblBooks.DateArrived,
    tblBooks.Price,
    tblBooks.Qty,
    tblBooks.Barrowed, ([Qty])-([Barrowed]) AS Available  
FROM (((tblCategory RIGHT JOIN tblBooks ON tblCategory.IndexCategory = tblBooks.CategoryIndex)
    LEFT JOIN tblAutores AS aut ON tblBooks.AutorEspiritualIndex = aut.IndexAutores)
    LEFT JOIN tblAutores AS aut_med ON tblBooks.AutorMediumIndex = aut_med.IndexAutores)
    LEFT JOIN tblAutores AS aut_enc ON tblBooks.AutorEncarnadoIndex = aut_enc.IndexAutores
0
 

Author Comment

by:Eduardo Fuerte
ID: 41765599
Hi,

Sorry my typing errors.

After correcting still not good:
img003
If you would like, here is Access database (password lib 2006)
MasterFile.mdb
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 41765602
Please translate the error message to English.  The syntax looks correct, ther than the use of the RIGHT JOIN with the subsequent LEFT JOINs.

This assumes that all the table and column names are spelled correctly, and that the columns being joined via the equals sign are of the same data type.
0
 

Author Comment

by:Eduardo Fuerte
ID: 41765607
Of Course:
Syntax error (missing operator) in query expression ....

@jrb1
img004
1
 
LVL 65

Expert Comment

by:Jim Horn
ID: 41765614
Since tblCategory is not in the image in the top of the question, please eyeball that table and make sure the names are spelled correctly.
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 41765615
Try this:
SELECT 
    tblCategory.CategoryName,
    aut.AutorName, 
    aut_med.AutorName,
    aut_enc.AutorName,
    tblBooks.BookNo,
    tblBooks.ISBN,
    tblBooks.Author,
    tblBooks.BookTitle,
    tblBooks.YearPublished,
    tblBooks.DateArrived,
    tblBooks.Price,
    tblBooks.Qty,
    tblBooks.Barrowed, ([Qty])-([Barrowed]) AS Available  
FROM 
    (((tblCategory 
    RIGHT JOIN tblBooks 
	   ON tblCategory.IndexCategory = tblBooks.CategoryIndex)
    LEFT JOIN tblAutores as aut 
	   ON tblBooks.AutorEspiritualIndex = aut.tblAutores.IndexAutores) 
    LEFT JOIN tblAutores as aut_med 
	   ON tblBooks.AutorMediumIndex = aut_med.IndexAutores) 
    LEFT JOIN tblAutores as aut_enc 
	   ON tblBooks.AutorEncarnadoIndex = aut_enc.IndexAutores;

Open in new window

0
 
LVL 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 50 total points
ID: 41765628
take a look at the post by JRB1.  Access requires that in queries with multiple joins that each join be encapsulated by a set of parenthesis.

This is really awkward, but that's the way it is.  Easiest way is to link the tables into Access and then create the query in the query designer, just like you have it displayed above.  Access will add the ( ) pairs for you.
0
 

Author Comment

by:Eduardo Fuerte
ID: 41765643
@Dale

It is just what I'm wondering...
Jrb1 query really works out and I'm trying to understand why...
0
 

Author Comment

by:Eduardo Fuerte
ID: 41765647
@Zberteoc

Your last query produces:
img005
It seens a parameter is needed ?
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 41765675
actually, since you have tblAutores aliased as [aut]  I think you ned to change:

ON tblBooks.AutorEspiritualIndex = aut.tblAutores.indexAutores

to

ON tblBooks.AutorEspiritualIndex = aut.indexAutores
0
 

Author Comment

by:Eduardo Fuerte
ID: 41765690
@Dale

Exactly.
0
 
LVL 27

Assisted Solution

by:Zberteoc
Zberteoc earned 50 total points
ID: 41765692
I used the Accest Query Builder and this is what it built for me:
SELECT 
    tblCategory.CategoryName, 
    aut.AutorName, 
    aut_med.AutorName, 
    aut_enc.AutorName, 
    tblBooks.BookNo, 
    tblBooks.ISBN, 
    tblBooks.Author, 
    tblBooks.YearPublished, 
    tblBooks.DateArrived, 
    tblBooks.Price, 
    tblBooks.Qty, 
    tblBooks.Barrowed, 
    ([Qty]-[tblBooks].[Barrowed]) AS Available
FROM 
    tblCategory 
    INNER JOIN 
    (((tblAutores AS aut 
    INNER JOIN tblBooks ON (aut.IndexAutores = tblBooks.AutorEspiritualIndex)) 
    INNER JOIN tblAutores AS aut_med ON tblBooks.AutorMediumIndex = aut_med.IndexAutores) 
    INNER JOIN tblAutores AS aut_enc ON tblBooks.AutorEncarnadoIndex = aut_enc.IndexAutores) 
    ON tblCategory.IndexCategory = tblBooks.CategoryIndex;

Open in new window

I am afraid this is all I can do at this point.
0
 
LVL 25

Expert Comment

by:jrb1
ID: 41765832
My query worked for 2 reasons.  I fixed the error with:

aut.tblAutores.indexAutores

Also, MS Access requires that the joins be grouped properly.
0
 

Author Closing Comment

by:Eduardo Fuerte
ID: 41765841
Thanks for everyone participation!

The replyes are a very good starting point to a better understand how how things happens in MS-Access.
0

Featured Post

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

732 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question