User Interface, Button to prompt combo box and open forms 2

[Colebean]

I am designing a document control system. This is my first time on a forum but I have run out of options! I am closing thread707-1587863 as requested by MajP and Duane. It was very lengthy and complicated, so I am consolidating it as follows. I have attached the database link, and the tables/relationships/forms info is listed after my problem description. You can refer to thread707-1587863 for my primary key explanation (how my document number is required to function).

I guess where I am having the most trouble is at the user interface part of the database. How do I create a form control that when selected it has a combo box appear with options that have different forms opened depending on the selection? Also, how do you have other columns on the combo box find previous and current records and use a formula to input values into the opened form? Finally, how do you have the previous record moved into an archive table, when a document is changed and the document version changes, so that only the most recent version is accessible?

Further Explanation/Details:

I need to have a form appear upon opening the database that has two functions 1. Start New Process and 2. Modify Existing Process. When the user selects one of these options I need a combo box to pop up with the options for Change Type (Create, Supplement, Pen and Ink, Revise), Document Type, Document Series, and Document Version.
When the user chooses Start New Process, I need different things to happen when each Change Type is selected:
"Create" will find the next record, by the user inputting the Document Type. Then it adds .001 to the previous record's Document Series for its' Document Series value and force v1.0 as its' Document Version, finally it would open the grouped form CREATE SOP.
"Supplement" would find the most recent record, by the user inputting Document Type and Document Version. Then it adds .1 to the previous Document Version, finally it would open up the group form SUPPLEMENT SOP.
"Revise" will find the most recent record, by the user inputting Document Type and Document Series. Then it adds 1 to the previous Document Version and forces the decimal value to be 0, finally it would open up the group form REVISE SOP.

When the user chooses modify existing process, I need the Change Type to pull up the group form associated with each Change Type and by the user inputting Document Type, Document Series, and Document Version to go to the existing record that needs to be modified.

TABLES:
Change Type
Change Type dbText PrimaryKey Indexed

Document Information
Document Type dbByte PrimaryKey Indexed Required
Document Series dbDecimal PrimaryKey Indexed
Document Version dbText PrimaryKey Indexed
Document Title dbText
Document Description dbText
Change Type dbText Indexed
Reason Change dbText
Purchase Order Number dbText
Purchase Order Approval Date dbDate
Estimated Delivery Date dbDate
Actual Delivery Date dbDate
Date Posted to Sharepoint dbDate
Date Submitted to EPA dbDate
Date of EPA Approval dbDate
Link to Document dbMemo
Date Document Provided for Signature dbDate
SOP Lead Name dbText
Date SOP Lead Signed dbDate
QAQC Evaluator dbText
Date QAQC Signed dbDate
Manager Name dbText
Date Manager Signed dbDate
Review Start Date dbDate
Review Complete Date dbDate
Reviewer Name dbText
Date Retired dbDate
Reason Retired dbText
Link to Archived Document dbMemo

Document Type
Document Type Number dbLong PrimaryKey Indexed
Type of SOP dbText

Draft Reviews
Review Number dbByte PrimaryKey Indexed
SOP Lead Name dbText
SOP Lead Date Assigned dbDate
Date SOP Lead Draft Complete dbDate
Field Evaluator dbText
Date Draft Provided for Field Eval dbDate
Date Field Eval Complete dbDate
QAQC Evaluator dbText
Date Draft Provided to QAQC dbDate
Date QAQC Evaluation Complete dbDate
Document Type dbByte PrimaryKey ForiegnKey Indexed Required
Document Series dbDecimal PrimaryKey ForiegnKey Indexed
Document Version dbText PrimaryKey ForiegnKey Indexed

Employees
EmployeeNo dbLong PrimaryKey Indexed
LastName dbText
FirstName dbText

Relationships:
Name: Change TypeDocument Information
Table: Change Type
Foreign Table: Document Information
PK: Change Type FK:Change Type

Name: Document InformationDraft Reviews
Table: Document Information
Foreign Table: Draft Reviews
PK: Document Type FKocument Type
PK: Document Series FKocument Series
PK: Document Version FKocument Version

Name: Document TypeDocument Information
Table: Document Type
Foreign Table: Document Information
PK: Document Type Number FKocument Type

Name: Document TypeDraft Reviews
Table: Document Type
Foreign Table: Draft Reviews
PK: Document Type Number FKocument Type

Name: EmployeesDocument Information
Table: Employees
Foreign Table: Document Information
PK: EmployeeNo FK:SOP Lead Name

Name: EmployeesDraft Reviews
Table: Employees
Foreign Table: Draft Reviews
PK: EmployeeNo FK:SOP Lead Name

Forms:

INDIVIDUAL FORMS:
Document Information: Document Type, Document Series, Document Version, Document Title, Document Description, Change Type, Reason Change

Equipment: Purchase Order Number, Purchase Order Approval Date, Estimated Delivery Date, Actual Delivery Date

SOP Signatures: Date Document Provided for Signature, SOP Lead Name, Date SOP Lead Signed, QAQC Evaluator, Date QAQC Signed, Manager Name, Date Manager Signed

Draft Review: Review Number, SOP Lead Name, SOP Lead Date Assigned, Date SOP Lead Draft Complete, Field Evaluator, Date Draft Provided for Field Eval, Date Field Eval Complete, QAQC Evaluator, Date Draft Provided to QAQC, Date QAQC Evaluation Complete, Document Type, Document Series, Document Version {This is a Continuous form since there will be more than one review for each document}

GROUPED FORMS:
CREATE SOP: Document Information (Master Table); Tabs for: New Equipment, Draft Review, SOP Signatures, Finalized

SUPPLEMENT SOP: Document Information, Tabs for: Draft Review, SOP Signatures, Finalized

PEN AND INK SOP: Document Information, Tab for: Finalized

REVISE SOP: Document Information, Tabs for: Draft Review, SOP Signatures, Finalized

 

[jrbarnett]

How do I create a form control that when selected it has a combo box appear with options that have different forms opened depending on the selection?

Create a new form, add a combo box to it with your options as a value list, or to take them from a table in your database.

Also, how do you have other columns on the combo box find previous and current records and use a formula to input values into the opened form?

This is a fairly generic answer, but use the facilities of Access to look up data based on the current record. A combination of DLookup, recordset or queries using GROUP BY and aggregate functions should be good enough to pick up what you need.

Finally, how do you have the previous record moved into an archive table, when a document is changed and the document version changes, so that only the most recent version is accessible

You need to write event handlers in VBA on your data entry/update forms to copy the current data to archive when it is saved or closed, this can also increment the version number and stamp the changes with the date/time and the user who made the changes for your archive purposes.

Very Important: If your archives are used for any sort of statutory data logging (rather than just history tracking for your own purposes), then you need to lock down the user interface as well, otherwise it is far too easy for somebody to manipulate the data in the archive tables directly.

John

 

[dhookom]

Your table structure is similar to what I thought it might be. You have committed spreadsheet by using all of those date fields in your Document Information table. What happens when the QA manager wants to record another "step/Date" in the doc management process? Are you going to want to create one or more fields in this table and change all of your queries, forms, reports, code, ...?

IMO, stop developing until you have the tables correct.

I have built similar solutions before and keep a "workflow" table of dates. I would have added a single field primary key of Autonumber named [DocID]. No one needs to see this number. The workflow table might look like:
[tt][blue]
tblWorkFlows
=======================
wrfWrFID autonumber primary key
wrfTitle values like "review by manager", "EPA Approval"
wrfActive Y/N is this active
wrfSeq numeric value that describes the expected order
wrkDaysReq Typical days to accomplish this WF
wrfNotes

tblDocWorkFlow
=======================
dwfDWFID autonumber primary key
dwfDocID link to [Document Information].DocID
dwfWrFID link to tblWorkFlows.wrfWrFID
dwfDate date completed
dwfStatus
dwfNotes
dwfEmployeeNo links to Employees.EmployeeNo
[/blue][/tt]

Duane
Hook'D on Access
MS Access MVP

 

[Colebean]

Thanks Duane! I'm restructuring the tables now. You are absolutely correct, work flows would be a great solution. I have had a hard time trying to develop this database, I have developed very small straightforward databases before but this is driving me up a wall! I appreciate all of your help!

 

[MajP]

Next remove every space within your names.

DocumentType. Not Document Type. This will save a lot of pain later.

I agree with Duane of using a single autonumber pk (DocID). Leave (DocType, DocVersion, DocSeries) as an index (no duplicates and required) in the document table. Use DocID as the foriegn key in all tables.

I know different people have different opinions about composite keys, natural keys, and autonumbers. Theory aside, I just think that this will make things much easier. Now you can build a search form that allows you to find "where DocID = 3" vice
"where [Document Series] = 1 AND [Document Type] = 2 and [Document Version] = 'v1.0'"

 

[MajP]

So assuming you change your names to

DocumentInformation
DocID PK autonumber
DocumentType byte
DocumentSeries decimal
DocumentVersion decimal not text

The following functions will get you a long way and will be used throughout the application

Public Function getStrVersion(decVer As Single) As String
  getStrVersion = Format(decVer, "v#.0")
End Function
Public Function getTrackingNumber(DocID As Long) As String
  getTrackingNumber = getType(DocID) & Format(getSeries(DocID), ".000") & getStrVersion(getVersion(DocID))
End Function
Public Function getVersion(DocID As Long) As Single
  Const fldName = "DocumentVersion"
  Const tblName = "DocumentInformation"
  getVersion = DLookup(fldName, tblName, "DocID = " & DocID)
End Function
Public Function getSeries(DocID As Long) As Single
  Const fldName = "DocumentSeries"
  Const tblName = "DocumentInformation"
  getSeries = DLookup(fldName, tblName, "DocID = " & DocID)
End Function
Public Function getType(DocID As Long) As Integer
  Const fldName = "DocumentType"
  Const tblName = "DocumentInformation"
  getType = DLookup(fldName, tblName, "DocID = " & DocID)
End Function

Public Function getNextSeries(DocID As Long) As Single
  getNextSeries = getSeries(DocID) + 0.001
End Function
Public Function getNextVersion(DocID As Long) As Single
  getNextVersion = getVersion(DocID) + 1
End Function
Public Function getMaxSeriesForType(typeID As Integer) As Single
  Const fldName = "DocumentSeries"
  Const tblName = "DocumentInformation"
  Const typeName = "DocumentType"
  Dim strWhere As String
  
  strWhere = typeName & " = " & typeID
  getMaxSeriesForType = DMax(fldName, tblName, strWhere)
 End Function

Public Function getNextSeriesForType(typeID As Integer) As Single
  getNextSeriesForType = getMaxSeriesForType(typeID) + 0.001
End Function

Public Function createFromType(typeID As Integer) As String

  Dim snglSeries As Single
  Dim strSql As String
  
  snglSeries = getNextSeriesForType(typeID)
  strSql = "Insert into DocumentInformation (DocumentType, DocumentSeries, DocumentVersion) values ( "
  strSql = strSql & typeID & ", " & snglSeries & ", 1)"
  Debug.Print strSql
  CurrentDb.Execute strSql
  createFromType = formatTrackingNumber(typeID, snglSeries, 1)
End Function

Public Function formatTrackingNumber(typeID As Integer, series As Single, version As Single) As String
  formatTrackingNumber = (typeID) & Format(series, ".000") & getStrVersion(version)
End Function

Assume I have this data

DocID DocumentSeries DocumentVersion DocumentType
1 0.001 1 1
2 0.001 2 1
3 0.002 1 2
5 0.002 1 1

If I get the DocID such as a search form I can do the following
?getType(1)
1
?getSeries(1)
0.001
?getVersion(1)
1
?getStrVersion(1)
v1.0
?getTrackingNumber(1)
1.001v1.0
?getNextSeries(1)
0.002
?getNextVersion(1)
2

If I pass the type
?getMaxSeriesForType(2)
0.002
?getNextSeriesForType(2)
0.003


?formatTrackingNumber(6,.004,5)
6.004v5.0

So for example. I need to create a new SOP.
I pass the type in. It gets the next series for that type and the version is 1.

Then run an input query. Example
?createFromType(2)

this returns 2.003v1.0
and creates a record, type 2, series .003, version 1.0

 

[MajP]

please change to this to account for the first record of that type.

Public Function getMaxSeriesForType(typeID As Integer) As Single
Const fldName = "DocumentSeries"
Const tblName = "DocumentInformation"
Const typeName = "DocumentType"
Dim strWhere As String

strWhere = typeName & " = " & typeID
getMaxSeriesForType = Nz(DMax(fldName, tblName, strWhere), 0)
End Function

Also you need an archive field in your table

DocumentInformation
blnDocumentArchived (yes no)

Now you archive the document after you create/modify

Public Sub archiveDocument(DocID As Long)
Dim strSql As String
strSql = "Update DocumentInformation SET blnDocumentArchived = -1 where DocID = " & DocID
CurrentDb.Execute strSql
End Sub

So looking at my function create from type see if you can come up with the following functions:

Public Function supplementFromSeriesVersion(series As Single, version As Single) As String

End Function

Public Function reviseFromSeriesVersion(series As Single, version As Single) As String

End Function


Now You need a robust search form. To do this make a tabular continous form with at least these fields

Type, Series, Name, Version, Title, and the thing I call a tracking number(i.e 1.001v1.0) which can be a calculated field in a query.

Above each one of columns use a command button for the lable. When we click these buttons we will sort the form based on that column. In the tag property of the command button put the associated fields name.

Now above those (maybe the header) put three comboboxes. Type, Series, Version. We will make those cascading. You select a type, you will get only the series for that type, and only the versions for the series. Now this will filter the form.
So to find a record will be like your outlook inbox. You can sort by any column, and/or filter the data. Finding a record will be very easy. Once you select a record the form simply returns the [DocID]. And now you can do whatever you want to it.

Also if you did not see. Turn the version into a decimal not string.

 

[MajP]

Sorry, one last thing. You asked about how to modify a document based on possibly combo boxes that let you select, Type, Version, Series

Public Function getDocID(typeID As Integer, series As Single, version As Single) As Long
  Dim strWhere As String
  strWhere = "DocumentType = " & typeID & " AND DocumentSeries = " & series & " AND DocumentVersion = " & version
  getDocID = Nz(DLookup("DocID", "DocumentInformation", strWhere))
End Function

This would get you the docID and then

dim docID as long
docID = getDocID(combo1,combo2,combo3)
docmd.open form ..... "DocID" = docID

Give that a try. Try to set up cascading combos where when you pick type it limits the series to that type. Check the faqs.

 

[Colebean]

You are AMAZING MajP!! I am going to jump on this first thing in the morning. I just might sleep tonight for the first time in a month Thank you so much.

 

[MajP]

Now, this could all be done using composite keys but you can see it would be much more cumbersome. Using a single DocID may not be the purist approach, but it will make things much simpler.

One other thing. Instead of a continous form. Do the following. Build a form with three combos at the top (Type, series, version). Below the combos add a big listbox. The listbox needs to be multicolumn with at least trackingnumber, type, series, version, name. (other fields if room permits). Again put the command buttons on top of the listbox (format to make the same width as the column). The command buttons are the labels and have the field name in the tag property. Make it look like your outlook inbox. If you click on Subject it sorts by subject. Put an ok and cancel button at the bottom of the form. Do not worry about the code, I have a class module that will do most of this with very little code.

If you have real or notional data populate the tables. It will make demoing this a lot easier.

 

[MajP]

One more thing. Add another small pop up form. Put on this a Type Combo, and Series Combo. Read in the Faqs how to cascade combos. Set this up so you pick a type, It unhides the series combo, and the series combo filters to series associated to the type. Put on this form an OK and Cancel button. Make the cancel button close the form, make the OK button hide the form (visible = false).

On the main form I would have
Option group with
Start New Process
Modify Existing Process

Combo For Change Type

Once they select a change type it will either pop open the
Find a Document Search form, or the Small Type Series pop up.

Once you get all of this done, post a version and I will show you how to throw all of this together.

 

[Colebean]

Hi MajP! Thank you so much for all of your help!! I think I have the database reorganized according to your suggestions. I put in some trial data. Here's the link to the updated database:

SOP Document Control System-Tek-Help.accdb

I added an archive table so that I can hopefully cut and paste a record when archived. I was hoping this would let me create a report so that we can view all of the maintenance histories of a doc. Also, I put a retire option on the main form so that I can put something into the archive table manually, in case of equipment abandonment. I only want most employees to be able to access the Active SOPs (most recent version). I also added a review button. Each SOP has to be reviewed annually.

I keep getting .ooo for document series values and 0 for version values. Also, I'm not sure if the 2 combo forms are going to work the way I need them to or not...I need one form for Creating A New SOP Process (create, supplement, revise) and another to go back into the database after a process is created to fill in more dates for whatever process was started (create, supplement, revise)so it doesn't populate a new number. Does this make any sense?

I had a question on the main form, I am not sure how to bind it to both the Change Type and Document Information tables?

Below is a list of my tables/relationships. I also listed which forms were newly added per your recommendations.


Archive
ArchiveID dbLong PrimaryKey Indexed
DateRetired dbDate
ReasonRetired dbText
ArchiveDocumentLink dbMemo
TrackingNo dbSingle Indexed

ChangeType
ChangeType dbText PrimaryKey Indexed

DocumentInformation
DocID dbLong PrimaryKey Indexed
DocumentType dbInteger ForiegnKey Indexed Required
Document Series dbDecimal Indexed
Document Version dbDecimal Indexed
Document Title dbText Indexed
DocumentDescription dbText Indexed
ChangeType dbText ForiegnKey Indexed
ReasonChange dbText
PONo dbText
POApprovalDate dbDate
EstimatedDeliveryDate dbDate
ActualDeliveryDate dbDate
SharepointPostedDate dbDate
EPASubmittedDate dbDate
EPAApprovalDate dbDate
DocLink dbMemo
DateDocProvidedSig dbDate
NameSOPLead dbText
SOPLeadSigDate dbDate
QAQCEvaluator dbText
DateQAQCSigned dbDate
ManagerName dbText
ManagerSigDate dbDate
ReviewStartDate dbDate
ReviewCompleteDate dbDate
ReviewerName dbText
blnDocumentArchived dbBoolean
TrackingNo dbSingle Indexed
ArchiveID dbLong ForiegnKey Indexed

DocumentType
DocumentTypeNumber dbInteger PrimaryKey Indexed
SOPType dbText

DraftReviews
DocID dbLong ForiegnKey Indexed Required
ReviewNumber dbInteger
SOPLeadName dbText
SOPLeadDateAssigned dbDate
DateSOPLeadDraftComplete dbDate
FieldEvaluator dbText
DateDraftProvidedFieldEval dbDate
DateFieldEvalComplete dbDate
QAQCEvaluator dbText
DateDraftProvidedQAQC dbDate
DateQAQCEvaluationComplete dbDate
DraftID dbLong PrimaryKey Indexed

Employees
EmployeeNo dbLong PrimaryKey Indexed
LastName dbText
FirstName dbText


Name: ArchiveDocumentInformation
Table: Archive
Foreign Table: DocumentInformation
PK: ArchiveID FK:ArchiveID

Name: ChangeTypeDocumentInformation
Table: ChangeType
Foreign Table: DocumentInformation
PK: ChangeType FK:ChangeType

Name: DocumentInformationDraftReviews
Table: DocumentInformation
Foreign Table: DraftReviews
PK: DocID FKocID

Name: DocumentTypeDocumentInformation
Table: DocumentType
Foreign Table: DocumentInformation
PK: DocumentTypeNumber FKocumentType

Name: EmployeesDocumentInformation
Table: Employees
Foreign Table: DocumentInformation
PK: EmployeeNo FK:NameSOPLead

Name: EmployeesDraftReviews
Table: Employees
Foreign Table: DraftReviews
PK: EmployeeNo FK:SOPLeadName

Forms:
frmDocCreateSearch
frmDocSearch
frmMainMenu

Module LookUpDocumentNumberPopulate

 

[MajP]

I will look at this tonight and get back to you.

"I need one form for Creating A New SOP Process (create, supplement, revise) and another to go back into the database after a process is created to fill in more dates for whatever process was started (create, supplement, revise)so it doesn't populate a new number. Does this make any sense?"

If you have a option group
Create New SOP
Modify SOP
And a combo
Create
Supplement
Revise

Then I simply think
If optiongroup = Create then
if combo = create do something
if combo = supplement do something
if combo = revise do something
else if option group = modify then
if combo = create do something
if combo = supplement do something
if combo = revise do something
end if

 

[dhookom]

IMO, your tables are still wrong. I wouldn't go any further until your tables are normalized.

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

Duane,
Can you work with the OP on the tables? I will work with the interface issues.

I really just need these fields I believe in the docinformation.
For documentInformation
DocID dbLong PrimaryKey Indexed
DocumentType dbInteger ForiegnKey Indexed Required
Document Series dbDecimal Indexed
Document Version dbDecimal Indexed
Document Title dbText Indexed
DocumentDescription dbText Indexed
ChangeType dbText ForiegnKey Indexed
ReasonChange dbText
DocLink dbMemo
blnArchived

to demonstrate the naviagation, incrementing, and searching.

 

[dhookom]

Colebean suggested the tables were being restructured back on 1/24. I assumed he/she understood the reason why. I would be willing to answer additional questions on how to implement a more normalized structure.

The "workflow" type solution takes a look at the process or steps a document goes through. Each of these steps should create a new record in a workflow table. The benefits of this are you can add or remove steps as needed without any structure changes.

Duane
Hook'D on Access
MS Access MVP

 

[Colebean]

I decided not to do the workflow because MajP had done so much work trying to help me already. This process really won't change since they have been doing the same way since the 70s. Duane, do you think it is possible to do it this way? Where did I go wrong with the tables? I really appreciate all of your input!!

 

[Colebean]

Duane, I'm going to go ahead and redo the tables in the workflow design you suggested tonight. You said I'm not normalized, which columns do you think I should eliminate?

 

[Colebean]

Duane, sorry, nevermind about the tables--I finally got a pretty good grasp on what to do to restructure them(it is starting to make more sense to me now, I'm a little slow) so will have the new tables posted tonight.

 

[Colebean]

OK! I have the tables in workflows!! Below are the tables/relationships and I have posted the database at

SOP-DCS-Tables-Workflows.docx

Please let me know what you guys think. I changed ChangeType to ChangeCategory, I figured it could be confusing with DocumentType. I re-labeled everything so that it made more sense (I hope).

tblChangeCategory
sctChangeID dbLong PrimaryKey Indexed
sctChangeCategory dbText Indexed

tblDocInfo
docID dbLong PrimaryKey Indexed
docType dbInteger ForiegnKey Indexed Required
docSeries dbDecimal Indexed
docVersion dbDecimal Indexed
docTitle dbText Indexed
docDescription dbText Indexed
docChangeCategory dbText ForiegnKey Indexed
docChangeReason dbText
blnDocArchived dbBoolean
docTrackingNo dbSingle Indexed

tblDocType
DocumentTypeNo dbInteger PrimaryKey Indexed
SOPType dbText Indexed

tblDocWorkFlow
dwfDWFID dbLong PrimaryKey Indexed
dwfDocID dbLong ForiegnKey Indexed
dwfWrFID dbLong ForiegnKey Indexed
dwfProvided dbDate
dwfDateComplete dbDate
dwfStatus dbText
dwfNotes dbText
dwfEmployeeNo dbLong ForiegnKey Indexed

tblDraftFieldEvalWF
fdrFieldID dbLong PrimaryKey Indexed
fdrDWFID dbLong ForiegnKey Indexed
fdrReviewNo dbInteger

tblDraftQaqcEvalWF
qdrQaqcID dbLong PrimaryKey Indexed
qdrDWFID dbLong ForiegnKey Indexed
qdrReviewNo dbInteger

tblEmployees
EmployeeNo dbLong PrimaryKey Indexed
LastName dbText
FirstName dbText

tblEpaWF
epaID dbLong PrimaryKey Indexed
epaDWFID dbLong ForiegnKey Indexed
epaApprovalStored dbText

tblLeadSigWF
sigLeadID dbLong PrimaryKey Indexed
sigLeadDWFID dbLong ForiegnKey Indexed

tblManagerSigWF
sigManagerID dbLong PrimaryKey Indexed
sigMgrDWFID dbLong ForiegnKey Indexed

tblNewEquip
equNewEquipID dbLong PrimaryKey Indexed
equDocID dbLong Indexed
equPONumber dbText
equPOApprovalDate dbDate
equEstimatedDeliveryDate dbDate
equActualDeliveryDate dbDate

tblQaqcSigWF
sigQaqcID dbLong PrimaryKey Indexed
sigQaqcDWFID dbLong ForiegnKey Indexed

tblRetiredSop
arcRetireID dbLong PrimaryKey Indexed
arcDocID dbLong ForiegnKey Indexed
arcDateRetired dbDate
arcReasonRetired dbText
arcretireDocLink dbMemo

tblSharepointWF
sptID dbLong PrimaryKey Indexed
sptDWFID dbLong ForiegnKey Indexed
sptDocLink dbMemo Indexed

tblSopAnnualReview
sarReviewID dbLong PrimaryKey Indexed
sarDocID dbLong ForiegnKey Indexed
sarAdminEmail dbMemo

tblSopLeadWF
sdrLeadDraftID dbLong PrimaryKey Indexed
sdrDWFID dbLong ForiegnKey Indexed
sdrReviewNo dbInteger

tblWorkFlows
wrfWrFID dbLong PrimaryKey Indexed
wrfTitle dbText
wrfActive dbBoolean
wrfSeq dbInteger
wrkDaysReq dbInteger
wrfNotes dbText


Name: tblChangeCategorytblDocInfo
Table: tblChangeCategory
Foreign Table: tblDocInfo
PK: sctChangeCategory FK:docChangeCategory

Name: tblDocInfotblDocWorkFlow
Table: tblDocInfo
Foreign Table: tblDocWorkFlow
PK: docID FK:dwfDocID

Name: tblDocInfotblRetiredSop
Table: tblDocInfo
Foreign Table: tblRetiredSop
PK: docID FK:arcDocID

Name: tblDocInfotblSopAnnualReview
Table: tblDocInfo
Foreign Table: tblSopAnnualReview
PK: docID FK:sarDocID

Name: tblDocTypetblDocInfo
Table: tblDocType
Foreign Table: tblDocInfo
PK: DocumentTypeNo FK:docType

Name: tblDocWorkFlowtblDraftFieldEvalWF
Table: tblDocWorkFlow
Foreign Table: tblDraftFieldEvalWF
PK: dwfDWFID FK:fdrDWFID

Name: tblDocWorkFlowtblDraftQaqcEvalWF
Table: tblDocWorkFlow
Foreign Table: tblDraftQaqcEvalWF
PK: dwfDWFID FK:qdrDWFID

Name: tblDocWorkFlowtblEpaWF
Table: tblDocWorkFlow
Foreign Table: tblEpaWF
PK: dwfDWFID FK:epaDWFID

Name: tblDocWorkFlowtblLeadSigWF
Table: tblDocWorkFlow
Foreign Table: tblLeadSigWF
PK: dwfDWFID FK:sigLeadDWFID

Name: tblDocWorkFlowtblManagerSigWF
Table: tblDocWorkFlow
Foreign Table: tblManagerSigWF
PK: dwfDWFID FK:sigMgrDWFID

Name: tblDocWorkFlowtblQaqcSigWF
Table: tblDocWorkFlow
Foreign Table: tblQaqcSigWF
PK: dwfDWFID FK:sigQaqcDWFID

Name: tblDocWorkFlowtblSharepointWF
Table: tblDocWorkFlow
Foreign Table: tblSharepointWF
PK: dwfDWFID FK:sptDWFID

Name: tblDocWorkFlowtblSopLeadWF
Table: tblDocWorkFlow
Foreign Table: tblSopLeadWF
PK: dwfDWFID FK:sdrDWFID

Name: tblEmployeestblDocWorkFlow
Table: tblEmployees
Foreign Table: tblDocWorkFlow
PK: EmployeeNo FK:dwfEmployeeNo

Name: tblWorkFlowstblDocWorkFlow
Table: tblWorkFlows
Foreign Table: tblDocWorkFlow
PK: wrfWrFID FK:dwfWrFID