Multiple choice DB and queries 1

[Kikk0]

Hello everyone, I am trying to develop a DB in Access 2003 with the following structure:

[USERS] = A table containing a finite number of usernames
[SKILL] = A table containing a finite number of skills
[TIME] = A table containing time periods in 10 mins. increments

[MAINTBL] = A table that contains these fields:
(USERNAME - Linked to USERS)
(SKILL1 - Linked to SKILL)
(TIME1 - Linked to TIME)
(SKILL2 - Linked to SKILL)
(TIME2 - Linked to TIME)...
... (SKILL5 - Linked to SKILL) and (TIME5 - Linked to TIME)
(ISSUE - A descriptive field for the user to fill)
so the user can select for each (ISSUE) up to five (SKILL) and for each (SKILL) select the (TIME)

{I hope I've been clear up until now}

My dilemma is that I cannot seem to create a query that will display, given the (USERNAME), which (SKILLS) and how much (TIME) per skill the specific user used.

I will attach a sample DB if necessary


Thanks in advance for all your help.

 

[lespaul]

first off you should read Fundamentals of Relational database Design. Second, you should change your table structure based on the information you learn about in the linked document. Having fields named Something1, Something2 is a red flag that your tables aren't in the correct form.

What exactly are you trying to do with this database? What is the "time" all about?



Leslie

Have you met Hardy Heron?

 

[Kikk0]

Hi Leslie,

thank you for the pointers.
The DB will have to display how much time every user is spending on which skills (the issue on which they are spent is not important).

Please check the attachment with the sample DB (I'm new at these) and tell me if the structure needs to be modified.

 

[lespaul]

I don't have Access on this machine so I can't look sorry, you'd have to post the details.

Is this work tracking? skills testing? project management? what exactly are the skills and why do you care how much time was spent on them?

Leslie

Have you met Hardy Heron?

 

[Kikk0]

Hi Leslie,
it is necessary for the organization I work for to keep track of all the skills (or Core Competencies) and the time spent on each of them as there are minimum requirements to be met for the employees to be certified (each skill has to be used by a minimum amount of time, i.e. SKILL1 has to be used for at least 4 hours within a year and so on).
As far as the details go I guess what;s in my first post stays: three tables are linked to a main table through a 1 to many relationship.

Hope this helps.

 

[Kikk0]

DUH... Never thought of using a UNION SELECT... all I had to do was:

create 5 select queries (username, SkillX and TimeX)
then create a query that would UNION the 5 into three fields... at that point I can sum the minutes, group the skills and group the usernames

SELECT Username, Skill1, Time1
FROM Q1
UNION
SELECT Username, Skill2, Time2
FROM Q2
UNION
SELECT Username, Skill3, Time3
FROM Q3
UNION
SELECT Username, Skill4, Time4
FROM Q4
UNION
SELECT Username, Skill5, Time5
FROM Q5

And it's done... thanks again for the help Leslie.

 

[lespaul]

So you'll need the People, Skills, PeopleSkills and Competency Tables.

People will list the people and Skills will list the skills.
PeopleSkills will tie People to Skills:

[tt]
People
ID Name OtherPeoleField
600 Leslie

Skills
ID Type YrlyMinimumCompetencyHrs
100 DBA 2
101 Delphi Programming 20
102 Typing 100

PeopleSkills
ID PeopleID SkillID
301 600 100
302 600 101

Competency
ID PeopleSkillID DateRecorded TimeSpent (in mins)
501 301 2/15/2008 100
502 302 2/18/2008 200
[/tt]

so now I have two skills (DBA and Delphi Programming) and I have recorded 100 minutes on the DBA and 200 minutes on Delphi Programming. You can now report by year since you track the date the time was spent.

That's how I would do it.

Leslie

 

[Kikk0]

Thanks Leslie, will try to structure my DB as you suggested and will keep you posted.