Solved

Could you point what must be changed to make this Access query to correclty run?

Posted on 2016-08-20
15
62 Views
Last Modified: 2016-08-22
Hi Experts

Could you point what must be changed to make this Access query to correclty run?

Accordingly with

SELECT tblAutores.AutorName, tblCategory.CategoryName, tblBooks.BookNo, tblBooks.ISBN, tblBooks.Author, tblBooks.BookTitle, tblBooks.YearPublished, tblBooks.DateArrived, tblBooks.Price, tblBooks.Qty, tblBooks.Barrowed, ([Qty])-([Barrowed]) AS Available 
                       FROM tblBooks
                       LEFT JOIN tblCategory ON tblBooks.CategoryIndex = tblCategory.IndexCategory 
                       LEFT JOIN tblAutores  ON tblBooks.AutorIndex = tblAutores.IndexAutores ORDER BY tblBooks.BookNo ASC

Open in new window


Considering

tblBooks is the base table
tblAutores has a FK to tblBooks - presenting the Author Name atribute

tblCategory has a FK to tblBooks - presenting the Book's Category

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
  • 3
  • 3
  • +1
15 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 41763725
what is wrong with your query?

try this query

SELECT tblAutores.AutorName, tblCategory.CategoryName, tblBooks.BookNo, tblBooks.ISBN, tblBooks.Author, tblBooks.BookTitle, tblBooks.YearPublished, tblBooks.DateArrived, tblBooks.Price, tblBooks.Qty, tblBooks.Barrowed, ([Qty])-([Barrowed]) AS Available  
                      FROM tblBooks
                      LEFT JOIN tblCategory  ON tblBooks.CategoryIndex=tblCategory.IndexCategory
                      LEFT JOIN tblAutores ON tblBooks.AutorIndex = tblAutores.IndexAutores
                                ORDER BY tblBooks.BookNo
0
 

Author Comment

by:Eduardo Fuerte
ID: 41763750
Hi

I see you omitted ASC but still doesn't work.

I really don't know why this query doesn't return lines.

This query runs inside a C#/ Access  - code that I'm adapting to my purposes.

This original code runs perfectly
SELECT tblCategory.CategoryName, 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 ORDER BY tblBooks.BookNo ASC

Open in new window


In this case it has just one FK
tblCategory has a FK to tblBooks - presenting the Book's Category

But I need another FK reference
tblAutores has a FK to tblBooks - presenting the Author Name atribute
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 41763803
upload a copy of the db.
0
Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

 

Author Comment

by:Eduardo Fuerte
ID: 41763820
Ok, of course!

password:    lib2006
0
 

Author Comment

by:Eduardo Fuerte
ID: 41763822
0
 
LVL 2

Expert Comment

by:Antonio Salva Ripoll
ID: 41763904
Hi @Eduardo Fuerte.

I've found an error in the tblBooks table.

You don't have the AutorIndex field, so, you can't do a join between the tables using that field.

Best regards.

Antonio (Barcelona, Spain)
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 400 total points
ID: 41763906
try this query

SELECT tblAutores.AutorName, tblCategory.CategoryName, 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 ON tblBooks.AutorEspiritualIndex = tblAutores.IndexAutores;
0
 
LVL 2

Assisted Solution

by:Antonio Salva Ripoll
Antonio Salva Ripoll earned 50 total points
ID: 41764203
Hi

To accomplish what you are looking for, you must modify your query and remove the join with the Autores table.

The following SQL query runs without error and returns the data correctly:

SELECT tblBooks.Author, tblCategory.CategoryName, tblBooks.BookNo, tblBooks.ISBN, tblBooks.BookTitle, tblBooks.YearPublished, tblBooks.DateArrived, tblBooks.Price, tblBooks.Qty, tblBooks.Barrowed, ([Qty])-([Barrowed]) AS Available
FROM tblBooks LEFT JOIN tblCategory ON tblBooks.CategoryIndex = tblCategory.IndexCategory
ORDER BY tblBooks.BookNo;

Open in new window


Best regards.

Antonio (Barcelona, Spain)
0
 
LVL 30

Assisted Solution

by:hnasr
hnasr earned 50 total points
ID: 41764712
Check your join fields. I tried to design the query from your sql.
This is the result, simplified form.

SELECT tblAutores.AutorName, tblCategory.CategoryName, tblBooks.BookNo
FROM tblAutores INNER JOIN (tblCategory INNER JOIN tblBooks ON tblCategory.IndexCategory = tblBooks.CategoryIndex) ON tblAutores.IndexAutores = tblBooks.AutorEncarnadoIndex;

Open in new window


I just used one existing field from tblBooks for the join condition.

AutorName	CategoryName	BookNo
Math	Computer Books	B00000000001

Open in new window

0
 

Author Comment

by:Eduardo Fuerte
ID: 41765301
Hi for All

Thanks for replies

@Antonio  
I can't ommit Autores table since it has the Author's name.

@hnasr
The query returned just 01 line.

@Rey
Your query runs ok.
But just one more thing that is going to be subject of other question derivated on how to make several joins using the same table, since the same book could have more than 01 author.
0
 
LVL 30

Expert Comment

by:hnasr
ID: 41765391
@hnasr
The query returned just 01 line.
Right because of the join field used, it has a value in one record.

I expect you to comment on the query if it supports my understanding to the issue.
I modified the field, from Ray's comment since you mentioned it runs OK,  and used a left join:

SELECT tblAutores.AutorName, tblCategory.CategoryName, tblBooks.BookNo
FROM tblAutores RIGHT JOIN (tblCategory INNER JOIN tblBooks ON tblCategory.IndexCategory = tblBooks.CategoryIndex) ON tblAutores.IndexAutores = tblBooks.AutorEspiritualIndex;

Open in new window

0
 

Author Comment

by:Eduardo Fuerte
ID: 41765419
0
 
LVL 2

Expert Comment

by:Antonio Salva Ripoll
ID: 41765441
Hi @Eduardo Fuerte.

In the sample database you post, the tblAutores table hasn't the author name, this value is contained in the tblBooks table.

Based on that sample, I posted my answer.

If that is not right, please, check the sample and upload it again.

Best regards.
0
 

Author Comment

by:Eduardo Fuerte
ID: 41765851
Hi @Antonio

You are right in one point:  the tblBooks table really has an Author Name column, that must be excluded later.

Since I'm adapting the code obtained elsewhere and the most correct way is to mantain a separate table to Authors (and in my case a book could have 01 or 02 authors) I started to use a FK relation - accordingly the other question I posted) - and I didn't excluded the column yet.

By the way
The only thing I don't like about Barcelona is 6 x 0 Santos F.C.
0
 

Author Closing Comment

by:Eduardo Fuerte
ID: 41765857
Thanks for everyone participation!
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
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…

740 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