Skip to main content
L
Luke10253
Inspiring
February 20, 2009
Question

designing a query and displaying the query output

  • February 20, 2009
  • 5 replies
  • 875 views
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
    This topic has been closed for replies.

    5 replies

    L
    Luke10253Author
    Inspiring
    February 23, 2009
    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.
    L
    Luke10253Author
    Inspiring
    February 22, 2009
    The table above didn't display properly; Job 1, Job 2 and Job 3 should all be moved over 1 column to the right.
    T
    Tariq_Ahmed__ACP_
    Participating Frequently
    February 22, 2009
    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

    L
    Luke10253Author
    Inspiring
    February 22, 2009
    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.
    D
    Dan_Bracuk
    Inspiring
    February 23, 2009
    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?
    L
    Luke10253Author
    Inspiring
    February 20, 2009
    I will look into arrays - thank you!
    L
    Luke10253Author
    Inspiring
    February 21, 2009
    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
    D
    Dan_Bracuk
    Inspiring
    February 22, 2009
    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?
    D
    Dan_Bracuk
    Inspiring
    February 20, 2009
    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.
    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices