You should try and think of how a relational database stores this sort of
data rather than how you'd represent it on a paper form. Relational
databases store data in only one way, as values at column positions in rows
in tables. If you have a separate column in a table for every type of work
experience you are departing from this fundamental principle. Its what's
known as 'encoding data as column headings'.
The way a relational database works is to represent each 'entity' type by a
table, whose columns represent attributes of that entity type. So an
Employees table will have columns EmployeeID, FirstName, LastName etc.
Another important principle is that tables contain no 'redundancy', i.e. each
fact is stated once and once only. This is achieved by having separate
tables related to each other by means of a foreign key column in one
referencing the primary key column in another. So a Cities table might be
related to a States table, and an Addresses table to the Cities table, but
the Addresses table would not include a State column as once you know the
City you know the State via the relationships. To be told 20 times in an
Addresses table that San Francisco is in California would be redundancy, to
be told it only once in the Cities table is not.
With your scenario you already have an Employees table, which presumably has
a primary key column such as EmployeeID. If not add an autonumber column as
the primary key.
For work experience you have two entity types, ExperienceCategories
(Industrial, Commercial etc), and WorkExperiences, each of which will be in
one experience category. So you need tables of those names, each with its
own primary key column, which again can be an autonumber, and in
WorkExperiences a foreign key column, ExperienceCatgoryID, which references
the primary key column of the same name in ExperienceCategories.
You now have to relate Employees to WorkExperiences. What you have here is
a many-to-many relationship type as each employee can have one or more work
experience, and each work experience can apply to one or more employees. A
many-to-many relationship type is not created directly between the two tables
but via a third table, EmployeeExperiences. This would have two foreign key
columns, EmployeeID and WorkExperienceID referencing the keys of the other
two tables respectively. The primary key of this table would be a composite
one made up of the EmployeeID and WorkExperienceID columns, which in
combination must be unique within then table. The EmployeeExperiences table
might also have other columns representing attributes of each employee's
particular work experience, e.g. the dates between which they had that
particular experience. Its for you to judge what attributes are needed for
this table, however.
For data entry you'd normally have an Employees form and within it a subform
based on the EmployeeExperiences table. Entering each work experience for an
employee is then simply a case of inserting a new record in the subform,
selecting from a list of work experiences in a combo box. The experience
category would be shown in an unbound control, but you can select a category
first, then select an experience from a combo box listing only those
experiences for the category in question. You'll find a demo of ways of
doing this at:
http://community.netscape.com/n/pfx...yMessages&tsn=1&tid=23626&webtag=ws-msdevapps
It uses the local administrative areas of County, District and Parish in my
area, but the principles are exactly the same.
When it comes to querying for employees with particular experiences it is a
simple task to find all employees with one particular work experience such as
forklift driver as you'd just join the tables in a query and put a criterion
on the relevant WorkExperienceID column. Querying for employees with several
different work experiences is more complex. In essence it involves seeing
how many rows for a particular employee exist in EmployeeExperiences where
the experience is any of those required, and then seeing if the number of
rows equals the number of experiences required. The following query for
example will return those employees who have all of experiences 3, 42 and 99:
SELECT EmployeeID
FROM EmployeeExperiences
WHERE WorkExperienceID IN(3, 42, 99)
GROUP BY EmployeeID
HAVING COUNT(*) =
(SELECT COUNT(*)
FROM WorkExperiences
WHERE WorkExperienceID IN(3, 42, 99));
In reality you would allow for the user to input the list of required
experiences at runtime rather than hard coding them in the query.
You say you are very new to Access, so I imagine that you are going to find
some of the above difficult to take in at this stage. As the other
respondent has said, you need to consider whether you and your friend are
prepared to invest time and effort in getting to a level where you are able
to implement the sort of solutions which an application of this nature
demands. Its by no means a trivial task.
Ken Sheridan
Stafford, England
