I am designing a document management system. Tables of concern are:

1. tblDocument... DocID, DocName, ActiveStatus
2. tblRevision... DocID, RevID, RevLevel, RevDate, LinkToDoc
3. tblApproval... RevID, EmpID, ApproveStatus, ApproveDate

tblDocument relates to tblRevision with DocID (1:M)
tblRevision relates to tblApproval with RevID (1:M)
EmpID in tbl Approval is a foreign key relating to tblEmployee

So basically, tblDocument contains records for all documents (documents can
be inactivated by the ActiveStatus toggle). Each document can have several
revisions in tblRevision. Each revision can have one or more approvals
required for the revision to be implemented.

Here's the problem...I am trying to write a query for my rptMaster, the
master documents list. I only want those lines printed for:
1. active documents
2. documents with revisions that have all necessary approvals.

I tried writing a query that used totals and then took the maximum
RevisionLevel and where the average of ApproveStatus = True. But this doesn't
work if there is a revision pending. The Select statement finds the latest
revision, but the approvals aren't all true, so it doesn't print a record.

I know I haven't explained this too well. Is there anyone who would care to
tackle this?