Document management & control

Question

Hi there,

My apologies if this message is long, but it includes an illustration of what I'm trying to achieve.

I am responsible for managing job descriptions, which often go through numerous iterations before they're approved. When they're updated, they go through another process of iterations before approval and activation as the new job description number/title, etc. We need to be able to see the historical data so we can determine how/when a position has evolved over time. For version control, I'm proposing we use the numerical system below. I know how to create tables with unique keys and concatenate them to create a link, but I'm not sure how to link the two together to function as you see below.

I'm trying to build a database for document management and control. I have an Excel worksheet (flat file) that contains the header information for a document (ID#, title, current details, status, notes, etc.), and I want to create a linked table that shows the activity history for each document.  (please see Example*). I've seen this in Access, but my Access skills are out of date, and I can't find the functions I used to know so well. Over the past 10 years, my career path has veered away from IT into Human Resources, so I'm pretty rusty.

Once I have the basics of this DB sorted out, I'll need to make it user friendly for my co-workers, who have never used Access before, but that's a question for another day. :)

Thank you for your help!

Dana

*Example: Here's a simplified idea of what I'm trying to create (within the limitations of this message text editor):

Position ID#	Title	Last Updated	Current Status	Notes
+ 122	Job Title A	23-Mar-2013	Awaiting approval	Stalled; Missing data
+ 123	Job Title B	23-Mar-2013	Active
- 124	Job Title C	14-May-2014	Awaiting approval
- Version# -	- Action Date -	- Action Taken -	- Action Status -	- Next Step -
123.11	7-Jun-2014	Sent draft for review	In progress	Manager approval
123.10	2-Jun-2014	Update requested	Initiated	Legislative changes
124.10	24-Jan-2012	Finalized & Approved	Complete	None
124.02	18-Jan-2012	Received Manager Approval	Proofreading	Sign-off
124.01	13-Jan-2012	Sent draft for review	In progress	Manager approval
124.00	11-Jan-2012	New Item request received	Initiated	Finalize details
- 125	Item D	05-Jan-2014	Active
- Version# -	- Activity Date -	- Action Taken -	- Action Status-	- Next Step -
125.11	29-Nov-2005	Title Change Approved	Complete	None
125.10	3-Apr-2001	Change department ownership	Complete	None
125.10	5-Dec-1998	Received document	Legacy document	None

Answer 1

This is the element I'm specifically looking for assistance with. I don't know how to create the mainform/subform combo! Once I've got that working, I think I'm golden.

Dana

Toronto, Canada

Use the form wizard! It will walk you through setting up the main form. Then use the wizard to create your subform in a tabular format. This will allow you to see multiple records for the position. 

Finally, you go back into the main form in Design view and use the Wizard to add the subform onto the main form. It will prompt you to set up the links. It will probably suggest the link on Position_ID.

Answer 2

When you go on to forms, I would create to mainform/subform combos. One to show positions not yet approved, the other for Active positions. You then filter the main form for whichever and the subform will show the history records for that position. 

This is the element I'm specifically looking for assistance with. I don't know how to create the mainform/subform combo! Once I've got that working, I think I'm golden.

Dana

Toronto, Canada

Answer 3

"I am surprised you are puting 123.11 under 124. That may be a limitation of the text editor :-)"

It absolutely is. :-)

-- Use the Relationships form to draw lines between corresponding fields and importantly in the dialog that pops up check the box to enforce the relation. You now have taken care of the R in RDBMS.

I'm knowledgeable enough to create the tables up to the point of creating the table relationships. It's the part that comes after that, that I'm fuzzy on.

I think this question may have been addressed in the second response.

Dana

Toronto, Canada

Answer 4

I just want to expand a bit on Tom's suggestion. 

In your example you use ActionDate in one instance and Activity Date in another. I'm guessing here that this is your intention was two tables one for the first set of iterations prior to approval and another to show the iterations involved in updating. Two tables should not be necessary here as the Status field can be used to indicate the status. 

Also, by using an autonumber PK, you can make your joins much easier, I don't recommend using composite keys if they can be avoided. 

For the Version number field that Tom recommends see my blog article on Sequential numbering. You can use the methods described to create a sequential number for each Position number.

When you go on to forms, I would create to mainform/subform combos. One to show positions not yet approved, the other for Active positions. You then filter the main form for whichever and the subform will show the history records for that position.

Answer 5

Hi Dana,

Looks like a fun project. The data you are showing hints at a couple of tables. I am surprised you are puting 123.11 under 124. That may be a

limitation of the text editor :-)

Right now I'm not focused on forms or reports, just on database design. Tables and relationships.

I would bring the data from Excel into the first table:

tblPositions

PositionID AutoNumber PK  'This is NOT your "Position ID#"

PositionIDNumber long integer required uniqueindex

Title text(50) required

DateCreated datetime required

CreatedByID long int required   'Links to tblUsers

DateUpdated datetime required

UpdatedByID long int required

'NOT Current status, that will be the most recent ActionStatus from table2.

Notes memo not_required

tblActionsOnPosition

ActionOnPositionID autonumber PK

PositionID long int required FK

VersionNumber text(10) required uniqueindex on PositionID+VersionNumber

ActionDate datetime required

ActionTaken text(255) required

ActionStatusID long int required   'Links to tblActionStatus

NextStep text(50) required default="none"?

Use the Relationships form to draw lines between corresponding fields and importantly in the dialog that pops up check the box to enforce the relation. You now have taken care of the R in RDBMS.