Adding tasks to existing database

[BillA]

Having a little difficulty with what I believe should be simple.

I have a project tracking db, which tracks project until it is approved.
I’ve been asked to introduce additional tasks; Renewals and Amendments.
Unlike tracking the approval process, a project will have multiple renewals
and amendments. Each renewal and amendment will have many of the same fields
used to track the project approval (mostly date fields). Additionally, I
would need to introduce commenting for each instance of a renewal or
amendment, and each renewal or amendment may have zero or many comments.

I’m having designer block. I would like advice for how to introduce the
renewal and amendment, and connect corresponding comments (from a comments
table?).

Ultimately, from a users perspective, we would like to have a form that
shows project information and will have tabs for “Application†“Renewal†and
“Amendment.†I’ve included the main table named “tbl_Project†which the
renewal and amendment would be tied to.

Table: tbl_Project
ProjectID
RequestType
ProjectNumber
DateReceived
StudyTitle
StudyHasWebPage
ApprovalDate
StatusDescriptionID (lookup – open/closed/withdrawn/pending/denied)
[Etc…]

Thank you for any advice.
Bill

 

[Steve]

The answer to your question lies in something you said in your post:
<<a project will have multiple renewals and amendments>>
You have a one to many relationship with a project - a project may have
multiple renewals and amendments. Therefore you need another table to record
renewals and amendments.
TblRenewalsandAmendments
RenewalsandAmendmentsID
ProjectID
RenewalOrAmendment
<other fields as needed>

RenewalOrAmendment could be an option group if you never will have more than
the two elements. Or you could add another table:
TblType
TypeID
Type (Renewal, Amendent)
and use a combobox to enter RenewalOrAmendment.

Regarding comments, if you only need to record comments and will never do
any type of analysis of the comments, you can use a memo field to enter
multiple comments. If you want to be able to analyze comments or to report
separate comments, you need another table to record separate comments:
TblComment
CommentID
RenewalsandAmendmentsID
Comment