Criteria: If "All" then omit 'Remainder'

  • Thread starter Thread starter OzeAuditor
  • Start date Start date
O

OzeAuditor

My mdb has an Aspects_Table (many) and an Areas_Table
(many) linked by AspectsAreas_Table. In both primary
tables the lists of Aspects and Areas have an entry "ALL"
meaning 'All Aspects' and 'All Areas' respectively,
intended to avoid repetitively listing each Aspect and
Area in reports and forms. There is a similar
relationship between Aspects and Requirements (ie Many-to-
Many via a Link_Table).

This wasn't wise in hindsight because now I can't figure
out a Query Criteria that will detect the "All" entry has
been returned in a SELECT Query and thus omit all the
other (otherwise) valid selections. When I'm reporting
the areas that relate to the aspects that relate to my
(another table) Requirement, I want my query to
return, "This Requirement applies in the following
Areas, eg.

Areas = All",
not
"Areas = Area1, Area3, Area9, (for Aspect1 and then) All
(for Aspect2)".

Can I make MSAccess detect the presence of 'All' in its
selection and then omit the other selection from the
report? Or should I go back to design of my tables and
figure out another way to indicate 'All' records in a
table fit the criteria for a report?

TIA
OzeAuditor
 
This is JOPO (just one person's opinion), so take it with a grain of salt...

I'd be more inclined to leave "All" off, but keep the structure. I'd manage
an "All" situation via my forms and code behind forms. If "All" Areas was
appropriate, I'd use code to write every Area into the relation/junction
table.

Similarly, I'd compare a list of chosen Areas against the full list -- if
there's "missing" items, it wasn't "All".

Good luck

Jeff Boyce
<Access MVP>
 
Back
Top