Best way to filter a form by multiple query results.

[MilaK]

Hello,

A continuous form “frm_Projects”has a list of distinct projects and attributes (created date, number of samples, sign off dates). The query behind ”frm_Project” form uses tbl_Project and tbl_Samples and Group by to count the number of samples in each project, thus I don’t want to add “sample_name” field to the query nor bound the recordsource directly to "tbl_Projects".

Tbl_Projects is joined to tbl_Samples (one-to-many). Each project has many samples, it is possible however, that the same sample was used on two different projects.

The user needs to search for a project by typing in the sample name in a text field box on “frmProjects”. A query will return the project name(s) that will be used to filter the form to show all projects that contain that sample.

I'm thinking about using an additional query(shown below) to filter the form. If there are mutiple resutls (several projects that contain the same sample)how do I then use multiple results to filter the form?

SELECT tbl_Projects.run_name
FROM tbl_Projects INNER JOIN tbl_Samples ON tbl_Projects.run_name = tbl_Samples.run_name
WHERE (((tbl_Samples.sample_name)=[Forms]![frm_Projects]![txt_sample_name]));

Thank you

 

[sneuberg]

Tbl_Projects is joined to tbl_Samples (one-to-many). Each project has many samples, it is possible however, that the same sample was used on two different projects.

These sentences are contradict each other. If the relationship is set up correctly as a one to many then the same sample couldn't be used on a different project. Consider Project A and B with Samples 1 and 2 and this data in the Sample table.

Sample (PK)    Project (FK)
  1                    A
  2                    A
  1                    B

This third entry would cause a primary key (PK) violation in the samples table.

Is is possible that you mean the two project could have the same types of samples?

Could you post a screen shot of your relationships?

 

[MilaK]

Hi Steve,

sample_id is unique but the sample name can repeat. So yes, in way sample type can be the same.

please see the attached. Thanks, Mila

 

[sneuberg]

Back to your original question you could try tacking this query onto the record source query as a subquery. In the WHERE clause of the record source query you would add something like

WHERE  tbl_Projects.run_name IN (SELECT tbl_Projects.run_name
FROM tbl_Projects INNER JOIN tbl_Samples ON tbl_Projects.run_name = tbl_Samples.run_name
WHERE (((tbl_Samples.sample_name)=[Forms]![frm_Projects]![txt_sample_name])));