Solved

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

Posted on 2016-08-20
15
49 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 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

by:Rey Obrero
ID: 41763803
upload a copy of the db.
0
 

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 119

Accepted Solution

by:
Rey Obrero 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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

757 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now