Request for help on Database design

[rama1712]

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

 

[ejstefl]

Read up on Data Normalization.

After briefly skimming your requirements, I would think you would want the following tables:

tblPupil
-PupilID (autonumber, PK)
-FirstName
-SurName
-etc.

tblTask
-TaskID (autonumber, pk)
-TaskName
-TaskDesc
-etc

tblPupilTask
-PupilTaskID (autonumber, PK)
-PupilID (fk)
-TaskID (fk)
-DateComplete
-TimeToComplete
-NotesToComplete

You don't need to store the total time taken (nor should you) as you can calculate it at any time by summing the TimeToComplete field where the PupilID = a certain pupil.

Hope that gets you started...

 

[rama1712]

Thaks for info more help needed

Sorry got confused with pm and thread thats what i get for trying to do to many thinks at once



Any Chance you can do a demo database and email it me as when i do the table you say i get an
This expression typed incorrectly
If you can pm me and i will let you have email address

If you can help this would be great School a bit stuck at moment with this and i have just been informed that there is a time pressure (Nice of them to tell me) 1 week I have to come up with something.
I am a hardware man not software design but all thay see is a Techie and think we can do everthing related to computers

Stuart

 

[ejstefl]

I'm sorry, but I am not going to build your database for you. Take a shot at it. Post with specific questions.