Help with Master Documents query!

G

Guest

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?
 
G

Gary Walter

It sounds like you want to

group by DocID (maybe other fields?)

where ActiveStatus = -1

HAVING Max(ApproveStatus) = -1

i.e., within a group, if *any* revision
was not approved, then max would
be 0 (false)

if *all* in group were approved, then
max would be -1
 
Top