Document control database controls documents by doc name,Doc number, rev level, date, etc. Need suggestions on pulling in a listing of just the most current documents (latest revision) into a form. I have a variety of different documents that span from original to Z. Help!
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
List of most current document based on revision level 1
- Thread starter ekrobi
- Start date
-
1
- #2
Hi ekrobi,
I have a document control database that is actually ISO certified and in order to accomplish this there are a few tables needed to properly control revisions. I won't guess at your structure but I'll give you a little idea from mine.
In a main Quality Assurance Procedure (QAP) section, there exists a table of documents id-ed and a description, a few other fields - date created, approved by...that kind of stuff.
*Titles for the documents live seperately as they are repeated in many sub sections.
As a "sub table" from the DocID table, I stack the revisions: the docID, a revisionID (from a revision number table). One Doc, many revisions. Dates, approval etc...
The QAP document can go five levels deep with sections, their sub sections, their sub.... but I think thats getting off track a spec.
The "main form" (unbound) will fire up and in its on load event select the last revision via a SQL string:
strNewRecord = "SELECT tblqap1.DocID, Last(tblqap1.Revision) AS Revision, Last(tblqap1.Issued) AS Issued, tblqap1.PrepBy, tblqap1.RevBy, tblqap1.Approved, tblqapdoctitle.Title FROM tblqapdoctitle INNER JOIN tblqap1 ON tblqapdoctitle.DocID = tblqap1.DocID GROUP BY tblqap1.DocID, tblqap1.PrepBy, tblqap1.RevBy, tblqap1.Approved, tblqapdoctitle.Title HAVING ((Not (tblqap1.RevBy) Is Null) AND (Not (tblqap1.Approved) Is Null))"
and apply it as the record source for the form.
Plain English: "get the DocumentID, the Last revision for it from the Doc-Revision table, but it must be reviewed and approved.
If you don't have your doc table split up sort of as described, you may have some difficulties in pulling this off without errors. You may call your last revision by a date or a number or a letter. As in my case there may be three revisions stacked on top waiting to go, but they don't make the cut because the QA manager has not approved them or reviewed them. Sure there's more than one way, but I know this structure and method works. If you could ellaborate on your table structure we can probably go even further...
Hope this guides you or at least gives you an idea or two!
Gord
ghubbell@total.net
I have a document control database that is actually ISO certified and in order to accomplish this there are a few tables needed to properly control revisions. I won't guess at your structure but I'll give you a little idea from mine.
In a main Quality Assurance Procedure (QAP) section, there exists a table of documents id-ed and a description, a few other fields - date created, approved by...that kind of stuff.
*Titles for the documents live seperately as they are repeated in many sub sections.
As a "sub table" from the DocID table, I stack the revisions: the docID, a revisionID (from a revision number table). One Doc, many revisions. Dates, approval etc...
The QAP document can go five levels deep with sections, their sub sections, their sub.... but I think thats getting off track a spec.
The "main form" (unbound) will fire up and in its on load event select the last revision via a SQL string:
strNewRecord = "SELECT tblqap1.DocID, Last(tblqap1.Revision) AS Revision, Last(tblqap1.Issued) AS Issued, tblqap1.PrepBy, tblqap1.RevBy, tblqap1.Approved, tblqapdoctitle.Title FROM tblqapdoctitle INNER JOIN tblqap1 ON tblqapdoctitle.DocID = tblqap1.DocID GROUP BY tblqap1.DocID, tblqap1.PrepBy, tblqap1.RevBy, tblqap1.Approved, tblqapdoctitle.Title HAVING ((Not (tblqap1.RevBy) Is Null) AND (Not (tblqap1.Approved) Is Null))"
and apply it as the record source for the form.
Plain English: "get the DocumentID, the Last revision for it from the Doc-Revision table, but it must be reviewed and approved.
If you don't have your doc table split up sort of as described, you may have some difficulties in pulling this off without errors. You may call your last revision by a date or a number or a letter. As in my case there may be three revisions stacked on top waiting to go, but they don't make the cut because the QA manager has not approved them or reviewed them. Sure there's more than one way, but I know this structure and method works. If you could ellaborate on your table structure we can probably go even further...
Hope this guides you or at least gives you an idea or two!
Gordghubbell@total.net
- Thread starter
- #3
Yes, this is a Quality document control system (ISO-based)with all the elements you described and more. Going to do some restructuring based on your input. Would like to talk to you more on your approval tie-in with the revision control and how you're handling distribution. What a relief to find someone who understands--thanks!
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question
- Locked
- Question