Colums/Rows Link?

  • Thread starter Thread starter sunilkes
  • Start date Start date
S

sunilkes

Hi

I am working on a database which would contain a skill matrix. So, if I
have Names as records, and the skill types as columns, then each name
would have a rating against each skill.

The problem here is, I want the users to be able to add new skills as
and when they are identified...But the skills are in the columns, how
do I get to do this? Maybe another table and link it?

Am not sure, I ve been trying this for a while.

Thanks
 
The approach you described is exactly what you'd need to use ... if you were
limited to using a spreadsheet.

The problem you've identified is exactly the reason you'd want to use a
relational database like Access ... but you have to rethink the "how" (how
to design the table).

In a well-normalized database, you'd use three tables:

tblPerson (contains the person-related info like firstname, lastname,
DOB)
PersonID
FirstName
...

tblSkill (one row per skill)
SkillID
SkillTitle
SkillDescription
?SkillCategory
...

trelPersonSkill (the valid combinations of persons and skills)
PersonSkillID
PersonID (from tblPerson)
SkillID (from tblSkill)
?DateSkillAcquired (or Rated or Measured or ...)
... (any other facts related only to Person/Skill combination)

Note that this design allows you to add skills by merely adding rows to the
tblSkill.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/
 
Thanks Jeff,

I tried this and also looked at the Nwind.mdb sample. I don't want to
create multiple records of a particular name on the skills table.

Its like, if I have a name - John, it should appear only in one record,
and there need to be about 50 columns detailing skills on the column
headers, and the content will be a score.

I want the users to be able to add new rows(User names) and columns
(skill type)

Can I link a table in such a way that each table record becomes a field
in another table?

Sorry if I am not getting this right...
 
Jeff is absolutely correct regarding his table structure. You can use a
crosstab query to display your matrix. There are a number of good resources
on how to display crosstabs in forms and reports.
 
As Duane points out, how you DISPLAY the data (e.g., in a crosstab report)
will not limit how you STORE the data. But you can create table structures
in Access that mimic a spreadsheet and will ABSOLUTELY limit how you can
subsequently use Access' features and functions.

If you believe you need a spreadsheet structure for your data, why not use a
spreadsheet?

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/
 
Thanks Jeff,

I tried this and also looked at the Nwind.mdb sample. I don't want to
create multiple records of a particular name on the skills table.
Click to expand...

You won't, if you follow Jeff's accurate and correct advice.
Its like, if I have a name - John, it should appear only in one record,
and there need to be about 50 columns detailing skills on the column
headers, and the content will be a score.
Click to expand...

The data should be STORED in a normalized manner... and DISPLAYED
(using a Crosstab) in the wide-flat manner. They're different
requirements and use different solutions.
I want the users to be able to add new rows(User names) and columns
(skill type)
Click to expand...

If you use Jeff's solution, that's exactly what you can do. Add a new
user name as a new row in the people table; add a new skill as a new
row - NOT column!! - in the Skills table.
Can I link a table in such a way that each table record becomes a field
in another table?
Click to expand...

Yes. It's called a Crosstab Query. And it becomes a field in a dynamic
Query, not stored redundantly and non-normally in any Table.

John W. Vinson[MVP]
 
Thanks,

I will give this a try. This is exactly what I should do - a cross tab
query, am taking some time to understand it though.

Regards
 
Thanks for the inputs, I have been able to make the Crosstab query and
also have a form linked to the tblrelPersonSkill so the skills can be
updated.

There are 166 types of skills. Now, the problem is whwnever a new user
is added, each of the skills need to be assigned manually, which is
tedious. Can I have the form automatically assign all 166 skills on the
tblrelPerson skill for the new user, with a default value of 1?

Any help would be apreciated.

Thanks
Sunil
 
?Assign?

Are you saying that you want "empty" cells in a table? Don't do that!

A table that holds valid PersonSkills ONLY holds as many rows as that person
has (demonstrated) skills, not one per every POSSIBLE skill.

Again, trying to make a matrix in the table is a spreadsheetly way of
thinking ... you won't get the benefit of Access' strengths if you treat it
as a spreadsheet.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/
 
Jeff,

I am still wondering if there is something i can do. In my case all new
users added will qualify atleast a 1 rating by default.

Adding 166 skills is a very tedious job, and the skill ratings are
updated almost each day.

There should be some way by which, on addition of a new user on a form,
that user name gets recorded 166 times onto the table
tblrelPersonSkill, with a specific skill on each record against that
user, and each rating being 1, which can then be updated.

Should I use an INSERT INTO, or update query, am not quite sure how it
works and would like to know

Thanks
 
Yes, it can be done

From the form where you enter the new person, you can use a query like the
following that is called by a button or automatically in the after insert
event of the form.

Simplest method is to build this query and save it. Then in the afterinsert
event of the form check to make sure you have the person id field (should
always be there as a required field) and then execute the query.

INSERT INTO tRelPersonSkill (PersonID, SkillID, Rate)
SELECT FORMS![YourEntryForm]![ControlWithPersonID], tblSkill.SkillID, 1
FROM tblSkill

Private Sub Form_AfterInsert()
IF len(FORMS![YourEntryForm]![ControlWithPersonID] & "") > 0 then
docmd.OpenQuery "NameOfThequery"
End if
End sub


Now the problem with that is it can add duplicate records since nothing in
the Query prevents that. One way to fix that is to put an multifield-index
on the combination of PersonID and SkillID that won't allow duplicates.
 
Back
Top