Help request on database design

[Stuart Purvis]

To all you access Gurus

I have been asked to look at setting up and access database to do the
following

To keep a record of pupils and 9 tasks the pupils have to do and also the
total amount of time it took a pupil to do this task
They have a total of 25 Hours to do all tasks but this is spread out over
the year.

What I need is someone to tell me the best table layout to do this

The pupil record has to have the following
First Name
Surname
Form
Total Hours taken for all 9 Objectives
A description of what the pupil did to achive each Objectives
record if that task is complete
Also Date Task was Complete

I have done a test database with just one table in it and inside that table
had all of the above but apart from name, form and total hours I repeated
the rest nine times.
This just does not seem right

What I want is to pull up the pupil name and then select Task say from a
Drop down box this would then insert a new field if it did not allready exist
in pupil recored and then you can fill in the task details of course if the
field/s all ready existed then to open that up to allow you to update this

It would then need to update the total Hours field in the pupil record with
the hours it took to do that task/objective.


I have basic Access knowledge but i cannot think of best way to achive this
can anyone help please


Stuart Purvis
ICT TECHNICIAN
HILBRE HIGH SCHOOL

 

[Duane Hookom]

I would create at least the following tables:

tblStudents
================
stuStuID autonumber primary key
stuFirstName
stuLastName
stuTotalTime (25 hours, might be better in a separate table)
stu...

tblTasks
===============
tskTskID autonumber primary key
tskTitle title of task

tblStudentTask (9 records for each student)
================
sttStTID autonumber primary key
sttStuID Links to tblStudents.stuStuID
sttTskID Links to tblTasks.tskTskID
sttCompleteDate

tblActivity (for recording of time)
===============
actActID autonumber primary key
actStTID link to tblStudentTask.sttStTID
actDate date of activity
actMinutes number of minutes spent on activity
actComments description of what the pupil did

 

[Stuart Purvis]

Thanks for that
tblStudentTask (9 records for each student)

Any chance you can do a demo dbase so that i can see what you mean.

Thanks
Stuart

I understand most of it except for

 

[Stuart Purvis]

Any chance you can do a demo databse as i do not understand what you mean by

tblStudentTask (9 records for each student)

Stuart

 

[Stuart Purvis]

Any Chance you can do a demo database and email it me as when i not understand
what you mean by
tblStudentTask (9 records for each student)

thanks
Stuart

 

[Duane Hookom]

Each student has 9 tasks. tblStudentTask stores which student is assigned to
which tasks. This combination of student and task will have hours and
comments stored in the Activity table.

 

[Stuart Purvis]

I am thick Do the Database as instructed but cannot get it to work can i
email you what ihave done and see if you can see where i gone wrong.

All this help much appreciated and your work will be ack in database.

Stuart

 

[Duane Hookom]

Stuart,
"cannot get it to work" doesn't tell us much. You need to design and create
forms with subforms. Add a liberal amount of combo boxes.

To get you started, create a main form of just tblStudent. Then create a
continuous subform based on tblStudentTask with a combo box:
Name: cboTaskID
Control Source: sttTskID
Row Source: SELECT tskTskID, tskTitle FROM tblTasks ORDER BY tskTitle;
Column Count: 2
Bound Column: 1
Column Widths: 0,1

Set the Link Master/Child properties to
Master: stuStuID
Child: sttStuID

 

[Dabaum]

It sounds like he means in tblStudentTask there are 9 records. Easiest way
is to do the table from design mode first.

Field Name | Data Type
sttStTID | Autonumber
sttStuID | Number
sttTskID | Number
sttCompleteDate | Date/Time

This table will mostly be numbers that refer back to the simplified tables
of tblTask, and tblStudent. tblTask should have the 9 events/activities that
the pupils are going to do. tblStudentTask will hold each task linked to
each student, so for 20 pupils, you would end up with 180 records 1 pupil: 9
tasks.

At least that's what it sounds like I could be wrong.