designing a query and displaying the query output

[Luke10253]

The goal is to create a report (in HTML form) of all the jobs (and job-related information) for a given department.

I envision the department name as a hyperlink to a report based on the unique DepartmentID.

For example, the “Sales” department might contain these jobs:
VP of Sales
Associate Salesperson
Sales Assistant

Each job requires a set of skills and a specific skill level.

“Persuasion skill” could vary as follows:

VP of Sales: ADVANCED persuasion skill
Associate Salesperson: MODERATE persuasion skill
Sales Assistant: BASIC persuasion skill

“Communication skill” could vary as follows:

VP of Sales: EXPERT communication skill
Associate Salesperson: ADVANCED communication skill
Sales Assistant: NOT REQUIRED

And so on…

Each job has roughly 20 skills that are related to it.

I’d like to display this information in a table with the skills listed in rows, the job titles listed in column headings, and the skill level (e.g., ADVANCED) located in the appropriate cell where the skill and job title intersect. That way, the reader can easily compare skill levels across jobs.

The requisite tables are below. (A field name preceded by “frn_” is the foreign key related to another table’s primary key.)

Table: Departments
Fields: DepartmentID, DepartmentName

Table: Jobs
Fields: JobID, JobTitle, frn_DepartmentID

Table: Skills
Fields: SkillID, SkillName

Table: Skill_Levels
Fields: Skill_LevelID, Skill_Level_Description

Table: JobSkills (this table “links” a job, skill, and skill level)
Fields: JobSkillID, frn_JobID, frn_SkillID, frn_Skill_LevelID

I'm not sure how to do this, so thank you for any help!

Luke

[Dan_Bracuk]

It's called a cross tab.

If you are smarter than me, and your db supports it, you might be able to write a query that gets your data in the format you need it. Otherwise,

run your query.
re-arrange it into a 2D array
output the array.

[Luke10253]

I will look into arrays - thank you!

[Luke10253]

The code below shows the simple array that I made (based on a Forta book).

I want the array to output a skill only once and to output a job only once. The proficiency rating for each skill is different for each job, though.

Predictably, the array below outputs the skill as many times as there are jobs, so lots of redundant data is displayed.

Thank you for any tips or suggestions about how to go about this or where to look for more information.

Luke

[Dan_Bracuk]

quote:

---

Originally posted by: Luke10253
The code below shows the simple array that I made (based on a Forta book).

I want the array to output a skill only once and to output a job only once. The proficiency rating for each skill is different for each job, though.

Predictably, the array below outputs the skill as many times as there are jobs, so lots of redundant data is displayed.

Thank you for any tips or suggestions about how to go about this or where to look for more information.

Luke

---

Going back to this,
Persuasion skill” could vary as follows:

VP of Sales: ADVANCED persuasion skill
Associate Salesperson: MODERATE persuasion skill
Sales Assistant: BASIC persuasion skill

How exactly is that stored?

Also, what does your query took like now?

[Luke10253]

“VP of Sales” is stored as text in Table: JobTypes, Field: JobTypeName

Instead of a word, i.e., “ADVANCED,” proficiency ratings will be displayed as numbers based on a rating scale from 1 to 5. They are stored as text in Table: KSAProfRatings, Field: PRRating

“persuasion skill” is stored as text in Table: KSAs, Field: KSAStatement

Table: Jobs contains the foreign keys linking to Table: Departments and Table: JobTypes.

Table: JobKSAs is a linking table that contains the foreign keys to Table: Jobs, Table: KSAs and Table: KSAProfRatings.

The query is below.

[Tariq_Ahmed__ACP_]

So what kind of display are you looking for? Something like:

SKILL FOO
_____JOB X____ SKILL LEVEL: 22
_____JOB Y____ SKILL LEVEL: 29
SKILL BAR
_____JOB X____ SKILL LEVEL: 22
_____JOB Z____ SKILL LEVEL: 34

[Luke10253]

I'd like the display to be a table with dynamically generated rows and columns, depending on the number of skills and jobs for the given department.

[Dan_Bracuk]

quote:

---

Originally posted by: Luke10253
I'd like the display to be a table with dynamically generated rows and columns, depending on the number of skills and jobs for the given department.

---

That's not quite the same as your original description. I thought you wanted some thing like

job/skill job1 job2 etc
skill1 basic moderate
skill2 expert advanced

In your query, you are ordering by ksastatement. Does that represent a skill?

[Luke10253]

The table above didn't display properly; Job 1, Job 2 and Job 3 should all be moved over 1 column to the right.

[Luke10253]

You're correct; I want to display the data as you described, with one slight change.

Instead of the words "expert" or "basic," the proficiency ratings should be displayed as numbers, e.g., "1" for "basic, "3" for moderate, and "5" for expert. (Table: KSAProfRatings contains fields for both, i.e, Field: PRStatement for "basic", "moderate", "expert" etc. and Field: PRRating for "1", "2", "3", etc. I want to use Field: PRRating)

Table: KSAs contains Field: KSAStatement, which contains the skills. In addition to skills, Field: KSAStatement also contains knowledges and abilities.

So, in addition to the skills that I mentioned above, there might be a knowledge, such as "Knowledge of company products & services," and an ability, such as "Ability to communicate effectively." These knowledges and abilities receive a proficiency rating just like the skills.

There is conceptual overlap among knowledges, skills, and abilities (KSAs), so I had referred only to "skills" for clarity.

The query that I posted reflects the actual table names, e.g., Table: KSAs instead of Table: Skills, and Table: KSAProfRatings instead of Table: Skill_Levels (see my first post), but the principle is the same.