Variant-step procedures in Access

[EdwardMartinIII]

Well, I THOUGHT I had a clever solution, but I might have been tricked by my own cleverness.

I'm writing a batch of procedures. Just ordinary technical procedures (how to change out a backup tape, how to check mail logs, how to reset security protocols accidentally flipped by clueless co-worker, etc.).

There are about a hundred procedures, but that number could easily grow over the years.

My first thought was to use a database (I have Access on this machine). Every Procedure has certain common fields, such as "Dependent process(es)", "Department", "Initiating Event" and so forth. Also, they all have "Procedure steps" in common.

Initially, I figured Procedure Steps would just be a MEMO field, which means I could put any number of steps in there.

As an Access database, I could feed this data to an internal network website. I could also allow Word documents to print the most-current-procedures by pulling data from the database.

However, I hit a glitch.

Many procedures are happier with pictures, and sometimes those pictures need to be with their steps.

So, a field could be "StepText" (string) and then another field (integer) called "StepNumber" and a third field called "StepImage" (blob or image)

The part that troubles me is that there can be practically ANY number of steps to any given procedure. Usually more than three, usually less than, say, thirty.

I can only think of two ways of handling this potentially confusing issue:

1. My DumbButSimple solution is to just add, say, thirty fields, called "StepText_01", "StepText_02", "StepText_03", etc. and thirty fields called "StepTImage_01", "StepImage_02", "StepImage_03", etc. to each Procedure's record. that way, I already have an order, and I can have an image for every step, if I want.

2. The SeemsSmartbutMayHaveHiddenBombs solution is to have a third field called "StepNumber_01", etc. and somehow extract those first, then format, and then bring in each record's Step and Image.

Ideas? Suggestions?

Edward

"Cut a hole in the door. Hang a flap. Criminy, why didn't I think of this earlier?!" -- inventor of the cat door

 

[fneily]

There are seven Access forums. This should probably be posted in Microsoft: Access Tables and Relationships
You'll get the Access gurus answering your question.

It seems you don't create Access databases alot so:
Your idea No.1 violates the first form of Normalization. Drop the numbers and you have duplicate column headings. This'll lead to having empty cells. In theory, you'll be creating variable length records which is against the normalization protocol. See:
Fundamentals of Relational Database Design

http://r937.com/relational.html

Next, you don't store images in an Access table. See:
How to display an image from a folder in a form or in a report in Access 2000

http://support.microsoft.com/?kbid=210100

Add pictures on access table
thread700-1159849

Manage an "image" field in VB/Access
thread700-1124341

If I understand your post, I see it this way:
A department runs a procedure that has many steps. This procedure has a dependent process and an initiating event.
Tables:
tblDepartment with PK(primary key) of DeptID, Description
tblDependentProcess with Pk of DPID, ProcessName
tblInitiatingEvent with Pk of IEID, Description
tblProcedure with multi-field PK of ProcID and StepNum
other fields are Steptext, StepImagePath

Now bring everything together in
tblUsage with PK of UsageID example:
UsageID DeptID IEID DPID ProcID
1 Acct Login Validation Valid1

Connecting the tables in a query, you can have a form or report showing a departments procedures and steps with images and the accompaning dependent process and initiating events.

Assumptions: Dependent processes can be enumerated.
Initiating Events can also be listed uniquely.
Dependent processes and initiating events will appear across departments.

 

[EdwardMartinIII]

It seems you don't create Access databases alot"

That is correct. I am new to the idea, but am trying to cram tutorials into my head as much as I can, while remaining awake. The reading fails to rivet. 8)

"Your idea No.1 violates the first form of Normalization. Drop the numbers and you have duplicate column headings. This'll lead to having empty cells."

I don't understand this. The numbers differentiate the columns, so of course dropping them makes the columns the same. Thus, dropping them seems ill-advised.

Every procedure has a step #1. If it only has one step, then #2-#29 would be NULL values, I would think. NULL's a perfectly acceptable value in a field.

Most every procedure has a step #2, and the frequency of those steps drops as the columns approach "30". I just picked "30" as some arbitrary limit. In more than a decade of technical writing, I can't once recall documenting a process with more than 20 steps.

Another bonus is that it's impossible to have more than one step share a step count.

Not sure what's wrong with empty cells. I've seen situations where there are empty cells, such as sales records where the sales person forgot to code in, so there is no (for example) EmployeeID in a transaction table.

"Next, you don't store images in an Access table."

Well... you could, but the article points out that it gets bigger. I forgot to add in my post that a later test used a file URL for the images, instead of an actual file embedded in the db.

Considering we're trying to deploy using an HTML site and a series of Word documents, this makes more sense anyway. Sorry I forgot to mention that.

"This procedure has a dependent process and an initiating event"

My understanding is that any given process might have one or more dependent processes and one or more initiating events. For example, a server reboot process might predicate a tape change process, but the tape change process might also trigger every Monday.

The idea being that there will somewhere (probably a table) be a list of initiating events and the User picks which event just happened (or perhaps some process notifies the Users) and they are presented with the procedures they need to follow.

Why "multi-field PK of ProcID and StepNum"?

ProcedureID is supposed to be unique. Procedures usually have a title and an ID number and titles might be the same across different departments (each department might have an "empty the trash" procedure), but the Procedure Number (or ProcID) is unique across the whole company. "StepNum" is just which number this particular step is, when sorting the steps.

Thanks so far. Still very new to this. Questions, questions, questions...

Cheers,

Edward

"Cut a hole in the door. Hang a flap. Criminy, why didn't I think of this earlier?!" -- inventor of the cat door

 

[fneily]

Again, a very important non-intuitive concept is normalization. The first normal form (step, rule) is that there are no repetitive column headings and all fields are atomic. Adding numbers to a field name does not make it unique. Example: Project1, Project2. The overall category is just Project. What happens if there is only one project? Then Project2 is null. What happens if in the future Project3 needs to be added? Project2999? Add more columns??? Wrong. This leads, again to variable length records(look at where the data actually resides. The right border is then jagged). A table MUST always maintain a rectangular geometry. If you've seen RELATIONAL databases with alot of nulls, I would question their structure. A cell may be empty ONLY if the data is immediately not available but will be there later. NOT as a place holder. Null is NOT a perfectly acceptable value in a relational database table. Do not confuse an Access table with an Excel spreadsheet. They are not alike in any way.
You mentioned no EmployeeID in a transaction table. There were no safeguards or validation set up to prevent that?? Very bad design.
I wasn't sure about multiple dependent process and multiple initiating events. This means that tables must be structured to take that into account.
My understanding is this(at least when I created Procs for AT&T): A procedure has one to many steps. So this is why a multi-field PK for tblProcedure. It would look like this:
ProcID StepNum StepText StepImagePath
Proc1 1 Blah c:\imagefolder\first.jpg
Proc1 2
Proc1 3
Proc2 1
Proc3 1
Proc3 2
etc.
Primary Keys MUST be unique in tables. As you can see, ProcID is not unique. So you combine fields together to make a unique key. In this case ProcIDStepnum. So Proc11 is different from Proc12. Etc.

Create your table structures, then post them in
Microsoft: Access Tables and Relationships

Again, that's where all the Access gurus play so you'll get more opinions, advice, critics.

If you really want to read about normalization theory, google Ted Codd. He is the creator and namer of it. I met him in 1975 at MIT.

 

[EdwardMartinIII]

Yes, okay, so, normalization. Must always be rectangular. Gotcha.

I've seen otherwise, but okay.

So at the lowest level, if there are some varying number of Procedures, and Procedures can have anywhere from 2 to 30 steps in them, and each step may or may not be associated with URL, then it seems as if SOMETHING'S gonna be blank.

What am I missing, here?


Edward

"Cut a hole in the door. Hang a flap. Criminy, why didn't I think of this earlier?!" -- inventor of the cat door

 

[EdwardMartinIII]

You mentioned no EmployeeID in a transaction table. There were no safeguards or validation set up to prevent that?? Very bad design."

I am not qualified to comment on the design, just its existence. 8)

"Do not confuse an Access table with an Excel spreadsheet."

It is not so much confusion as it is the framework I am more familiar with. I am aware that there are supposed to be differences between the two.

"As you can see, ProcID is not unique."

Ah, I see the misunderstanding. You are using shorthand and I am new, therefore I try to make sense of it and goof.

Every Procedure is unique. The TITLES (such as "Empty the Trash") might be duplicates (the cafeteria has an "EmptyTheTrash" procedure, and the IS department has one as well), but the actual Procedure Number (such as "Procedure_00223") is unique.

I looked back at your first example. I had not considered departments, but I expect that's a natural progression upwards. I would expect that a Department could have any number of procedures associated with it, and a Procedure could have any number of Departments associated with it (for example, several different departments need to do the same thing to back up their data to tape, so they all refer to the same Procedure)

Wait a second... Are you suggesting cramming EVERY Procedure and EVERY step of every Procedure into one single table? If so, then yeah, that would make the Procedure ID pretty useless as a unique identifier.

Then you would need a table of all Procedures, showing those other Procedures that are their dependencies (can vary in number) or Initiating Events (could vary in number)...

Even if I just consider Initiating Events... If I have a table with a column of Initiating Events, it can have a varying number of Procedures triggered by it. If I choke it to a column of Procedure numbers, then it'll have a varying number of Initiating Events.

(head shaking)





Edward

"Cut a hole in the door. Hang a flap. Criminy, why didn't I think of this earlier?!" -- inventor of the cat door

 

[fneily]

The only field that'll be blank is the URL. And that may or may not be filled in the future. Look at my tblProcedure table. It doesn't not matter how many procedures or how many steps in a procedure. Each step is a record. So no blanks for procedure name or stepname.

"I would expect that a Department could have any number of procedures associated with it, and a Procedure could have any number of Departments associated with it". Bingo, a many-to-many relationship. Relational database hate this, you can't have them. So to resolve this type of relationship you create what is known as a junction table which will create TWO one-to-many relationships. At minimum, this table will have the primary keys of the other two tables and any COMMON fields. That's my tblUsage.

So, you have a dependent process that could match to many procedures and a single procedure that could match to many dependent processes. Many-to-many relationship. You need another junction table. Same logic for initiating events.

Again, create your table structures, post them in the appropriate forum and let us look at them.

Concerning Access tables and Excel spreadsheets you state "I am aware that there are supposed to be differences between the two." That is a huge understatement. They are totally different. They only look alike which confuses people.

 

[EdwardMartinIII]

Oh man, this just struck me!

As I review more of the actual procedures, I'm seeing subcounts and so forth (nested lists, basically).

Also occured that as they jigger systems on this end, steps might be aded or subtracted and, of course, all succeeding steps have to keep up with the change.

So...

What if the procedure steps were memo type fields, and contained HTML fragments?

Basically, the first line in the memo field was <ol class="TopLevel"> and the last line was </ol> and everything in between was HTML?

That way, I could nest lists as much as they need, include links out to web-based documents (for example, online dictionaries), and step could have any number of illustrations necessary -- it would just be an IMG call in the HTML.

And all the supporting data, dependent functions, departments, resources, help desl personal, etc. would be much more ordinary lements in the database.

And it'll be alllll squarrrrred away. 8)

Thoughts?

Edward

"Cut a hole in the door. Hang a flap. Criminy, why didn't I think of this earlier?!" -- inventor of the cat door

 

[EdwardMartinIII]

(got to experiment to see if Word can import HTML code fragments..)


Edward

"Cut a hole in the door. Hang a flap. Criminy, why didn't I think of this earlier?!" -- inventor of the cat door

 

[fneily]

Now that is a good idea. So you'd break down a proc to atomic steps. Then the tblProcedure table would contain the procID, stepnum, and just steptext. The field steptext would be of memo type containing the HTML. And as you said, you now just put an IMG reference in the HTML code. You don't need to worry about storing/referencing the image in Access.
You might even consider this, now that I think about. Don't look at it from Proc down. Each step would be a function. So a step might be used in more then one Proc. So have a table with Procnames, a table with stepnames and the code. Now bring them together in a junction table. TblProcsteps
that would look like:
ProcstepID ProcID StepID
Again, a many-to-many relationship resolved with a junction table. So if there are any addtions or changes, you just add or delete from this one table.
Why do you care about Word? Any reports can be done in Access and then sent to word like this:
DoCmd.OutputTo acreport, "report name", "RichTextFormat(*.rtf)", "report path", False, ""
One last thought, this is quite a project. You getting good pay?

 

[EdwardMartinIII]

I think -- and I might be wrong here -- that it could be even simpler.

(thinking...)

Okay, it's not in the business rules, per se, but there should probably NOT be a shared step between any two procedures. A step can change or be edited, and it would be too much of a pain to have to find out where ELSE this step is used.

So...

table Procedure:
ProcedureID (primary key) example: MD_Proc_00042
ProcedureTitle (text) example: "Emptying the Trash"
ProcedureText (memo) (HTML fragment as described above)
ProcedureExpectedResult (text)
ProcedureFailurePlan (text)

There will probably be a few more fields in this table.

This is only one table. There would be other tables that would bring in more data, such as Department (thank you -- because now other Departments want to join in on this database), dependent procedures, contact personnel, initiating events, etc.

So.

Editing a procedure is simply editing the HTML fragment. the steps don't have to be broken up for any reason.

That fragment might contain ordered lists, links to images, links to online archives elsewhere in the world, lists to wikipedia, etc. Anything HTML-flavored.

"Why do you care about Word?"

Well, it's what I know and it's supplied for free here. I can control display styles and everything. I'm not familiar with producing reports in Access (or SQL Server, where this will probably be ported), although I'm game to try if it looks like it can make an attractive product from my HTML memo field. Attractive-to-print is important to get buy-in from the target departments.

Another possibility is to encourage them to use the HTML interface (no-brainer), but then if they choose to print, to use a special CSS to make the print look as good as possible. I'm pretty darn okay on the CSS and formatting front.

"One last thought, this is quite a project. You getting good pay?"

Yes, it's a big project, and I think they are just starting to understand how very, very useful this thing will be for them once it's done. I'm a tich under my normal payrate for this sort of thing, but (shrug) I'm learning some very useful things that I didn't know before.

Edward

"Cut a hole in the door. Hang a flap. Criminy, why didn't I think of this earlier?!" -- inventor of the cat door

 

[fneily]

Not sharing steps does make it alot easier. You'll also noticed that creating a useful database is not a one shot deal. You have to think about each part/topic/table, bounce ideas around, redesign, etc. Anytime you meet someone who claims to be an Access programmer and immediately sits in front of a computer to design a system, just walk away. They most likely don't have a clue. You start designing with paper and pencil. You, and as I've stated before, must get the tables correct first. If not, all the rest of your work is wasted. You'll hit problems and won't be able to do the analysis you want. So NORMALIZE, each table! There's a mandatory three step process which must be done in order. Also, remember no many-to-many relationships - you must create junction tables.
And then the coding part. Yeah buddy - VBA, ADO/DAO, SQL, Automation(since you'll communicate with word). I remember my first time.(sigh)
Useful? You bet! Isn't it amazing no one thought of it before? They should have been documenting all along.
Other departments now want in...sure, for a price.
By the way, first create the database by itself. It sounds like you'll want to display the info on the 'net, through a browser. That's a whole other topic. But first the database. What the heck, to jump ahead, are you talking INTRAnet or INTERnet? For INTRAnet, you won't need html.

 

[EdwardMartinIII]

They should have been documenting all along."

Some things are documented. On various scraps of paper. I'm used to having stuff like that dumped on me, though -- it's what I fix best.

"By the way, first create the database by itself. It sounds like you'll want to display the info on the 'net, through a browser. That's a whole other topic. But first the database. What the heck, to jump ahead, are you talking INTRAnet or INTERnet? For INTRAnet, you won't need html."

All internal net.


Edward

"Cut a hole in the door. Hang a flap. Criminy, why didn't I think of this earlier?!" -- inventor of the cat door

 

[fneily]

Since it's an Intranet, you can either just put the database on a shared drive, the Jet engine will handle record locking, or split the database. See Access help, search on Split and see topic: Split an existing Access database between its data and its objects
But that's way in the future.