Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 82
  • Last Modified:

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

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
Eduardo Fuerte
Asked:
Eduardo Fuerte
  • 7
  • 4
  • 4
  • +2
3 Solutions
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>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
 
ZberteocCommented:
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
 
Eduardo FuerteAuthor Commented:
Hi

After corrections - reflected in @Zberteoc code

I had this error
img002
(Syntax error - missing operator in query)
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
How many spaces are between LEFT and JOIN?  Should only be one.
0
 
ZberteocCommented:
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
 
jrb1Commented:
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
 
Eduardo FuerteAuthor Commented:
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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
 
Eduardo FuerteAuthor Commented:
Of Course:
Syntax error (missing operator) in query expression ....

@jrb1
img004
1
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
 
ZberteocCommented:
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
 
Dale FyeCommented:
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
 
Eduardo FuerteAuthor Commented:
@Dale

It is just what I'm wondering...
Jrb1 query really works out and I'm trying to understand why...
0
 
Eduardo FuerteAuthor Commented:
@Zberteoc

Your last query produces:
img005
It seens a parameter is needed ?
0
 
Dale FyeCommented:
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
 
Eduardo FuerteAuthor Commented:
@Dale

Exactly.
0
 
ZberteocCommented:
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
 
jrb1Commented:
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
 
Eduardo FuerteAuthor Commented:
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 7
  • 4
  • 4
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now