TechWhirl (TECHWR-L) is a resource for technical writing and technical communications professionals of all experience levels and in all industries to share their experiences and acquire information.
For two decades, technical communicators have turned to TechWhirl to ask and answer questions about the always-changing world of technical communications, such as tools, skills, career paths, methodologies, and emerging industries. The TechWhirl Archives and magazine, created for, by and about technical writers, offer a wealth of knowledge to everyone with an interest in any aspect of technical communications.
Subject:Re: Looking for a database function From:Geoff Lane <geoff -at- gjctech -dot- co -dot- uk> To:TECHWR-L <techwr-l -at- lists -dot- techwr-l -dot- com> Date:Sat, 8 May 2010 23:00:53 +0100
On Saturday, May 8, 2010, Ned Bedinger wrote;
> The grid you're developing is a table, or series of tables. Why not make
> one table for each procedure? Who needs complicated tables when you have
> a database to manage the complexity for you?
> Let's do it: create a table and list the employees down the rows, and
> add one column named "status". Save the table, copy it, and name the
> copied table for a procedure you want to track. Copy the table again,
> name it for another procedure. DC al fine (do this copy/name step until
> all procedures have a table named after them. Put your status data in
> the appropriate tables, and then use the intrinsic power of databases
> to answer any questions you have about that data.
Perhaps better would be to use three tables:
- one for employees (i.e. a row for each employee)
- one for procedures (i.e. a row for each procedure)
- one for the intersection of employees and procedures (i.e. each row
records that a particular employee is trained in a particular
EmployeeID Forename Surname
1 Fred Bloggs
2 Joe Soap
3 Jane Doe
1 1 (records that Fred Bloggs is trained in Sprongling Widgets)
3 1 (Jane Doe is trained in Sprongling Widgets)
3 2 (Jane Doe is trained in Dooflaking Keefly Trunions)
With this structure, you can obtain a list of procedures that apply to
a particular employee or employees trained in a particular procedure
with some fairly simple SQL:
-- List procedures that apply to Fred Bloggs
SELECT ProcID, Procname
FROM Proc P INNER JOIN Emp2Proc E2 ON P.ProcID=E2.ProcID
-- List employees trained in Sprongling Widgets
SELECT EmployeeID, Forename, Surname
FROM Employee E INNER JOIN Emp2Proc E2 ON E.EmployeeID=E2.EmployeeID
In Access, it's fairly easy to set up datasheets to enter the data and
views to retrieve the results. If you need status, expiry dates, etc.
you can add these to the "Emp2Proc" table. (BTW, if you're using
Access remember to end each query with a semi-colon!)
The advantage of doing it this way over one table for each procedure
is that here the data is arranged to minimise anomalies. For example,
if you have one table for each procedure consider what you have to do
to add an employee, or if an employees change their names, or if an
Of course, you could just do this with a spreadsheet with one row per
employee and one column per procedure (or vice versa) and shading
cells at the intersection of employees and the procedures in which
they're trained. With judicious use of "Freeze Panes" you can even
scroll the worksheet to make it easier to read off the procedures for
an employee or the employees for a procedure.