Status reports

[stosh59]

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.

[LillMcGill]

In the Comments table, CommentID should be the PK. (With Proj as the PK, you can't enter multiple comments for a project.) Leave the join to ProjectID as it is. Add a CommentDate field.

ProjectID is the PK in the Projects table, right?

[stosh59]

Thanks. The CommentID was going to be a date since comments are entered weekly (e.g., 20110107).

No, ProjectID is not the PK...but based on your question, I am thinking I should change that. Is that the recommendation?

Thank you, Lill, for responding so quickly. Your suggestions fixed the problem!!!