However, I believe I can find my way around this issue by creating a query to break up each "step" into its own column and then combining all queries for each step to columnize each step compared to having them listed in rows.
You would be taking a normalized table and de-normalizing it...??
Code:
NumID ANumber TheStep StepValue
1 1234 S1 C
2 1234 S2 NC
3 1234 S3 NC
4 4567 S1 C
5 4567 S2 C
6 4567 S3 C
7 1234 S4 NC
Given the above normalized table, you could use a crosstab query
Code:
TRANSFORM First(StepTable.StepValue) AS FirstOfStepValue
SELECT StepTable.ANumber
FROM StepTable
GROUP BY StepTable.ANumber
PIVOT StepTable.TheStep;
to produce a result like:
Code:
ANumber S1 S2 S3 S4
1234 C NC NC NC
4567 C C C
(See attached mdb)
Essentially, my "status" table will have to have a lay out of every possible step definition possible. For example, in my first post the status "Phase 1 complete" has different step definitions that arrive to that status. So as you may imagine, with 10-12 steps total, there will be plenty of different combinations of step definitions to cover.
But I'm trying to find out if it is even possible to use a table as a guide to status a record based on given criteria for each step.
Yes, with enough code, it is possible (and not that much code is required).
BUT..., without examples, I'm kinda stuck. Need to see your "Number" table and the "Status" result table. Care to attach the project? (Preferably in A2000-2003 format)