tblDates would have a single field [datDate] with every possible date you
will ever be interested in.
--
Duane Hookom
Microsoft Access MVP
:
ok im trying it your way, Ive created the tables.
tblAbsentPeople
===================
abpAbPID primary key autonumber
abpPeoID foreign key to tblPeople.peoPeoID
abpDate date they will be gone
abpReason reason for absense
What does ABP? mean, ive created them as fields.
Also with the 'Date Table' Do i do the fields as Sunday ,Monday etc... then
fill the data in as teh 01/01/2008 for the corresponding day?
Il prob need some more help when comming to the Query as well.... soz for
the lack of personal knowledge
:
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
:
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.
:
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
:
Im tryin to create a database where if i download the sheet 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
?
