Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2016-08-22
19
Medium Priority
?
77 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 66

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
Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

 
LVL 66

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 1600 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 66

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 66

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 48

Assisted Solution

by:Dale Fye
Dale Fye earned 200 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 48

Expert Comment

by:Dale Fye
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 200 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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

721 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