Mike, What you have to remember with your query is that the COUNT is just a count of rows...and that is done AFTER the data has been joined together. If you look at how your joins are constructed, you will see that your counts are going to be the product of the two joins...
You can strip all of the extraneous stuff out of your query and have... SELECT media.media_id, COUNT(media_image.media_image_id) AS image_count, COUNT(media_file.media_file_id) AS file_count FROM (media LEFT OUTER JOIN media_image ON media.media_id = media_image.media_id) LEFT OUTER JOIN media_file ON media.media_id = media_file.media_id GROUP BY media.media_id And let's say the data you have is as follows... *media* media_id 1 *media_file* media_id media_fileid 1 10 1 20 *media_image* media_id media_image_id 1 70 1 80 1 90 If you run the above query, first it is going to join media to media_image...and you have the results... 1 10 1 20 Then it is going to join media_file with the results you returned from the first join.... 1 10 70 1 10 80 1 10 90 1 20 70 1 20 80 1 20 90 As you can see, every row of that third table is joined to anything that matches in the first join, resulting in quite a few more rows than you intended. So when you wrap your COUNT() around that column, it just counts up the rows and spits out a number you don't want.... 1 6 6 There are DBs that support a COUNT(DISTINCT column), but I don't believe Access is one of them. Your best bet is probably to do this through a subquery in your SELECT statement... In the following example, I took out the second join, and replaced it with a subquery to find the file_count...you could replace both the joins with subqueries if you want. SELECT media.media_id, COUNT(media_image.media_image_id) AS image_count, (SELECT COUNT(media_file_id) FROM media_file WHERE media_id = media.media_id) AS file_count FROM (media LEFT OUTER JOIN media_image ON media.media_id = media_image.media_id) GROUP BY media.media_id -jim ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Create robust enterprise, web RIAs. Upgrade & integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:263681 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
