Show all records that have more than one matching in a related table (1 Viewer)

[RCurtin]

Hi,
I've been trying to get this for ages now - both in the design view and in sql:

I have 2 tables - one called DrawingsRegister and a related one called DrawingRevisions. Each drawing has one or more drawing revisions. I want a query that will show each drawing (just once) that has more than one revision:

SELECT tblDrawingsRegister.DrawingNum, tblDrawingsRegister.DrawingName, tblDrawingRevisions.DrawingNum

FROM tblDrawingsRegister INNER JOIN tblDrawingRevisions ON tblDrawingsRegister.DrawingNum = tblDrawingRevisions.DrawingNum

WHERE ((Count([tblDrawingRevisions]![DrawingNum])>"0"));

Thanks for your time,
RCurtin.

 

[cheuschober]

You can do this the easy way or the hard(er)way. For now I recommend the easy way which is assuming that .DrawingNum is your unique identifier. Take your initial table and just make a normal query where your two fields are .DrawingNum and CountOf(DrawingNum) with the where clause of 'CountOf' as being >1.

You'll get a whole list of .DrawingNum's that meet that criterion.

Save the query and make a second query pulling in the first query and your initial table. Join the .DrawingNum's of the two and there ya go.

The hard(er) was is to do the same as subquery with the IN (SELECT...) in the Where clause of .DrawingNum.