Query Criteria Problem: Include all of [Field-A] where one of [Field-B] (for [Field-A]) = "Criteria"

[prs312]

I'm having an issue specifying the correct criteria to filter my query. The criteria is based on two fields:
[Field-A] = SubjectID
[Field-B] = PhaseName

Subjects advance through Phases in the following order:
"Screening">"Post-Implant">"Optimization">"Randomized Testing">"Follow-up"

I'm trying to only include data in the query of subjects who have at least reached the "Optimization" phase. However, if they have data from later phases I want to include that too. Attached is an example of what this query currently looks like, and boxed in red are the only parts I would like to maintain. I greatly appreciate any help with this problem.

[June7]

So you want all the records for each SubjectID that have reached the Optimization phase. There will always be an Optimization before there can be "Randomized Testing" and "Follow-up"?

SELECT * FROM tablename WHERE SubjectID IN (SELECT SubjectID FROM tablename WHERE PhaseName = "Optimization");

If you want to be able to sort by the phase sequence, need to assign an alpha or number value to each phase (or a date value could be used to sort, unless phases could occur in same day). So in query calculate a field with:

Switch([PhaseName="Screening",1 , [PhaseName]="Post-Implant",2, [PhaseName]="Optimization",3, [PhaseName]="Randomized Testing",4, [PhaseName]="Follow-up",5)

Alternatively, have a table of these phase names with a field for the order value. Include that table in query.

[prs312]

Yes. And your suggestion did just that. Thanks very much June!