Initial Check Box Setup

[Mike C.]

Hello.

I am in the process of designing a database that, in
addition to having the members name, address, etc., will
have a group of check boxes. The check boxes will allow
the inputer to associate a number of different expertises
to the member. It will look as follows:

Member Name Expertise 1 Expertise 2 Expertise 3
-----------------------------------------------------
John Doe x x
Jane Doe x x
Scooby Doo x x x

How do I go about intially setting up the table(s) for a
database design such as this one?

Any help would be appreicated.

Thanks in advance,

m.

 

[Cheryl Fischer]

Mike,

From the very brief description you provided, I would say you are starting
off with a very un-normalized design which will do nothing but cause you
grief down the road. With what you have: a single table containing an
employee name with columns representing some number of individual skills,
you can design forms, queries and reports. But what happens when it's
determined that an additional skill must be added to those that are already
in the table? Not only will your table have to be modified, but so will
the queries and forms that you use. And, that is only the beginning...

What you should be looking at is:

Table: tblEmployees
PK - Autonumber
LastName
FirstName
<other fields directly relating to employee.

Table: tblEmpSkills
PK - Autonumber
EmpID (Long Integer - links to PK in tblEmployees
Skill
<additional field for Skill Level ??>

I strongly recommend that you take a look at the following two links which
discuss normalization - the second link shows an example which bears some
resemblance to your current table.

http://databases.about.com/library/weekly/aa080501a.htm
and
http://databases.about.com/library/weekly/aa081901a.htm

In addition, here is an Amazon link to one of the better books on database
design: "Database Design for Mere Mortals", Hernandez
http://tinyurl.com/2uona

 

[Mike C.]

Thanks for the response.

Unfortunetly, I am still having some problems. I still am
not sure on how to set up the tables and then set up the
form with the actual check boxes on them.

Any further assistance would be greatly appreicated.

m.

 

[Cheryl Fischer]

The beauty and utility of a normalized design is that you can have an
unlimited number of skills assigned to each employee; this, therefore,
renders having a fixed number of checkbox controls on a form (subject to
redesign whenever a new required skill is added) unnecessary.

Take a look at the Northwind.MDB database which ships as a sample with
Access. Open the Orders form. Notice that for each order there can be
multiple line items in the order. This is a parent-child relationship,
similar to the relationship that would exist between a single
employee/person in your database and that person's multiple skills. Notice
that you can add new line items to an order by using a combo box to enter
the product name, thereby making data entry more accurate and fast.

It is very easy to look at the rich design environment of an Access form and
begin picking out the controls one wants to use before creating
appropriately normalized tables and defining their relationships, putting
the cart before the horse (so to speak). Once you have designed each of
the tables your database needs and made sure that the data is normalized,
you'll find that the forms generally "design themselves", as Access forms
and controls are designed to work their best with normalized data.

 

[TC]

Mike, you are heading for "multo problemos" if you are even >thinking< about
your forms at this stage!

The first stage in developing a competent, working database is to design the
table structures using the principles of database normalization. That stage

is not concerned< with user-interface things like forms or reports. If you

design a properly normalized database, you will be able to write whatever
forms & reports you may want - >later<!

Start here for a dry, but instructive read:
http://support.microsoft.com/support/kb/articles/Q100139.ASP

HTH,
TC