Incorrect Table design for forms

[jex57]

Hi All.

What I am trying to accomplish is a datasheet whereby I can capture details for marks (grades) based on test results.

It would look something like this:

Test 1 test 2 test 3 test 4
Pupil Name
Pupil Name
Pupil Name
Pupil Name
Pupil Name


I created my database tables as follows

Class _table
Class_ID
Pupil Name

Subject_table
SubjectID
Subject_name

Category_Table
CatID
SubjectID
Category


Test_table
Test_id
catID
Test Descritpion


Mark_table
Test_ID
CatID
ClassID
Mark


Now I want to populate my marks - so ideally create a query that will display my entire class list, and a test description, which I would then capture (similar to excel). Problem is that based on my normalisation the query wont let me capture data - it states query is not updateable...

Is there a table or something that I am missing.

Is there a better way to do this?
I can no longer find the classroom management template that Microsoft used to supply

Thank you

 

[strive4peace]

instead of using a query to update information, design a mainform with subforms. Ideally, each form will be based on just one table.

The subforms will be synchronized to the mainform via the LinkMasterFields and LinkChildFields of the subform control.

 

[Micron]

Well, here's my two cents.
- IMO, your table set up is not quite right, plus the result you want looks like a spreadsheet approach, which is not how db's are supposed to be laid out. You will require a crosstab query to get what you're storing in rows to display in columns.
- Your marks table has no link back to student, which is what you ultimately want.
- If you're keeping data about students (gender, age, address, etc.) they belong in their own table.
Making assumptions (such as Science is Category; Chemistry is Subject)
- You have CatID in too many places.
- SubjectID doesn't belong in Category
- CatID belongs in Subject.

I'm going to stop there. Maybe this would help: if you have not done so already, create some spreadsheets with your intended design and put some fake data in. Try querying in your mind how you would retrieve information and see if you can follow a trail using links between table fields. If there are any you don't use in the process, you probably have a foreign key somewhere that's not needed.

 

[strive4peace]

good suggestions. Others also posted suggestions here:

http://www.mrexcel.com/forum/microsoft-access/901396-assistance-ms-access-offline.html