Advice on db design

[kalmi]

I could use some suggestions on how to set up part of my database. I have a table with about 21 fields to captures dates that activities were completed for each project. The dates are entered via a form. So for example:

Field1 = Receive Date
Field2 = Review Date
Field3 = Returned results of initial submission
Field4 = Resubmission Date
Field5 = Review Date2
Etc.

What I need to get out of the table is the status of each project. So, for example, if Receive Date is null or if Review Date is not null for a project I need the report to read:

Project Status
Project A “We have not received the submittal”
Project B “We have finished our review”

I tried adding a "status" field to the table that would store the message, and then updating the message via code on the input form as the dates were filled in for each project but couldn't get it to update properly. Then I thought this would be better handled at the query or report level but I'm a litle stuck as to the best method to use. Any suggestions?

 

[ted.martin]

Have you tried DLOOKUP for the appropriate msg stored in a table depending upon the values of your field?

It would be quite a complex Criteria with lots of AND's but should be do-able.

An alternative you be to make a table with the msg's in that incorporates the field values which would all need to be present for the msg to show. Either way, you need to create these 'msg rules'.

 

[kalmi]

Thanks for responding. I don't think a regular table with messages will work because the fields are all dates that get filled in as the project progresses so it won't really be possible to link the two tables explicitly through a query. A lookup table might work, I've never used them before so I'll have to check into it. Thanks again for the idea.