query extract

  • Thread starter Thread starter Mike
  • Start date Start date
M

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
 
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)
 
Back
Top