Query Help?

[Julie Smith]

Hi,
I want to make a query but I am unsure how to go about it. Here is what I
have:
Jobs({pk}jobID, drawingID)
Sample Data:
J1, Dwg1
J2, Dwg2
J3, Dwg1
J4, Dwg3
J5, Dwg2
J6, Dwg4

Now, I'd like to make a query that only shows the job numbers that have more
than two counts of the same drawing ID. For example, from the above data I
should end up with:
J1, Dwg1
J3, Dwg1
J2, Dwg2
J5, Dwg2

I hope someone can help me.

Thank you in advance.

 

[John W. Vinson]

Hi,
I want to make a query but I am unsure how to go about it. Here is what I
have:
Jobs({pk}jobID, drawingID)
Sample Data:
J1, Dwg1
J2, Dwg2
J3, Dwg1
J4, Dwg3
J5, Dwg2
J6, Dwg4

Now, I'd like to make a query that only shows the job numbers that have more
than two counts of the same drawing ID. For example, from the above data I
should end up with:
J1, Dwg1
J3, Dwg1
J2, Dwg2
J5, Dwg2

I hope someone can help me.

Thank you in advance.

Make a Totals query. Create a query based on your table; select pkJobID and
drawingID, and make it a Totals query by clicking the Greek Sigma icon.

Group By DrawingID and select Count on pkJobID. Put a criterion under that
field of >1. The SQL would be:

SELECT Count([pkJobID]), [drawingID] FROM tablename
HAVING Count([pkJobID]) > 1;

Save this query as qryCountJobs.

THen create a new query joining qryCountJobs to your table by drawingID to
pick up the actual job ID's.

John W. Vinson [MVP]

 

[Jerry Whittle]

This is a job for SubQuery Woman!

Open up a new query with the Jobs table as its source. Go to View, SQL View
and past in this:

SELECT Jobs.jobID, Jobs.drawingID
FROM Jobs
WHERE Jobs.drawingID IN
(SELECT Jobs.drawingID
FROM Jobs
GROUP BY Jobs.drawingID
HAVING Count(Jobs.jobID)>1)
ORDER BY 2,1;

 

[KARL DEWEY]

Now, I'd like to make a query that only shows the job numbers that have more
than two counts of the same drawing ID.
Your words do not match the data or am I misunderstanding your words?
J1 only has Dwg1 once.