Record count based on status

[kimberlyb]

I need to write a query that will yeild a report that counts the number of
projects based on date fields. For example: a project might be in an
upcoming status based on 1. first record date and 2. no engineer start date.
If the first record date is 11/01/07 and the engineer start date is 2/1/08,
the project is considered to be in upcoming status for November 2007,
December 2007, and January 2008. It then is considered to be in design
status for February 2008. Can anyone offer any tips on how to write this
query?

Thanks -KB

 

[KARL DEWEY]

More rules are needed. It looks like ---
no engineer [start date]) = "upcoming status"
engineer [start date] = "design status"

I think your best bet would be to develop a Truth table matrix using every
factor as fields and Status in each record with all factors requiered either
True or False.

[Status] [record date] [engineer start date] [design complete date]
upcoming status TRUE FALSE FALSE
design status TRUE TRUE FALSE
construction TRUE TRUE TRUE