Using a query in the criteria of another query

[shiphtfour]

Hello, I am sure this is fairly simple to do, but I cannot seem to find the right way to do it. I would like to take the results from one query and use them to filter out unwanted results in a second query. So the results of the second query will not equal any of the results from the first query. Thanks for the help.

[maximus]

This is Possible here is an example:

I have two tables tblProduct and tbloutDetails. In this example I want to find the name of the product issued for out_Id = 1. I Use the Select Query in the Criteria To Select the product_ifd from the tanle tbloutdDetails fror out_id=1 and then this product ID is used as a criteria to find the productname.


SELECT tblProduct.product_name
FROM tblProduct
WHERE (((tblProduct.product_Id)=(Select product_id from tbloutDetails where out_Id=1)));

Please replace the names of Tables and Fields with the the names of Tables and Fields in your Database.

Pls. Mark the thread solved if this solves your problem.

[shiphtfour]

Thanks for your help maximus. What I am trying to accomplish is slightly different, but hopefully can still be achieved. I have a table containing a PrimaryID and a SecondaryID followed by their RelationshipID. What I want is a query that will give me all instances of the SecondaryID that: 1. Do not also appear in the PrimaryID column, and 2. Do not appear in the SecondaryID column with a RelationshipID = 1. How might I go about doing this?

[maximus]

shiphtfour I need more details to help you out here. May be u can upload a sample and tell me exactly what u want to do. I will be more than happy to help you out. pls upload your sample in MS Access 2000

[shiphtfour]

I have attached a txt file of what my table looks like. It shows the relationships of 7 mice. A relationshipID = 1 means they are mated. A relationshipID = 2 means the secondary mouse (SMouseID) is the offspring of the primary mouse (PMouseID). The data shows that mouse 1 is mated with mouse 2. Mouse 3 is the offspring of mouse 1 and 2. Mouse 4 is mated with mouse 5. Mouse 6 is the offspring of mouse 4 and 5. Mouse 6 is mated with mouse 7. I want a query that returns all offspring that are not mated. In this case, mouse 3. If I just run a query for SMouseID with RelationshipID = 1 filtered out, mouse 6 will also be returned. So, my query would have to find all instances of SMouseID that 1) don't also appear in the PMouseID field and 2. Don't appear in SMouseID with a RelationshipID = 1. Thanks again for your help.

[shiphtfour]

Here is the table in access 2000 format. I hope you are still able to help, thanks.