Re: Looking for a database function

[Geoff Lane <geoff -at- gjctech -dot- co -dot- uk>]

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
  procedure).

For example:

Table Employee:

EmployeeID    Forename   Surname
1             Fred       Bloggs
2             Joe        Soap
3             Jane       Doe


Table Proc:

ProcID       Procname
1            Sprongling Widgets
2            Dooflaking Keefly Trunions


Table Emp2Proc

EmployeeID  ProcID
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
WHERE E2.EmployeeID=1


--
-- List employees trained in Sprongling Widgets
--
SELECT EmployeeID, Forename, Surname
FROM Employee E INNER JOIN Emp2Proc E2 ON E.EmployeeID=E2.EmployeeID
WHERE E2.ProcID=1


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
employee leaves.

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.

Good luck & HTH,


-- 
Geoff

^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

Use Doc-To-Help's XML-based editor, Microsoft Word, or HTML and 
produce desktop, Web, or print deliverables. Just write (or import) 
and Doc-To-Help does the rest. Free trial: http://www.doctohelp.com


 - Use this space to communicate with TECHWR-L readers - 
   - Contact admin -at- techwr-l -dot- com for more information - 


---
You are currently subscribed to TECHWR-L as archive -at- web -dot- techwr-l -dot- com -dot- 

To unsubscribe send a blank email to 
techwr-l-unsubscribe -at- lists -dot- techwr-l -dot- com
or visit http://lists.techwr-l.com/mailman/options/techwr-l/archive%40web.techwr-l.com


To subscribe, send a blank email to techwr-l-join -at- lists -dot- techwr-l -dot- com

Send administrative questions to admin -at- techwr-l -dot- com -dot-  Visit
http://www.techwr-l.com/ for more resources and info. 

Please move off-topic discussions to the Chat list, at:
http://lists.techwr-l.com/mailman/listinfo/techwr-l-chat