Multiple Matching Records in Related Table

[bymarce]

I have two tables with a many to many relationship joined through a junction
table. The first table contains data from scientific experiments. The
primary key is DataID. The second table contains the conditions under which
the experiments were run and is called Conditions. I want to make a query
that will return records in the Data table that were run under the same
conditions. If experiments were run at a temperature = 100K and a pressure =
1ATM they would have two records in the conditions table. I can see how to
return all the conditions records that match either criteria. How do I get
it to return the DataID from the junction tables that match both criteria.
Marcie

 

[KARL DEWEY]

This should do it ---
SELECT DataID
FROM [JunctionTable] INNER JOIN [Conditions] ON [JunctionTable].[ID] =
[Conditions].[ID]
WHERE ([Conditions].[Temperature] = "100K") AND ([Conditions].[Pressure] =
"1ATM");

Assuming your [JunctionTable] has DataID and ID fields.

 

[bymarce]

Thanks for the info but I think my question wasn't clear. Sorry about that.
Here's more info. Temperature and Pressure are not fields in the conditions
table. They are values in the field "ConditionName". The fields in the
conditions table are "ID", "ConditionName", "Value", and "Units". My form as
three sets of unbound controls where the user can select up to 3 sets of
condition, value, and units. I'm thinking I need to do a query that returns
the "DataID" from the junction table where there are 2 or 3 records returned
or do a query of a query. Thanks for your help.

This should do it ---
SELECT DataID
FROM [JunctionTable] INNER JOIN [Conditions] ON [JunctionTable].[ID] =
[Conditions].[ID]
WHERE ([Conditions].[Temperature] = "100K") AND ([Conditions].[Pressure] =
"1ATM");

Assuming your [JunctionTable] has DataID and ID fields.

--
Build a little, test a little.

I have two tables with a many to many relationship joined through a junction
table. The first table contains data from scientific experiments. The
primary key is DataID. The second table contains the conditions under which
the experiments were run and is called Conditions. I want to make a query
that will return records in the Data table that were run under the same
conditions. If experiments were run at a temperature = 100K and a pressure =
1ATM they would have two records in the conditions table. I can see how to
return all the conditions records that match either criteria. How do I get
it to return the DataID from the junction tables that match both criteria.
Marcie

 

[KARL DEWEY]

If you want more suggestions then post sample data from the tables.
--
Build a little, test a little.

Thanks for the info but I think my question wasn't clear. Sorry about that.
Here's more info. Temperature and Pressure are not fields in the conditions
table. They are values in the field "ConditionName". The fields in the
conditions table are "ID", "ConditionName", "Value", and "Units". My form as
three sets of unbound controls where the user can select up to 3 sets of
condition, value, and units. I'm thinking I need to do a query that returns
the "DataID" from the junction table where there are 2 or 3 records returned
or do a query of a query. Thanks for your help.

This should do it ---
SELECT DataID
FROM [JunctionTable] INNER JOIN [Conditions] ON [JunctionTable].[ID] =
[Conditions].[ID]
WHERE ([Conditions].[Temperature] = "100K") AND ([Conditions].[Pressure] =
"1ATM");

Assuming your [JunctionTable] has DataID and ID fields.

--
Build a little, test a little.

I have two tables with a many to many relationship joined through a junction
table. The first table contains data from scientific experiments. The
primary key is DataID. The second table contains the conditions under which
the experiments were run and is called Conditions. I want to make a query
that will return records in the Data table that were run under the same
conditions. If experiments were run at a temperature = 100K and a pressure =
1ATM they would have two records in the conditions table. I can see how to
return all the conditions records that match either criteria. How do I get
it to return the DataID from the junction tables that match both criteria.
Marcie

 

[bymarce]

Thanks for you help. I got it worked out. I made queries to find matching
dataIDs for each condition criteria and then made another query in which the
first 2 or 3 queries are joined that shows only matching records.
Marcie

If you want more suggestions then post sample data from the tables.
--
Build a little, test a little.

Thanks for the info but I think my question wasn't clear. Sorry about that.
Here's more info. Temperature and Pressure are not fields in the conditions
table. They are values in the field "ConditionName". The fields in the
conditions table are "ID", "ConditionName", "Value", and "Units". My form as
three sets of unbound controls where the user can select up to 3 sets of
condition, value, and units. I'm thinking I need to do a query that returns
the "DataID" from the junction table where there are 2 or 3 records returned
or do a query of a query. Thanks for your help.

This should do it ---
SELECT DataID
FROM [JunctionTable] INNER JOIN [Conditions] ON [JunctionTable].[ID] =
[Conditions].[ID]
WHERE ([Conditions].[Temperature] = "100K") AND ([Conditions].[Pressure] =
"1ATM");

Assuming your [JunctionTable] has DataID and ID fields.

--
Build a little, test a little.


:

I have two tables with a many to many relationship joined through a junction
table. The first table contains data from scientific experiments. The
primary key is DataID. The second table contains the conditions under which
the experiments were run and is called Conditions. I want to make a query
that will return records in the Data table that were run under the same
conditions. If experiments were run at a temperature = 100K and a pressure =
1ATM they would have two records in the conditions table. I can see how to
return all the conditions records that match either criteria. How do I get
it to return the DataID from the junction tables that match both criteria.
Marcie