query extract

[Mike]

hello
i have a large database of jobs. each job has between 2
and 6 rows of data which contain different products. i am
trying to write a query which will pull out all jobs which
contain a certain product. the product would be one of the
rows in any particular job. however i don't just want the
single rows extracted that contain the product, i want all
the rows for all jobs that contain that product. each job
has a number and the job number is shown in each row. is
there a way i can easily do this - i just end up with the
individual rows that contain the product and not all the
rows for those jobs.
thanks for your help in this

 

[Van T. Dinh]

Not sure from your description but it sounds like the job
for a Query with a Sub-Query. Try something like:

SELECT Main.*
FROM YourTable As Main
INNER JOIN
(
SELECT DISTINCT Sub.JobID
FROM YourTable As Sub
WHERE Sub.ProductID = {whatever}
) As SubQuery
ON Main.JobID = SubQuery.JobID

You can also use the IN Operator but I think Inner Join is
more efficient.

HTH
Van T. Dinh
MVP (Access)