Hi,

I am creating a listing of documents in ASP that contain the Title of the
document and its authors. In Access I created a table for the documents
(title, number, etc.) and one table for the authors. Also I created another
table that connects the two since there are documents with more than one
author and authors that have more than one document.

The problem I am having is creating a Master Detail Page Set in following
format:

Document Number 1, Title 2, Authors
Document Number 2, Title 2, Authors

I don't know if I have to create a special recordset or a query in Access.
I created a Query but it doesn't work in UltraDev, one of the tables records
don't show up.

Many thanks for any assistance,

Ed

Hello,

2 tables will suffice, the relation type needs to be: 1 author to many documents.
Once you have this you can join the author name in the query resulting in something like:

SELECT documents.docID, documents.Title, authors.authorName
FROM authors INNER JOIN documents ON authors.authorID = documents.Author

This would work in a simple example i created for you:
access 2000 version dennisvg.homeip.net/UDzone/documents.mdb
access 1997 version dennisvg.homeip.net/UDzone/documents_oldAccess.mdb

Your third table creates a hiccup in your relations, resulting in 1 document can have multiple authors with unsatisfying results.

Let me know if you need more assistance.

With kind regards,

Dennis van Galen
Webmaster KPN Services
Financial and Information Services

Hello Dennis and thanks for the help.

I checked your database but the problem seems to be that some documents have more than one author and currently I can only add one author per document.

Thanks for the great help.

ummm, i missed that little piece of info.

let me re-adjust <img src=../images/dmxzone/forum/icon_smile_wink.gif border=0 align=middle>

the sql with the inbetween table in place becomes:

SELECT documents.docID, documents.Title, authors.authorName
FROM documents INNER JOIN (authors INNER JOIN docauthors ON authors.authorID = docauthors.author) ON documents.docID = docauthors.doc
ORDER BY authors.authorName

try the earlier links now, i updated it.

Open it in MS-Access and look at how the relationships are setup. Then look in the queries tab and open the query i made in design view to see how i did it.

With kind regards,

Dennis van Galen
Webmaster KPN Services
Financial and Information Services

and before you ask, you can remove the author field from the documents table now.
And you need a webbased entry thingy with a form with 2 dynamic select menus if you want to add authors to your documents or you can use checkboxes if you figured out how to loop a insert function, i'm still trying to get that one to work in Jscript, there's some examples around in VB though.

With kind regards,

Dennis van Galen
Webmaster KPN Services
Financial and Information Services