• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 87
  • Last Modified:

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

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
Eduardo Fuerte
Asked:
Eduardo Fuerte
  • 7
  • 3
  • 3
  • +1
3 Solutions
 
Rey Obrero (Capricorn1)Commented:
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
 
Eduardo FuerteAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
upload a copy of the db.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Eduardo FuerteAuthor Commented:
Ok, of course!

password:    lib2006
0
 
Eduardo FuerteAuthor Commented:
0
 
Antonio Salva RipollCommented:
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
 
Rey Obrero (Capricorn1)Commented:
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
 
Antonio Salva RipollCommented:
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
 
hnasrCommented:
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
 
Eduardo FuerteAuthor Commented:
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
 
hnasrCommented:
@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
 
Eduardo FuerteAuthor Commented:
0
 
Antonio Salva RipollCommented:
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
 
Eduardo FuerteAuthor Commented:
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
 
Eduardo FuerteAuthor Commented:
Thanks for everyone participation!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

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