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