need query

  • Thread starter Thread starter omko h
  • Start date Start date
O

omko h

I have a table with the following colums (in short):
ID, Name, Parent
the colums 'parent' revers to the colums 'ID'.

I want:
The names of all the items in the database with a colum with the number of
items that have this item as their parent.


anyone got a idea?
Omko Huizenga
 
Hi,
I read your question to mean "count all the unique
occurences of ID"
If this is true, build a Totals query using the ID
field twice. Use the Group By function for the first
instance; use the Count function for the second instance.
You can sort on either instance to get either a sorted ID
list or a list sorted by Count.

Hope this helps.
 
Something like the following should work:-

SELECT Name, NumParents
FROM tblParents LEFT JOIN (SELECT Parent, Count(ID) AS
NumParents FROM tblParents GROUP BY Parent) as qry
ON tblParents.ID=qry.Parent;

change tblParents to your tablename of course.

hth

Chris
 
it works!!!
you rock! I really don't get how it works, but that is fine with me :)
Omko Huizenga
 
Back
Top