One record has multiple records

[SueBK]

I have a list of project aspects (Financial, Safety, etc) that project managers must report against each month. For each aspect they assign a status (red/orange/green/NA).

They can also add comments to each aspect. Some don't add any, some add multiples. Each comment includes the comment itself, action required, date and by whom.

I'm thinking I need three tables (all with appropriate IDs):
1. Aspect
2. Report Month, Aspect, Status
3. Month/Aspect, Comment, action, when, whom

I did have it all in one table, but the status is reported against the aspect, not against the comment.

Does this seem logical? (I've had my head in this space for way too long to think with any clarity.)

 

[jzwp22]

How about something like this

tblProjects
-pkProjectID primary key, autonumber
-txtProjName

Since each project will have multiple monthly reports:

tblProjReports
-pkProjReportsID primary key, autonumber
-dteReport (report date)

You need a table to hold the list of aspects

tblAspects
-pkAspectID primary key, autonumber
-txtAspectDesc

Now there are multiple aspects for each report

tblReportAspects
-pkReportAspectsID primary key, autonumber
-fkProjReportID foreign key to tblProjReports
-fkAspectID foreign ket to tblAspects
-status

Since each aspect tied to a report can have multiple comments, you need this table as well

tblAspectComments
-pkAspectCommentID primary key, autonumber
-fkReportAspectsID foreign key to tblReportAspects
-txtComment
-txtAction
-Who
-DueBy

 

[SueBK]

Thanks for your input. I 'think' I may have it working. Forgot to mention I have several dozen other tables already sitting in the DB.

At the moment, I seem to be able to enter data in a logical manner, without double-handling, so I guess that's a good sign.

 

[jzwp22]

It sounds like you are getting things worked out; if you have additional questions, please post back.