Solved

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

Posted on 2016-08-20
15
59 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
  • 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
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…

809 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