I am trying to create a database at work that stores the details of students and checks that are carried out each week for each student.
So 500 students have 3 checks per week every week for 39 weeks.

I want to be able to fill this in on a fresh datasheet each week for a whole class of 33 students but to store the data collected from previous weeks.

My initial though was to have TBL_student linking to TBL_week then to TBL_check but this doesn’t work!!

Any ideas?

TBL_check needs to be linked to both other tables at least.

Why doesnt that work, you will need a common field between the three tables, something like StudentID, WeekNumber would do it.

So TBL_student links to TBL_check which stores the results of the three checks and a date stamp. Why do you need TBL_week?

rich the students needs the same checks carrying out each week – so multiple checks over multiple weeks

Can’t you just create a record in a table for each check carried out. The record could contain the student ID, check carried out, date stamp and result of the check.

Link this to a table containing all of the student IDs and you can query it in any way you want.

If you want to create a new data sheet each week you could update this into the master table will all the records via a simple append query.

If you want to maintain a tbale for each week then you could join these using a UNION query (you will have to write this in SQL mode but Access does support it)

Rob,
I still dont think you need the weeks tables. You can record the results of the checks with a dates stamp so the table just keeps getting longer…

Agree with SR. If you need the figures to say ‘Week 1’ etc you can put a drop down list in the form to record that.

The thing is – i want a form group of 35 kids to be visible to a non IT literate member of staff to fill in each week with a blank sheet.
Essentially so it looks like a spreadsheet with kids names down the left and categories across the top.
That is really what i am struggling to do

That is relatively easy then. The best way would be to use a form but they could just type directly into a temporary table.

Then when they have finished adding data you need to trigger some form of macro or VBA script that takes the data they have just created and appends it into the big table of results. You can then either archive the individual table for that week or delete it.

still not working grr

Probably echos what’s already been said …

TBL_student
==========
Student_ID
Name

TBL_week
========
Week_Number
Year

TBL_check
=========
Check_ID
Check_Name

I think you’re missing the linking table
TBL_week_check_student
======================
Week_Number
Year
Student_ID_FK
Check_ID_FK

And if you have this table it makes the TBL_Week redundant.

If you present this as a spreadsheet than it will have to be as a pivot table and you will have to write a macro to save the data off it.

used jfletch’s idea 🙂

One of life’s winners 8)