ms access request for help

[rama1712]

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

[wesleyw]

You'll need a second table with a foreign key in the first to refer it too.

Pupil Table ->
Automatic ID (PK)
First Name
Surname
Form
Total Hours

Tasks Table ->
Automatic ID (PK)
Description
Task Complete
Date Task was completed
Time taken to complete this task
Pupils ID (FK)

Then link the FK on the Tasks table to the PK on the Pupils.

Then you could write a quick bit of code to look up all of the tasks with that pupils ID number and calculate the total time taken so far!


Wes