Hi, I have a Access database with two tables; a category table and an images
table. In the Category table I have a field for Index number and a field for
Categories. The images table has a field for all the images and a field to
specify what category the images are assigned to.

I am trying to write a simple Select query that will return a list of
Categories from my Category table, and in the second colum a count of the
number of images assigned to each category from my main table of images.

Can anybody suggest how I could do this?

Thanks for any help you can give me,

Mark.

[ Post a follow-up to this message ]

SELECT Categories.Name,
COUNT(Images.CategoryID)
FROM Categories INNER JOIN Images
ON Images.CategoryID = Categories.CategoryID
GROUP BY Categories.Name

--
http://www.aspfaq.com/
(Reverse address to reply.)




"Mark" <m@notmyemailaddress.com> wrote in message
news:RmlEc.850$Fc7.199326@stones.force9.net...
> Hi, I have a Access database with two tables; a category table and an
images
> table. In the Category table I have a field for Index number and a field
for
> Categories. The images table has a field for all the images and a field to
> specify what category the images are assigned to.
>
> I am trying to write a simple Select query that will return a list of
> Categories from my Category table, and in the second colum a count of the
> number of images assigned to each category from my main table of images.
>
> Can anybody suggest how I could do this?
>
> Thanks for any help you can give me,
>
> Mark.
>
>

[ Post a follow-up to this message ]

Mark wrote:
> Hi, I have a Access database with two tables; a category table and an
> images table. In the Category table I have a field for Index number
> and a field for Categories. The images table has a field for all the
> images and a field to specify what category the images are assigned
> to.
>
> I am trying to write a simple Select query that will return a list of
> Categories from my Category table, and in the second colum a count of
> the number of images assigned to each category from my main table of
> images.
>
> Can anybody suggest how I could do this?
>
> Thanks for any help you can give me,
>
> Mark.

SELECT c.Categories, count(*)
FROM Category c inner join Images i
ON c.Categories = i.Categories
GROUP BY c.Categories

HTH,
Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[ Post a follow-up to this message ]

Thanks Aaron and Bob, its working now!

"Mark" <m@notmyemailaddress.com> wrote in message
news:RmlEc.850$Fc7.199326@stones.force9.net...
> Hi, I have a Access database with two tables; a category table and an
images
> table. In the Category table I have a field for Index number and a field
for
> Categories. The images table has a field for all the images and a field to
> specify what category the images are assigned to.
>
> I am trying to write a simple Select query that will return a list of
> Categories from my Category table, and in the second colum a count of the
> number of images assigned to each category from my main table of images.
>
> Can anybody suggest how I could do this?
>
> Thanks for any help you can give me,
>
> Mark.
>
>

[ Post a follow-up to this message ]

Hi, I have one other quick question: if I wanted to include the ID field for
each category, would it just be a case of changing the SQL from:

SELECT Categories.Name,
COUNT(Images.CategoryID)
FROM Categories INNER JOIN Images
ON Images.CategoryID = Categories.CategoryID
GROUP BY Categories.Name

TO:

SELECT Categories.ID, Categories.Name,
COUNT(Images.CategoryID)
FROM Categories INNER JOIN Images
ON Images.CategoryID = Categories.CategoryID
GROUP BY Categories.Name

So far this has not worked and I get the error:

"You tried to execute a query that does not include the specified expression
'ID' as part of an aggregate function"



"Mark" <m@notmyemailaddress.com> wrote in message
news:RmlEc.850$Fc7.199326@stones.force9.net...
> Hi, I have a Access database with two tables; a category table and an
images
> table. In the Category table I have a field for Index number and a field
for
> Categories. The images table has a field for all the images and a field to
> specify what category the images are assigned to.
>
> I am trying to write a simple Select query that will return a list of
> Categories from my Category table, and in the second colum a count of the
> number of images assigned to each category from my main table of images.
>
> Can anybody suggest how I could do this?
>
> Thanks for any help you can give me,
>
> Mark.
>
>

[ Post a follow-up to this message ]

Sorted it with this:

SELECT tblCategories.ID, tblCategories.CatName,
COUNT(tblImages.CategoryID)
FROM tblCategories INNER JOIN tblImages
ON tblImages.CategoryID = tblCategories.ID
GROUP BY tblCategories.CatName, tblCategories.ID

Thanks.....
"Mark" <m@notmyemailaddress.com> wrote in message
news:RmlEc.850$Fc7.199326@stones.force9.net...
> Hi, I have a Access database with two tables; a category table and an
images
> table. In the Category table I have a field for Index number and a field
for
> Categories. The images table has a field for all the images and a field to
> specify what category the images are assigned to.
>
> I am trying to write a simple Select query that will return a list of
> Categories from my Category table, and in the second colum a count of the
> number of images assigned to each category from my main table of images.
>
> Can anybody suggest how I could do this?
>
> Thanks for any help you can give me,
>
> Mark.
>
>

[ Post a follow-up to this message ]