RE: Creating an DB for an Office Rota/Skills

Tech-Archive recommends: Speed Up your PC by fixing your registry

I have provided the generally accepted structure. If you create separate
fields for skills and/or days then you are on your own. I wouldn't do it this
way since you wouldn't be normalized.

--
Duane Hookom
Microsoft Access MVP


"Tobes" wrote:

Oh ok, well atm ive got 2 tables.

tbl1 Names
===============
People ID
Name
Then ive listed the skills with a tick box against there name.
E.G.
ID | Name| Skill A | Skill B | Skill C | Skill D
1 Toby X 0 X X

0 = not ticked
X = ticked

tbl2 - This is messy.....
============================
Monday 1| Tueday 1.......... Monday 2........Monday 3 etc
Person A Person A ........ Person C ........ Person C
Person B Person C

these represent the person of on what day.....

I managed to get a query working for these tables but only shows 1 day not
whole month. but it does show the peoples skills and the people left in the
office... just need it upgraded to show the whole month.

Do u think i should scrap mine? if so would there be an easier way to talk
to you about it as im am new to this access stuff.

"Duane Hookom" wrote:

Do you have any tables? I would suggest the following to get you started:

tblPeople
================
peoPeoID primary key autonumber
peoFirstName
peoLastName
peoStatus
peoComments

tblAbsentPeople
===================
abpAbPID primary key autonumber
abpPeoID foreign key to tblPeople.peoPeoID
abpDate date they will be gone
abpReason reason for absense

tblDates (one record for every possible date)
===================
datDate date/time field

Create a cartesian query that will display every person for every date like:
qcarPeopleDates
=====================
SELECT peoPeoID, datDate
FROM tblPeople, tblDates;

Then create a query of qcarPeopleDates and tblAbsentPeople. Set the joins
between the table to the date and peoID fields. Have the join display all
records from the qcarPeopleDates. Set the criteria under abpAbPID to:
Is Null
This will then display every person for every date that they will not be
absent.

For skills, create two more tables:

tblSkills (one record per skill)
==============
skiSkiID autonumber primary key
skiSkill skill title

tblPeopleSkills (one record per person per skill)
=================
pesPeSID autonumber primary key
pesPeoID foreign key to tblPeople.peoPeoID
pesSkiID foreign key to tblSkills.skiSkiID

--
Duane Hookom
Microsoft Access MVP


"Tobes" wrote:

Im tryin to create a database where if i download the *** with the data of
people absent from my office. Inputed that data into a table somehow. Then
how
could i make it show me the people in the office instead of thw people
absent. So something like subtract the names from a total list of all the
people in the office.

Then also create a skills section which gives each person in the office a
tick or X in a table with their skills in it. So then people could look at
this go, oh on monday 31st .. MR joe Bloggs is in and he is skilled in
....... and look what skills he has an X in...

Does this make any sense ? if so how would i go about doing it :P?

.

Quantcast