Getting Access to Look Past the first Criteria

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

All,

I am using Access 2002 and have a database of 34 records. Each records
tracks up to 16 milestones (they all have a minimum of 5) and has a status
for each milestone. The status is given as a color - green, yellow, red, gray
or white. I need to pull ALL of the records that have ANY milestone of red,
yellow, or gray. (e.g., in a record, the first 3 milestones may be green,
but the 4th, 5th, and 9th may be yellow.) I tried building a query in using
or statements ("red" or "yellow" or "gray"), but I consistently get 8
records, all of which only use the criteria from the first milestone (in
other words, if the 1st milestone in the record isn't one of those colors, it
won't return that record.) I should get about 15.

Does this make sense? Anyone out there have any ideas where I can get Access
to look beyond the 1st milestone in each record?

Any help is more than appreciated.

Lori
 
Your problem is that your tables are not designed correctly. You need:
TblProject
ProjectID
<<Whatever other fields you need>>

TblMileStone
MileStoneID
ProjectID
MileStoneDescription
MileStoneStatus 'your colors
DateOfMileStone

You then create a query that includes both tables pulling down the fields
you need from TblProject and MileStoneStatus from TblMileStone. Set the
criteria of MileStoneStatus to "Red". You will then get a list of all
projects that have a Red status in any of the milestones.
 
Just use "OR" statements. Since you did not share the names of your fields,
I'll give you a generic answer. Something like...


SELECT * FROM SomeTableNameHere
WHERE (((SomeTableNameHere.Milestone01)="Red")) OR
(((SomeTableNameHere.Milestone02)="Red")) OR
(((SomeTableNameHere.Milestone03)="Red"))...


Rick B
 
PC Datasheet said:
Your problem is that your tables are not designed correctly. You need:
TblProject
ProjectID
<<Whatever other fields you need>>
Click to expand...

Careful about putting yourself forward as an authority on table
design. You may get some smart alec telling you you Tbl prefixes are
redundant and in contravention of ISO 11179 standards on metadata
element naming <g>.

Jamie.

--
 
Back
Top