Data entry for many to many (I thank!)

[megadan]

I have a database consisting of 5 Tables. This is alot so please bare with me...

STUDY TABLE
fields...Protocol#(KEY), StudyDiscription, Phase, ect.
INVESTIGATOR TABLE
fields...PIID(KEY), PIFirstName, PILastName, Address, ect.

DOCUMENT TABLE
fields...DocId(Key), DocumentType,Received/Sent, DateSent<>DateReceived(depending if document is being sent or received from PI),ect.

REVIEW TABLE
fields...RevID(Key), Type of Review, Reviewer, DateSubmittedForReview, DateReviewComplet,Accepted(Yes/No)

LINK TABLE(many to many relationship)
fields...Protocol(key), PIID(key), DocId(key), RevId(key) (linked to respective table)

Discription:
A study can have many investigators and these same investigators can be involved with many different studies. In order for the investigators to participate they must submit many different documents for review. Their are three different types of review(ex. Corporate Review, Clin Ops Review, Reg Review). The type of documents determines the first type of review(ex. ICF Doc = Clin Ops Review/ CRA Doc = Corporate Review). After the documents have been checked by clin ops reviewer, or corporate reviewer and excepted they must be submitted as a whole packet(all doc's together) for Reg Review. And then if accepted by reg review can PI's may particpate.

Questions:
1. Are my relationships correct or should their be any additions/deletions in tables.

2. If my relationships are correct(and I belive they are)can someone assist me in setting up a data entry form.

I have been working on this database for some time now and have ran into some many snags that has held me up and now my time is running down on me for completing this task. So any help is greatly appriciated. I just can't seem to get my data linked correctly. I belive that creating the forms has been the biggest problem for me.
Sorry about the long post
and thanks!

 

[elizabeth]

Don't understand &quot;And then if accepted by reg review can PI's may particpate.&quot; You also didn't mention if you need to track whether a reviewer rejects a document then later accepts it, or whether you only have to note the data a document is finally accepted. Based on what I do understand, if you need to track multiple review per review type, I would suggest athe following:

tblStudy: same as you already have for STUDY TABLE, but wth a &quot;complete&quot; indicator.
tblStaff: similar to tblInvestigator, but also includes reviewers.
tblDoc: same as DOCUMENT TABLE but incl field for author(investigator) and date completed or submitted.
tblReviews: similar to REVIEW TABLE except for date submitted (as that belongs to tblDocument) plus the fields in LINKING TABLE:
Protocol, Document, Review Type, Review Date, Reviewer, Decision (Y/N), Comment (probably).

If you need to note only one review per review type, I would suggest adding all the tblReview fields to tblDocument (3 sets, or whatever, of type, reviewer, date, decision, comment).

To make sure you can tell when it's appropriate to flag a Study as Complete, I suggest you create a one-to-many form that shows tblStudy fields at top, the document/review data in row format at the bottom. If multiple review records per document, ordered by Doc then Review Type then Review Date.