Need join with count of matching records in 2nd table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
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.
 
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
 
Back
Top