Looping to find status

[J-Mack]

My issue.... I am trying to determine the overall status of a record
based on seven status categories.

STAFF
DEVELOPMENT
DEPLOYMENT
ISSUES
SCHEDULE
SCOPE
QUALITY

Each of these categories is a field that can be green (1), yellow (2),
or red (3). If any of the 7 are red, then the overall is red.... if 3
of the seven are yellow, then the overall is yellow. I have been
racking my brain trying to determine how to best code this. I thought
a loop, but not sure how I could loop through them. Thought about
summing the fields, but that won't work either.

The fields are currently seven separate option boxes:

GREEN
YELLOW RED
STAFF
DEVELOPMENT
DEPLOYMENT
ISSUES
SCHEDULE
SCOPE
QUALITY

Any insight would be most appreciative. I may be trying to make this
too difficult. lol.

 

[Jeff Boyce]

I'm having trouble visualizing the underlying data structure. What you
described sounds a bit like a form, not a table.

Are you saying that you have 7 fields in your table, each field holding a 1,
2, or 3? If so, your table structure might benefit from further
normalization, as a data design like that ("repeating fields") is what you'd
expect to find for a spreadsheet, but makes you (and Access) work a lot
harder in a relational database.

It sounds like you could add the field values to help determine the overall
....

If the sum is 10 or greater (4 @ 1, plus 3 @ 2) AND if none are a 3, you
have "yellow".

If any are 3, you have "red".

This might be handled in a query, using IIF() statements (untested
speculation...).

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[J-Mack]

I'm having trouble visualizing the underlying data structure. What you
described sounds a bit like a form, not a table.

Are you saying that you have 7 fields in your table, each field holding a 1,
2, or 3? If so, your table structure might benefit from further
normalization, as a data design like that ("repeating fields") is what you'd
expect to find for a spreadsheet, but makes you (and Access) work a lot
harder in a relational database.

It sounds like you could add the field values to help determine the overall
...

If the sum is 10 or greater (4 @ 1, plus 3 @ 2) AND if none are a 3, you
have "yellow".

If any are 3, you have "red".

This might be handled in a query, using IIF() statements (untested
speculation...).

Regards

Jeff Boyce
Microsoft Office/Access MVP









- Show quoted text -

Yes.... It is a form. My apologies.

That is a great solution. I thought about adding them up, but I
couldn't get past the "red" status. lol.

Again, thank you.

JT