library design

[Elise R via AccessMonster.com]

I need a library to hold Standard Operation Procedures. There is appx 1200
SOPs that we use. Whenever an SOP is updated, the reference number changes
(example: AB.01.0.1 CHANGES TO AB.01.0.2) on the implementation date and the
old one is archived (archival date may be different for each of the
11departments). What is the best way to set this up? By the way, some SOPs
belong to only one department and some may belong to seven (7) departments,
etc. How can I enter the information in once and have it go to the other
departments, if any?

Any help would be greatly appreciated.

 

[KARL DEWEY]

You should not have it but once. A single department needs to control the
document and coordinate changes with the others that use or are affected by
it.

 

[Hermawih]

Create 3 tables.

A. table for SOP :
SOPID : Number (PK)
SOPName : Text (ie : AB.01.02)
Status :Number (1 = active , 2 = Archives)
B. table for department :
DeptID : Number(PK)
DeptName:Text
C. Linking Table for SOP and Depatment .
DeptID : Fkey
SOPID : FKey
ArchivedDateate/time .. When it is archived

Here is the diagram ,

DEPT DEPTSOP SOP
---------------------------
DeptID DeptID SOPID
DeptName SOPID SOPName
....... ArchivedDate Status
...... ...... .....

if you want to changed the SOP then
A. Create new sop on table SOP with status = 1 . Changed old SOP with
status = 2
B. Stamped the archived date on linking table .

Is that what you want ???