Full disclosure: I am an Access wannabe. So I barely know what I am doing.
I am trying to move a weekly status report from Excel to Access. Reporting will be easier as will analysis. I have four tables:
1) tblProject:
tablekey (PK)
ProjectID
Tower
ProjNam
PM
Start
End
Status
CmntID
2)tblPM
pmID (PK and linked to PM in tblProject)
PMFnam
PMLnam
3) tblStatus
StatID (PK and linked to Status in tblProject)
StatNam
4) tblComment
ProjectIDCmt (PK and linked to ProjectID in tblProject)
CommentID
Comment (memo)
My need is to be able to keep a history of comments by projectID. So for instance, I have project "X". It starts on Jan 10 and will end on Feb 27. Each week I need to add comments about the project status. I also need to be able to see the comment history for project X throughout the project life and even after it ends.
My problem is I can't figure out how to link the comment table to the project table and maintain that history. I have been unable to tie the comments to a particular project. What am I missing?
Thank you in advance.