Problem: query from two tables connected to a third table

[sheva]

Hi,
my problem is the following. I have one table that contains BOOKS and one
table that contains ARTICLES. Further I have a SUBJECTS TABLE that contains
different subjects and a code for each subject. The BOOK TABLE is connected
to the SUBJECTS TABLE via a third table thus creating a many-to-many
relationship. Likewise the ARTICLE TABLE is connected to the SUBJECTS TABLE
via another linking table to create a many-to-many relationship. The idea in
both cases is to know which subject is treated by which book/ article.
The problem is how to perform a query, which will show the subjects
contained in both the BOOK TABLE and ARTICLE TABLE. When I try to do one by
using the query wizard and selecting subject name, book name and article name
all i get is an empty table with the three respective fields.
I am using Access 2003 and am quite novice in the field of DB design.

 

[Wolfgang Kais]

Hello "sheva".

Hi,
my problem is the following. I have one table that contains BOOKS
and one table that contains ARTICLES. Further I have a SUBJECTS
table that contains different subjects and a code for each subject.
The BOOK table is connected to the SUBJECTS table via a third table
thus creating a many-to-many relationship. Likewise the ARTICLE
table is connected to the SUBJECTS table via another linking table
to create a many-to-many relationship. The idea in both cases is to
know which subject is treated by which book/ article. The problem
is how to perform a query, which will show the subjects contained
in both the BOOK table and ARTICLE table. When I try to do one by
using the query wizard and selecting subject name, book name and
article name all i get is an empty table with the three respective
fields.
I am using Access 2003 and am quite novice in the field of DB design.

The query that you wanted to design will list all those subjects that
are treated in at least one book and in at least one article. For all
these subjects, every combination of book and article that treat(s)
the subject will be listed together with the subject (provided that
you join all five tables correctly). I don't think that you want such
a result.
I think that a union query would be more appropriate. It should list
all articles and all books that treat a subject, something like this:

Select [Subject name], "article" as what, [article name] as Name
From Subjects INNER JOIN (Articles INNER JOIN LinkSubjectsArticles
ON Articles.ArticleID = LinkSubjectsArticles.ArticleID)
ON Subjects.SubjectID = LinkSubjectsArticles.SubjectID
UNION ALL
Select [Subject name], "book", [book name]
From Subjects INNER JOIN (Books INNER JOIN LinkSubjectsBooks
ON Books.BookID = LinkSubjectsBooks.BookID)
ON Subjects.SubjectID = LinkSubjectsBooks.SubjectID
Order By [Subject name]

 

[sheva]

Thanks very much Wolfgang,
this is exactly what I wanted to do.