Solved

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

Posted on 2016-08-22
19
45 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
  • 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 26

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
 
LVL 65

Expert Comment

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

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
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
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 26

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 26

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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

747 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now