IS-A relation (URGENT) 1

[LMichel]

How can I define a IS-A relation within access ?

For exemple:

tbl_doc:
IdDoc (primary key)
Title
Keyword

tbl_book:
ISBN (primary key)
Editor

Tbl_Report:
ReportCode (Primary Key)
Project

In this exemple a book IS A document
a report IS A document



Thanks for your help

 

[tlbroadbent]

I'm not sure that I fully understand the question. There is no "IS A" function in Access or JET SQL. Relationships are established by having common columns in different tables. I'm unclear about what relationship (if any) exists between the tables in your database.

I assume that tbl_Doc contains records for all documents while tbl_Book and tbl_Report contain records for books and reports, respectively. Books and reports could (should?) also be listed in tbl_Doc and have an IdDoc. A book or report would be identified as a document if listed in tbl_Doc.

If these assumptions are correct, you could add the IdDoc column to tbl_Book and tbl_Report to relate records to corresponding records in tbl_Doc. You could then use JOIN queries or correlated sub queries to identify if a "Book (or report) is a Document."

Example:
Select ISBN, Editor, tbl_book.IdDoc, Title
From tbl_Book Inner Join tbl_doc
On tbl_Book.IdDoc=tbl_Doc.IdDoc

I don't know if this is what you wanted. Let me know if it helped. If not, post some additional information for clarification. Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[JerryDennison]

You should not have multiple tables that define the same type of entity. Since a book and/or report are simply different types of documents they should all be held in the same table. Add a descriptor (i.e. field) that lets you define the type of document it is (i.e. book, report, manuscript, whatever).

 

[tlbroadbent]

On the contrary, I may have different tables for books and reports because they have different attributes. Note the attributes (or columns) in LMichel's tables. Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[JerryDennison]

I see. What threw me was there hasn't been established a relationship between tblDocs and it's subtables tblBooks or tblReports. You are correct.

 

[LMichel]

Here is some additional information

A document is either a report or(exclusive) a book.
Every book and every report is a document.
They are sometimes more than one document based on a book/report (several copies)

So, I think relation ship should be something like this:

http://club.euronet.be/sonimage/db/rel1.jpg

But, I can't encore referential integrity. If I do that, I'll be obliged to enter a book AND a report for each document. By Definition a document can't be a book and a report.

 

[tlbroadbent]

If properly designed, IDDoc would be a primary key on Tbl_Doc and a foreign key on the Tbl_Book and Tbl_Report. The foreign key could be used to enforce referential integrity but not in the way you describe. Entries in Tbl_Doc would not have to be on the other two tables. However, you would define it such that every entry on Tbl_Book and Tbl_Report would have to have a related entry in Tbl_Doc.

Let me know if you have additional questions. Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[LMichel]

The design should look like this:

http://club.euronet.be/sonimage/db/rel2.jpg

With this definition it is mandatory that a document exist for each report or each book. This is not what I want to do.
I'd like to first create records for books and reports and then create documents referring to a report or book.
It is not mandatory that a document exist for a report/book.
Many documents can refer to a book/report.

Thanks for your help

 

[tlbroadbent]

The answer is very simple. Access only enforces relationships that you define. If you don't want the relationships, don't define them.

You can still join the tables without having the relationships defined. You can also define the relationship as an outer join, such that any time the tables are joined, you can select all book or report records and only doc records that match.

Select ISBN, Editor, tbl_book.IdDoc, Title
From tbl_Book Left Outer Join tbl_doc
On tbl_Book.IdDoc=tbl_Doc.IdDoc
Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.