Re: Microsoft Access Problems

Subject: Re: Microsoft Access Problems
From: Joy Brady <joy_m_brady -at- yahoo -dot- com>
To: Jazzmyne Richardson <jazzmyne11 -at- hotmail -dot- com>, TECHWR-L <techwr-l -at- lists -dot- raycomm -dot- com>
Date: Fri, 7 Jul 2000 09:25:36 -0700 (PDT)

Jazzmyne Richardson wrote:

> I was wondering if anyone had any experience using
> Microsoft Access 2000.
> One of the projects that I am working on requires me
> to create a database of
> personnel that includes all of their contact
> information and picture as well
> as the major projects that the person is working on.
> The trick is that the
> database has to have the ability to sort through the
> employees based on
> their projects, title or department name because it
> will be used
> internationally as a way to give the partner
> countries the ability to see
> who they will be working with in the future.
> Because many of the people
> have multiple project and thus multiple items in the
> project field, I have
> not been able to get the program to filter through
> the entire field.

You need to approach this as a database problem, not
an Access 2000 problem. For those of you not
interested, I warn you: the following is a super-quick
overview of how to normalize information and create a
quicky "relational" database using Jazzmyne's case.

I'll guess that your table is something like this
right now. Let's call it "Employees":
UniqueID Fname LName Title Projects
jbra1 Joy Brady Advice-Giver (empty)
jsmi1 Jim Smiley Receptionist CCC
jsmi2 Joe Smith Committee Guy CCC,PDC,PCCC

If you have, or can have, multiple entries in a given
field (such as Projects, in your case), it is good to
break that field out into two separate tables. One to
describe the projects themselves. One to link the
projects to the people. Here's a lookup, or
descriptive table - "Projects":
ProjAbbr ProjectName
CCC Committee-Creation Committee
PDC Process-Definition Committee
PCCC Process-Committee Creation

If Joe Smith is in on three different projects, he
should have three rows on the "ProjectMembers" table
(see jsmi2). You don't HAVE to break down project
names into an abbreviation and a full-text
description, but it's an example of the kind of thing
you can do. This table contains a row for every
project any individual is on:
UniqueID ProjAbbr
jsmi1 CCC
jsmi2 CCC
jsmi2 PDC
jsmi2 PCCC

Finally, the "Projects" field ("column") in the
"Employees" table CAN BE REMOVED. It is not needed
now. Make these three tables related to each other by
the fields("columns") they have in common. UniqueID is
in both "Employees" and "ProjectMembers." ProjAbbr is
in both "Projects" and "ProjectMembers." (Look in your
Access2000 help on how to explicitly relate the tables
- I don't have Access 2000 yet, so am not sure of your
menu selections.) When you query for all folks on the
Committee-Creation Committee, you'd be asking for the
first and last names of the people from the
"Employees" table who have the UniqueID that matches
UniqueID for each occurrence of CCC in the
"ProjectMembers" table. This would return Joe Smith
and Jim Smiley.

If you wanted the project name spelled out, you could
also ask for the ProjectName from "Projects" in your
query where it matched "CCC."

This posting of mine is more of a big hint than
instructions that you should follow to the letter.
Good database design will also greatly help the person
who designs your web page! You will catch everyone on
every project this way! Your Access Help will cover
this also.

Joy Brady
Technical Writer
Columbus, Ohio, USA

Do You Yahoo!?
Send instant messages & get email alerts with Yahoo! Messenger.

Previous by Author: RE: Word 97 problems
Next by Author: Re: Pray for me
Previous by Thread: RE: Microsoft Access Problems
Next by Thread: Réf. : Javascript Sites

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

Sponsored Ads