Problem retrieving counts

[collizz]

I'm writing a simple report that needs to show individuals and the number of times that each individual has been designated the author and/or owner of a document.

The two tables in the query (simplified) are: Person, with columns personID (PK) and personName; Document with columns docID (PK), authorID and ownerID.

Each report line needs to show one line per person, with the ID, name, count of authorID and count of ownerID (showing the number of times he/she was designated the author and/or owner of one or more documents).

For example:
ID ... Name ..........Author ... Owner
1 .... John Smith .... 0 ......... 3
2 .... Mary Smith .... 0 ......... 0
3 .... Peter Smith ... 1 ......... 2

I need to create a query to retrieve one row per person, then count the number of matches for Person.personID against Document.authorID and Document.ownerID.

Pseudo code might be:
SELECT p.personID, p.personName, COUNT(d.authorID) FROM Person p, Document d WHERE d.authorID = p.authorID, COUNT(d.ownerID) FROM Person p, Document d WHERE d.ownerID = p.ownerID ?

Thank you.

 

[Dennisk]

you need a group (Total) Type Query. Where you would GROUP BY on PersonID and COUNT AuthorID.

 

[collizz]

Sample db and report to explain

There is only one record per personID, so not sure what GROUP BY would give me? Honestly, I do appreciate the help, and would really appreciate an assist on the syntax for the query. I know what I want, just can't figure out how to put it into SQL.
Person table
personID.........personName
1...................James
2...................John
3...................Mary
4...................Peter

Document table
docID.........authorID.........ownerID
0001.............2......................2
0002.............2......................1
0003.............1......................4
0004.............2......................2

Report
ID..........Name...................Author..........Owner
1...........James......................1.................1
2...........John........................3.................2
3...........Mary........................0.................0
4...........Peter.......................0.................1

All donations welcome!

 

[Rich]

What's the link between the persons table and the documents table?

 

[Dennisk]

I think a person can be an Author and/or an owner

so you would GROUP BY on AuthorID and COUNT(AuthorID)

then (and I may be wrong here) IN A seperate query

GROUP BY on OwnerID and COUNT(OwnerrID)

use a third query with the Persion Table linked to the AuthorID and linked to the OwnerID from the presious two queries.

 

[collizz]

The value for Person.personID may be present in Document.authorID and/or Document.ownerID.
If this is a compound query, I'd be interested to know how you put that together, guys.