PLEASE HELP ME!

[Rhian]

I am trying to put together a consultant database which basically stores all
the information about our consultant including, skills they have.

As you can imagine, the majority of the them have more than one skill and
so I was wondering how I would input all skills they have, into one cell
space. I have created a combo box so that people can select skills already
specified but obviously i can only select one skill.

Would I do this by creating another table? If so how I would i create the
relationship?

If someone could help i would be so grateful as this has taken me ages to
try and figure out and i still have no clue!

As you can read im pretty clueless so I would be grateful for any
suggestions and help in the simplest terms possible.

 

[Jim Ory]

Rhian,

Consider this:

Your first table should be "Consultants" and your second table should be
"AppliedSkills". (Name them however you'd like of course. But avoid using
spaces in names. Spaces create more problems than they are worth.)

Consultants table: (Any number of fields)

ConsultantID
ConLastName
ConFirstName
ConCompanyName
ConPhoneNumber
Con_Etc.

AppliedSkills table: (Two fields)

ConsultantID
Skill

The tables are joined using "consultantID"

You really don't want to put all their skills into one field. You would find
it quite difficult to try and find all the consultants that are Access pros
if they are lumped in with Electrical Engineers and other specialties.

Make a third table consisting of just one field. Let's call it "Skills".
This table will become the basis for a combo box or list box. Enter all the
'skills' in this table. When someone adds a 'skill' to a consultant in the
"AppliedSkills" table, they select from the combo/list box. The spelling will
remain the same for each skill. As you add more skills to the table, the
combo box will pick it up for use the next time.

I know I said "When someone adds a 'skill' to a consultant in the
"AppliedSkills" table" that doesn't mean to directly modify the table. That
usually should only be done through forms, queries, other access objects.

 

[TC]

Rhian, you have replied to /my/ message, but you are actually talking
to Jim.

That's a bit like looking directly at Tom, whilst actually talking to
Mary!

To avoid confusion, please reply to the correct message. If you don't
know how to do that, it will probably be in your newsreader program's
F1 help.

I'm not "having a go" at you here. I'm just trying to help you use the
newsgroups more effectively.

HTH,
TC (MVP Access)
http://tc2.atspace.com

 

[Grover Park George]

PMFJI:

You are making a big mistake by going with the "easier" table design, the
one with four Skill fields. This is a common error made by new Access users
.. This is what is usually referred to as a Repeating Group (Skill1, Skill2,
etc.) and will be the source of multiple problems down the road, not just
now.

You need THREE TABLES, not two. These tables are:

1. A Roster table of consultants

tblConsultant--the demographics of your consultants: name, DOB, etc.
==========
pkConsultantID -- Primary Key -- use an autonumber
ConsultantFirstName
ConsultantLastName
ConsultantDateofBirth
--other fields as requred

2. A Lookup table of Skills

tblSkill--the skills which you expect consultants to have
==========
pkSkillID -- Primary Key --use an autonumber
Skill

3. A Junction table to relate Consultants to Skills

tblConsultantSkill
==========
pkConsultantSkillID -- Primary Key --use an auntonumber
fkConsultantID -- Foreign Key from tblConsultant
fkSkillID -- Foreign Key from tblConsultant
SkillAcquiredDate -- Date this consultant was certified to have this skil


You need this table design because you have a many-to-many relationship
between skills and consultants. One consultant has many skills, one skill
can be acquired by many consultants.

HTH

George

Jim,

You are an angel!!

You have solved my problem of the day!
(I had to go with the second option because i didnt quite understand the
easier option. But it doenst matter because the only changes which are
going
to be made is when adding new people not updating records already
entered.)

My next move now is to use a combo box in a query. Im pretty sure i know
how
to do this but i think it may be a bit to complex....what do you think? is
it
possible to do it?

I appreciate that you proberly have a million better things to do with
your
time and i want you to know that I really do appreciate all your help.

Thanks
Rhian

I believe you have misunderstood how the tables are designed. There is
only
one "Skill" field in the "AppliedSkills" table not four fields. Although
you
can design your tables with 4 fields for skills in one table, you've
limited
yourself to adding more skills in the future. (One of my many mistakes in
designing tables.)

First, if you are using the two tables I've described earlier: (Preferred
method)

Create your select query using both tables, joined by "ConsultantID".

Perhaps you would add from the Consultants table:

ConsultantID
ConNameLast
ConNameFirst
ConCompany

And from the AppliedSkills table:

Skill

In the criteria box enter the following between the quotes: "[Please
Enter
Skill You Require]" (You can change the wording between the brackets to
suite
your taste.) When you run the query, a dialog box will appear. Type in
name
of the skill you are looking for, i.e. 'Access Pro' (if that is one of
your
skills in your "Skill table"). A list of consultants should appear that
have
that skill applied.

Now, on to your single table with four skill fields: (Not recommended
method)

Your query design is to add all fields of your table to the query. In the
criteria box under Field1, type in " Like "*" & [Enter Skill Required Or
Leave Blank For All] & "*"". Go to Field2, drop down one line, type in "
Like
"*" & [Enter Skill Required Or Leave Blank For All] & "*"". Go to Field3,
drop down one more line, type in " Like "*" & [Enter Skill Required Or
Leave
Blank For All] & "*"". Go to Field4, drop down another line, type " Like
"*"
& [Enter Skill Required Or Leave Blank For All] & "*"".

When you run this query, a dialog box will appear, type in the 'skill',
click on OK. All skill fields and name of consultant, etc. will list if
one
of those fields contains your search criteria.

--
Jim Ory

Hi Jim,

Thanks so much for getting back to me, you are a lifesaver!

I have another question though......

Now that I have created these tables how am i able to run a query which
searches for all the skills. For each consultant I have a maximum of 4
skills
and I want to search for any of the skills which could be in any field
in one
query so that it will display any people with that skills. Any of the
skills
can appear in any box....so for example.....one person may have a
planning
skill in skill field1 and another person may have a planning skill in a
different skill field2.
What would I have to input into the criteria box to ensure that it
searches
all the fields?

I would be grateful if you could supply me further with your great
Access
knowledge!!!

Many thanks!

:

Rhian,

Consider this:

Your first table should be "Consultants" and your second table should
be
"AppliedSkills". (Name them however you'd like of course. But avoid
using
spaces in names. Spaces create more problems than they are worth.)

Consultants table: (Any number of fields)

ConsultantID
ConLastName
ConFirstName
ConCompanyName
ConPhoneNumber
Con_Etc.

AppliedSkills table: (Two fields)

ConsultantID
Skill

The tables are joined using "consultantID"

You really don't want to put all their skills into one field. You
would find
it quite difficult to try and find all the consultants that are
Access pros
if they are lumped in with Electrical Engineers and other
specialties.

Make a third table consisting of just one field. Let's call it
"Skills".
This table will become the basis for a combo box or list box. Enter
all the
'skills' in this table. When someone adds a 'skill' to a consultant
in the
"AppliedSkills" table, they select from the combo/list box. The
spelling will
remain the same for each skill. As you add more skills to the table,
the
combo box will pick it up for use the next time.

I know I said "When someone adds a 'skill' to a consultant in the
"AppliedSkills" table" that doesn't mean to directly modify the
table. That
usually should only be done through forms, queries, other access
objects.
--
Jim Ory


:

I am trying to put together a consultant database which basically
stores all
the information about our consultant including, skills they have.

As you can imagine, the majority of the them have more than one
skill and
so I was wondering how I would input all skills they have, into one
cell
space. I have created a combo box so that people can select skills
already
specified but obviously i can only select one skill.

Would I do this by creating another table? If so how I would i
create the
relationship?

If someone could help i would be so grateful as this has taken me
ages to
try and figure out and i still have no clue!

As you can read im pretty clueless so I would be grateful for any
suggestions and help in the simplest terms possible.