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

>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.

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;

Select allOpen in new window

Hi

After corrections - reflected in @Zberteoc code

I had this error

(Syntax error - missing operator in query)

How many spaces are between LEFT and JOIN?  Should only be one.

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;

Select allOpen in new window

Hi,

Sorry my typing errors.

After correcting still not good:

If you would like, here is Access database (password lib 2006)
MasterFile.mdb

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.

Of Course:
Syntax error (missing operator) in query expression ....

@jrb1

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.

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;

Select allOpen in new window

@Dale

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

@Zberteoc

Your last query produces:

It seens a parameter is needed ?

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

@Dale

Exactly.

My query worked for 2 reasons.  I fixed the error with:

aut.tblAutores.indexAutores

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

Thanks for everyone participation!

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