Filtering duplicate participants from query based on a study name

  • Thread starter Thread starter Puciral
  • Start date Start date
P

Puciral

I have been working on a database that matches participant demographic
information to the studies they have participated in.
Each study and participant has unique autonumber IDs and I have combined
them both in a separate table with dates.
Participants have taken multiple studies and thus they show up multiple
times in a query.

Filtering out a study only cancels out the participants that have taken the
study and not all instances of the participant. I want to be able to take
the experiment name from a form such as
[Forms]![ExperimentFilter]![Expfilter] and then have that filter out all the
PartID associated with that StudyID.

Right now I have this in my query:
SELECT DISTINCT Hess_main_participant_table.Part_ID,
Hess_main_participant_table.Last, Hess_main_participant_table.First,
Dates_for_each_study_and_Participant.StudyID
FROM Dates_for_each_study_and_Participant INNER JOIN
Hess_main_participant_table ON Dates_for_each_study_and_Participant.Part_ID =
Hess_main_participant_table.Part_ID
GROUP BY Hess_main_participant_table.Part_ID,
Hess_main_participant_table.Last, Hess_main_participant_table.First,
Dates_for_each_study_and_Participant.StudyID
HAVING
(((Dates_for_each_study_and_Participant.StudyID)=[Forms]![ExperimentFilter]![Expfilter]))
ORDER BY Hess_main_participant_table.Last;

Right now the [Forms]![ExperimentFilter]![Expfilter] is giving me nothing
when I put it into the criterion and run the query even though I've triple
checked the spelling, etc.

I am a novice access user (3 months) and I've messed with the VB code a
little to make the msgboxs work and clean up some things but I was hoping I
could accomplish the filtering using queries and some kind of IIF and where
condition to take out all the PartID.

Thank you in advance for looking
 
I'll quickly summarize the main point since I may have been too verbose:

I wish to be able to select a name of a study and find all the PartID #s
associated with that StudyID and then filter out all the PartID #s that are
returned.
A 1 to many filter that is based on a study name filter.

Even a basic syntax or code helping would be VERY grately appreciated.
 
Back
Top