Count number of records in a Category

[TonyCronin]

My database has three tables - Category, SubCategory, Details. On my site - the first page shows all categories (I am using Select * from Category to create list of categories). When a category is clicked the next page shows all subcategories (Select * Subcategory where Category matches the one passed)

What I would like it the first page to show the number of subcategories that exist for a category on the first page. i.e.

Category 1 (2)
Category 2 (5)

etc. How would I do this?

Any help appreciated.

 

[shaddow]

You could use something like this

Select Category, count(*) from Category inner join subCategory on Category.id=subCategory.catid group by Category


________
George, M

 

[TonyCronin]

Almost there I think..

This is what I have...

SELECT Category.Category, COUNT (Category.Category)
FROM Category, Subcategory
WHERE Subcategory.Category = Category.Category
Group BY Category.Category

I get the short list of categories - it excludes those with no entries. I think this may be because WHERE is an inner join? and I need an outer join? Is this right? How can I do an outer join in access?

 

[Tarwn]

Your count is counting from the wrong table...

[sub]01000111 01101111 01110100 00100000 01000011 01101111 01100110 01100110 01100101 01100101 00111111[/sub]
The never-completed website:

http://www.tiernok.com/

 

[TonyCronin]

Changed first line to

SELECT Category.Category, COUNT (SubCategory.Category)

No difference in results... is this what you meant?

 

[shaddow]

You could use Rs.Filter to get the corespondent count.

sql="SELECT Category.Category from Category"
rscat.Open sql,conn,3,3

sql="SELECT Subcategory.Category from Subcategory"
rssub.Open sql,conn,3,3
while not rscat.Eof
 'filter the second rs with the category name and get it's record count
 rssub.Filter="Category='"&rscat("Category")&"'"
 rssub.requery
 count=rssub.RecordCount
 rscat.MoveNext
wend

________
George, M