Counting parent but not child records in query

[officeguru]

Greetings,
I have two tables that track publications submitted for proofing. Table 1[BOM Review] holds the name of the writer[Leader], the ID of the publication [Pub #], the date it passes review and a checkbox [Status] indicating it has passed. Table 2[Rejections] is related on publication ID[Pub no] and holds the date and reason for any rejections.

I wrote a query to total all documents for each writer that have passed review. Then total all documents that were never rejected. Dividing the latter by the former should give a percentage of documents that passed without rejection.

The problem I am having is that when a publication is rejected more than once, it is counted as more than one completed publication. I thought I could fix this by using the SELECT DISTINCT or SELECT DISTINCTROW but it doesn't work. Can someone see the error in my thinking?

Thanks
Brian


SELECT DISTINCTROW [BOM Review].Leader, Sum(IIf([BOM Review].[status]=True,1,0)) AS Pubs, Sum(IIf([rejections].[id] Is Null,1,0)) AS Clean, [Clean]/[Pubs] AS [Pass Rate]
FROM [BOM Review] LEFT JOIN Rejections ON [BOM Review].[Pub #] = Rejections.[Pub no]
WHERE ((([BOM Review].Date) Between [forms]![Report Date Range]![beginDate] And [forms]![Report Date Range]![endDate]))
GROUP BY [BOM Review].Leader;

 

[GoDawgs]

Can you post some example data and what you expect to get?

 

[officeguru]

OK, I created a dummy database to use as an example

Table 1
Task ID WriterID Task Description End Date Passed
5 Dick Dick 1 2/25/2004 TRUE
6 Dick Dick 2 3/4/2004 TRUE
7 Harry Harry 1 3/4/2004 TRUE
9 Harry Harry 2 2/28/2004 TRUE
1 Tom Title 1 3/2/2004 TRUE
2 Tom Title 2 2/15/2004 TRUE
3 Tom Title 3 2/29/2004 TRUE
4 Tom Title 4 2/19/2004 TRUE
8 Tom Title 5 2/19/2004 TRUE


PubID TaskID Reason RejectionDate
1 1 Bad Grammar 3/1/2004
2 1 Punctuation 3/2/2004
3 2 Errors 2/1/2004
4 6 Problems 1/1/2004
5 9 Poor work 1/30/2004

Query Results
WriterID Pubs Clean Pass Rate
Dick 2 1 0.5
Harry 2 1 0.5
Tom 6 3 0.5

SELECT DISTINCTROW Table1.WriterID, Sum(IIf([Table1].[passed]=True,1,0)) AS Pubs, Sum(IIf([table2].[TaskId] Is Null,1,0)) AS Clean, [Clean]/[Pubs] AS [Pass Rate]
FROM Table1 LEFT JOIN Table2 ON Table1.TaskID = Table2.TaskID
GROUP BY Table1.WriterID;


The Problem:

Tom has completed 5 publications. One pub was rejected twice. The query shows that he has 6 publications. If you did a simple join query there would be six records with Tom as WriterID, because there are two child records for Tom Title 1.

I don't want to count the records in the joined databases, I want to count the records in the parent table. In my example all of the documents were PASSED=TRUE, in practice most are not. I need to filter out the PASSED = FALSE records. Then of the PASSED=TRUE, I want to count those with and without rejections.

 

[Quehay]

To get total of passes and count of docs that didn't get rejected (this is a rough sketch--post again if this doesn't make sense to you):

select
writer,
Sum(IIf([BOM Review].[status]=True,1,0)) as Passed,
Count(docid) as NotRejected
from BOM review
where docid not in (select docid from rejection table)
group by writer



Turn your headache into my project!
Jeffrey R. Roberts
Insight Data Consulting
Access and SQL Server Development

http://www.InsightDataConsulting.com

 

[officeguru]

Thanks Jeffrey, I'll try that when I get back in--it does make sense though.

Brian

 

[officeguru]

Thanks for your help, but I am still struggling.

What I am trying to do is count all completed documents ('Passed') on a pass/fail basis. If there is one or 1000 related records in the rejections table, it is counted as a failure, if there are 0 related records it is a success.

Using Quehay's logic counts successes twice, once as a null value from rejections, and again as NOT IN(Rejections)

Looking at it from another perspective, it would be like a having a list of customers and related orders. I am trying to count all the customers, and all the customers with orders to get a percent of customers with orders. It shouldn't matter if they have one order or a dozen, the customer only gets counted once.

It is a little more complicated because I have additional conditions, but whatever logic would work in that example should work for my problem

Thanks again,
Brian

 

[GoDawgs]

How about using a DCount to find the number of rejections? If you do that I would suggest calculating the pass rate in a report or form instead of in the query...otherwise the DCount will be used twice and will slow things down.

Kevin

 

[Quehay]

Brian: I misread your original requirements--so you need a count of documents passed by author compared with documents that have passed without ever being rejected (I previously thought the rejection was absolute rather than a reject and resubmit until passed scenario).

Your requirement: I wrote a query to total all documents for each writer that have passed review. Then total all documents that were never rejected. Dividing the latter by the former should give a percentage of documents that passed without rejection

To get the count of docs passed by writer:
[tt]
query "countdocspassedbywriter"
select
docs.writerid,
count(docs.docid) AS CountDocsPassed
from docs
where docs.passed=True
group by docs.writerid;[/tt]

To get a total of docs passed and never rejected for a writer get a count of documents grouped by writer where the docid does not appear in the rejects table:
[tt]
query "countdocspassedneverrejectedbywriter"
select
docs.writerid,
count(docs.docid) as CountNeverRejected
from docs
where docs.docid not in (select docid from rejects)
group by docs.writerid;[/tt]

To put these together (ugly SQL) You need the outer join to be sure of getting writers who passed docs but never without a rejection.
:
[tt]
SELECT
countdocspassedbywriter.writerid,
countdocspassedbywriter.CountDocsPassed,
countdocspassedneverrejectedbywriter.CountNeverRejected,
[CountNeverRejected]/[CountDocsPassed] AS ratio_never_rejected_to_total_passed
FROM countdocspassedbywriter
LEFT JOIN countdocspassedneverrejectedbywriter
ON countdocspassedbywriter.writerid = countdocspassedneverrejectedbywriter.writerid;
[/tt]

You could put all this in one query but Access doesn't seem to allow SQL 92 JOIN syntax with
virtual tables and there's no WHERE clause OUTER JOIN syntax like the Oracle table1.field + = table2.field.

[tt]
SELECT
sub1.writerid,
sub1.docs_passed,
sub2.docs_never_rejected, sub2.docs_never_rejected/sub1.docs_passed AS ratio_never_rejected_to_passed
FROM
(select docs.writerid, count( docs.docid) as docs_passed from docs where docs.passed=true group by docs.writerid) sub1,
(select docs.writerid, count( docs.docid) as docs_never_rejected from docs where docs.passed=true and docs.docid not in (select docid from rejects) group by docs.writerid) sub2
WHERE
sub1.writerid=sub2.writerid[/tt]

Turn your headache into my project!
Jeffrey R. Roberts
Insight Data Consulting
Access and SQL Server Development

http://www.InsightDataConsulting.com

 

[Quehay]

The problem with your original approach is that you have to have the DOCUMENTS.DOCID (must be preserved/LEFT table docid) as a group by column to provide a meaningful distinct row (otherwise you've likely got a large number of writerid, sum(iif(passed)),sum(iif(rejected)) duplicate rows), but then you've lost the by writer/leader summary (although you could use this as a subquery without the aggregate functions and then group by writer/leader and sum in a subsequent query).
[tt]
SELECT DISTINCT docs.writerid, docs.docid, IIf([passed],1,0) AS DocPassed, IIf([rejects].[docid],1,0) AS RejectedAtLeastOnce
FROM docs LEFT JOIN rejects ON docs.docid = rejects.docid
ORDER BY docs.writerid, docs.docid;
[/tt]
Gives (which if then queried with aggregate sums and group by writerid gives accurate results):
[tt]
writerid docs.docid rejects.docid DocPassed RejectedAtLeastOnce
1 1 1 0
1 2 2 0 1
1 3 1 0
2 4 4 1 1
2 5 5 1 1
2 6 6 1 1
3 7 7 1 1
3 8 1 0
3 9 1 0
3 10 1 0
3 11 0 0
[/tt]

Turn your headache into my project!
Jeffrey R. Roberts
Insight Data Consulting
Access and SQL Server Development

http://www.InsightDataConsulting.com

 

[Quehay]

**LEFT JOIN is needed to see those writers who were never rejected (written in the converse above)

Turn your headache into my project!
Jeffrey R. Roberts
Insight Data Consulting
Access and SQL Server Development

http://www.InsightDataConsulting.com

 

[officeguru]

Thanks,
I need to play around with these examples, but I think you've got me thinking correctly.

This forum is teaching me a lot about Access AND about phrasing my questions. When you look at it every day it's easy to forget that 'passed' doesn't mean passed and 'rejected' doesn't always mean rejected.