Multiple criteria to get data from other table?

[wes]

Wonderful! You've given me a great map, I think this will do just what I wanted for the milestones. I still need to figure out how to handle the financial obligations, but it occurs to me that perhaps it will work as just another milestone, as it has an expected time frame and an actual timeframe. Hmmm . . . Now it's time to go play with some data! :-)

[jzwp11]

Good luck with the project & please post back if you have questions on the design.

[wes]

I've been playing with the structure and liking it! Of course I have more questions now.

As I'm entering the milestones I realize I have a lot of repetition. In tblMilestones there will be multiple instances of two or three records that pertain to the same basic milestone but show a different level of completion. (For example, PlannedDraftComplete, ActualDraftComplete; or PlannedDateToEditorial, ActualDateToEditorial, and EditComplete.) Does this mean this table is not normalized? Should tblMilestones just have the name of each milestone (DraftComplete, Editorial), and somehow with the actual projects show the different phases? The issue is, in the ContentCreation phase we only need to track the date we plan to complete a milestone, and date we actually complete it. In the ContentProduction phase, we need to track the PlannedDateTo, the ActualDateTo, and the DateCompleted.

I'm afraid this may just become overly complex, so I'm ok with leaving it with the mutiple records in a combined tblMilestones, but I wanted to check before I went forward in case I was missing something that would limit the future utility of the information.

[jzwp11]

Well, it really depends on what you call a milestone

As an example, I would consider "Planned Draft" as the milestone and then in a related table I would have actions relative to that milestone, such as sent to editorial, completed or any other actions you do relative to the Planned Draft