Database design problem

[dmcintyre]

Apologies for the cross posting, as I have only just found this newsgroup.

I have to design a databse that will allow multiple search criteria as
below.

Name
Region
Skill_1
Skill_2
Etc
Now for the hard part, of the 300 or so names, 20 regions and dozen
skill types, I want to return as an answer from a query the top X percent
closest to the client request.
To eplain further, I may have 2 names with the right region and all
skill sets and 2 with the right skills but not in the selected region.

The end result will allow the database user to find the most suitably
qualified available person even though all skills may not be available
(closest match to ideal)

Thanks in advance, Don

 

[Jeff Boyce]

Don

I'm not entirely clear from your description what the relationship is among
Name, Region, and Skill. (By the way, Access treats "Name" as a reserved
word -- you will cause yourself & Access headaches if you use that as a
field name.)

Before you can query, you need to have data -- and with a relational
database (Access), you need to define the relationships (i.e., one-one,
one-many, many-many). OK, OK, so you don't NEED to, but if you don't bother
to, you might as well use a spreadsheet!

Please provide more info...

Jeff Boyce
<Access MVP>

 

[dmcintyre]

Sorry for the ambiguity Jeff, and thanks for a prompt response, on
re-reading I see what you mean. It was my poor attempt at simplification of
the problem.
The tables consist of:
tblConsultant
txtConsName 'eg Fred Flintstone

tblRegion
txtRegion ' eg East, West etc

tblSkillSet
intSkill1, ' this is a score of 1 to 3
intSkill2
etc.

Each Consultant has a possibility of 10 skills maximum, he may have 1 or
more of those skills. Each skill will be rated between 1 and 3
Each Consultant may serve more than 1 region.

A typical entry may be:
Fred Smith , Skill1(2),Skill2(3),Skill4(4), East,West,South

The end game would be to find the most suitably qualified Consultant(s) with
the maximum score in eack skill and /or availability to work in the region
required.
In addition to list/display the next X% that may have all or some of the
skills and region. The reason for this is to ensure that distribution of
work is done fairly. If this part was omitted then all Consultants begining
with the letter A would get the majority of the work. This query would
enable the Admin to make a judgement based on factors that are not
quantifiable in a database.

The admin will make the selection based on an ideal criteria through combo
boxes based on the work available. In most cases compromises will be made
as it is unlikely that any individual will match the ideal criteria. A
simple addition of the Skill score will not work as it will be weighted
according to the Job eg Skill 1 I would like a 3 but may have to settle for
a 2 because the Region is not available for those Consultants listed as a 3.

 

[TC]

Apologies for the cross posting, as I have only just found this newsgroup.

(snip)

Don, cross-posting is fine. But I suspect that you have "multi"-posted
(which is not so fine!)

To cross-post, put all the newsgroups into the "Newsgroups" line of your
post. Seperate them with semicolons, or whatever other character your
newsgroup software requires. Then send the post >once<. This "cross posts"
the message to all those newsgroups simultaneously. The key benefit of
cross-posting, is that each of those newsgroups will see the replies that
were given in each & every other newsgroup. This makes the whole process
efficient & easy for all concerned.

MULTI-posting is where you post the message to one newsgroup, then again to
the next one, & so on. The problem with multi-posting is that the readers of
your post in one newsgroup, >will not< see any replies that you got in the
other newsgroups. So someone might spend time to answer a post, when it has
already been answered somewhere else. I suspect that you have inadvertantly
multi-posted, instead of cross-posting.

Hope this information helps

TC

 

[Jeff Boyce]

Don

If I read your description correctly:

A typical entry may be:
Fred Smith , Skill1(2),Skill2(3),Skill4(4), East,West,South

you have a spreadsheet, not a relational database. You hadn't included
these additional fields in your description of the tblConsultant, but you
also didn't include a description of a "junction" or "relation" table.

Since one consultant can have zero-to-many skills, and for any skill
possessed, a rating from 1-3 (so how come Fred has a 4 on Skill4?), and each
consultant can serve ?zero-to-many regions, to get the features and ease of
use out of Access, you need to build tables that show how these are related.

You know your data, I don't, so this is just a guess:

trelConsultantSkill
ConsultantSkillID (probably an Autonumber)
ConsultantID (a foreign key, from tblConsultant)
SkillID (a foreign key, from a NEW tblSkill, that lists skills)
SkillLevel (your 1-3 rating)

trelConsultantRegion
ConsultantRegionID (Autonumber)
ConsultantID (a foreign key, from tblConsultant)
RegionID (a foreign key, from tblRegion that lists regions)

Note that I've not drawn any relationship between skill and region with this
design. You're the one who knows, though.

Also note that a given Consultant will only have as many trelConsultantSkill
rows as s/he has skills -- no "blank" columns or rows. Ditto for Regions.

Or have I still misunderstood?

Good luck

Jeff Boyce
<Access MVP>

 

[dmcintyre]

Still struggling Jeff, this should be straightforward and I'm still not
getting it. Your assumption are right though.

So far, I have 4 tables linked as follows.
tbleConsultantName
txtConsultantName
PKConsultantName (PrimaryKey Autonumber)

tblRegion
txtRegion
PKRegion (PrimaryKey Autonumber)

tblConsultantSkill
txtConsultantSkill
ConultantSkillID (PrimaryKet Autonumber)
ConsultantID
SkillID
SkillLevel

tblConsultantRegion
ConsultantID
RegionID

Joins as follows:
[tblConsultantName] PKConsultantName 1 to Many with
[tblConsultantSkill]ConsultantID
[tblConsultantName] PKConsultantName 1 to Many with
[tblConsultantRegion]ConsultantID
[tblregion]PKRegion 1 to Many with [tblConsultantRegion]RegionID

I then created a query [qryAll] with
ConsultantName,ConsultantSkill,SkillLevel,Region
From this query [qryAll] I created a form [frmAll] to enter data to test
relationships.
The form came up blank, ie No fields visible to enter data.

I must be missing something obvious and appreciate all of your help.

Don

 

[Jeff Boyce]

Don

If you do have a one-to-many relationship between Consultant and Region, I
don't think a (single) form would let you show that (i.e., one Consultant,
many Regions). Similarly, one Consultant, many Skills doesn't translate to
a single form.

So my assumption/direction would be that a main form/subform relationship
would need to be established, and a query for each subform (I see at least
two in the relationships above).

Now, how 'bout that Consultant, Skill, Region connection? You said I was
correct in my assumption that there's no relationship among the three, but
two one-to-many relationships from Consultant to Region and Consultant to
Skill. It doesn't surprise me that your form can't hold data from the three
all jammed together.

Perhaps you are thinking about it in terms of what you want to see, not
(quite yet) what you have. Try building two forms, one for the
Consultant-Region (based on a query using the ConsultantRegion table joined
to Region), one for the Consultant-Skill (based on a query using
ConsultantSkill joined to Skill). Then, build a form (?and maybe a query)
that only looks at the Consultant table -- this is your "main" form. Add
the two previous forms as subforms. When prompted, or in the properties,
the common field between main (parent) and sub (child) forms is the
ConsultantID.

Good Luck!

Jeff Boyce
<Access MVP>

 

[dmcintyre]

Many thanks Jeff, It does give me a new angle on things. I'll try it
tomorrow and let you know how I get on