Creating an DB for an Office Tota/Skills

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 :P?

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 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 :P?

Already responded to in another newsgroup.

Please don't post the same item independently to multiple 'groups.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP
http://mvp.support.microsoft.com/

Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/


"Tobes" wrote in message
...
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 :P?

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 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 :P?

ye soz didnt no which 1 was best, put it in the other area 1st then saw this
area which i fort was more appropriate, with the information ive provided
about my tables etc, do you have an idea what im after?

"Jeff Boyce" wrote:

Already responded to in another newsgroup.

Please don't post the same item independently to multiple 'groups.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP
http://mvp.support.microsoft.com/

Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/


"Tobes" wrote in message
...
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 :P?

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 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 :P?

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 :P

"Duane Hookom" wrote:

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 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 :P?

I'm not clear what specific question(s) you might be asking, either here or
in the other newsgroup.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Tobes" wrote in message
news
ye soz didnt no which 1 was best, put it in the other area 1st then saw
this
area which i fort was more appropriate, with the information ive provided
about my tables etc, do you have an idea what im after?

"Jeff Boyce" wrote:

Already responded to in another newsgroup.

Please don't post the same item independently to multiple 'groups.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP
http://mvp.support.microsoft.com/

Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/


"Tobes" wrote in message
...
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 :P?

tblDates would have a single field [datDate] with every possible date you
will ever be interested in.
--
Duane Hookom
Microsoft Access MVP


"Tobes" wrote:

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 :P

"Duane Hookom" wrote:

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 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 :P?

In my office we have an excell kinda of spread sheet set up into a calander
that list the days and under each day it list the people out of office. I
would like to input this details into a database. then have someway of
converting it shows it shows a calander and list the people left in the
office. not the people out of office. Then with this info i would like to be
able to click on 1 day of the month to open further information which would
list the people left in the offfice and what skills they have ( what kinda
software they are trained on.

Does this make any sense?

"Jeff Boyce" wrote:

I'm not clear what specific question(s) you might be asking, either here or
in the other newsgroup.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Tobes" wrote in message
news
ye soz didnt no which 1 was best, put it in the other area 1st then saw
this
area which i fort was more appropriate, with the information ive provided
about my tables etc, do you have an idea what im after?

"Jeff Boyce" wrote:

Already responded to in another newsgroup.

Please don't post the same item independently to multiple 'groups.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP
http://mvp.support.microsoft.com/

Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/


"Tobes" wrote in message
...
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 :P?