Re: Looking for a database function

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
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


References:
Looking for a database function: From: Dan Goldstein
Re: Looking for a database function: From: Ned Bedinger

Previous by Author: Re: Lists of Shipped Components
Next by Author: Re: Not an acronymn....
Previous by Thread: Re: Looking for a database function
Next by Thread: RE: Looking for a database function


What this post helpful? Share it with friends and colleagues:

Sponsored Ads


Sponsored Ads