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

x
?
Solved

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

Posted on 2016-08-20
15
Medium Priority
?
68 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
Congratulations! You’re Certified – Now What?

Starting a new career can be overwhelming. Becoming certified in your field of expertise is a great start, but where do you go from here?  Here are some tips to help you on your career journey.

 

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 1600 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 200 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 31

Assisted Solution

by:hnasr
hnasr earned 200 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 31

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

715 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