Database form advice

[muriel]

I have a database (concerning bibliographic data) with the following tables:

Main Table
Title of Document
Author1ID
Organization1ID
Author2ID
Organization2ID
Author3ID
Organization3ID
Author4ID
Organization4ID

Author Table
Author1ID
Author1

Organization Table
Organization1ID
Organization1

Author Organization Table
Author1IDOrganization1ID
Author1
Organization1

Repeat above tables for Author & Org 1-4. The reason I split Author and Org into 4 separate tables is because each org can have many authors, and each author can have more than one org. The author to organization relationship is many-to-many - that's why I created the AuthorOrg table above.

Now I'm trying to figure out how to design a form so that the data gets stored in the right table. I also need to have lookup tables for authors and organizations.

I also need to query the database to get:
1) all authors and associated organizations
2) all organization and associated authors

Any advice - I'm going round and round with this. I'd really appreciate any insight.

Thanks.

 

[psemianonymous]

This depends on how your author-organization-document merry-go-round works. For *certain*, you need to eliminate the Author(#) tables/fields and move them all to one table/field. What I'm saying is that instead of storing the author/organization information 4 times in the documents table, put it elsewhere:

Make a new table. Call it "Document-(Author-Organization)". You then have two foreign keys; one for the "Author-Organization" ID, and one for the Document ID, which apparently is "Title of Document".


I hope I haven't confused you. Whiteboards are extremely useful, especially when you're in the same room. We have neither of those things.

--
Find common answers using Google Groups:

http://groups.google.com/groups?group=comp.databases.ms-access

Corrupt MDBs FAQ

http://www.granite.ab.ca/access/corruptmdbs.htm

 

[muriel]

Thanks for your suggestions. I appreciate it.

One problem is that when users are entering the data (via a data entry form), one document may have 4 separate authors who belong to 4 separate organizations. If I store them in one field, won't that make the query process more difficult? Also, one organization may have 4 separate authors.

I have to produce reports listing the authors & orgs; orgs & authors; and authors & documents. That's my main goal.

Thanks again for your help.

 

[psemianonymous]

I don't know the exact nature of the relationships, but this is the deal: if you have from 1-4 authors per document, then it is important that you put the "author list" in a separate table. Yes, this will make data entry more complicated. You will have to use a subform of some sort to put in (up to) each of the four authors. But. Querying on the reporting side will be much simplified, enough that I believe it is worth it to put the "author list per document" in a separate table.


Now let me amend what I wrote in the last paragraph: I don't know your relationship structure exactly. But I believe you have a situation where you need to include a "Author-Organization ID" link instead of "Author ID" and "Organization ID" link. This is confusing to my brain, and thus confusing to express what I'm attempting to describe.


Give an example and maybe I'll amend my recommendation one more time. But for now, it's

[Document table]
DocID *PRIMARY*
Title of document

[Co-Author Of Document table]
CoAuthorID *PRIMARY*
Author-OrganizationID
DocID

[other tables]