Need join with count of matching records in 2nd table

M

mscertified

I need to join 2 related tables, I need all columns in the first table and a
count of matching records in the 2nd table. So far, I cant figure out how to
do this. Any ideas?
 
K

KARL DEWEY

Try this --
SELECT [Table 1].[Field1], [Table 1].Field2, [Table 1].[Field3],
Count([Table 2].[Field3]) AS [How Many]
FROM [Table 1] LEFT JOIN [Table 2] ON [Table 1].[Field3] = [Table 2].[Field3]
GROUP BY [Table 1].[Field1], [Table 1].Field2, [Table 1].[Field3];

I only put three fields from Table 1 but you can put all. Field3 is the
common field for count of matching records.
 
J

Jeff Boyce

It sounds like you are saying that any records in table2 that match
(according to your join fields) records in table1 get counted. If so, I'm
guessing a Totals query would do that.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Top