Creating an exam timetable (1 Viewer)

Hi,

I developed this database about 4 years ago, and it's safe to say that it wouldn't be where it is without help previously received here.

It is used to organise the support provided in exams to pupils with additional support needs.

I have attached a zip of the front and back end to help folk who maybe able to answer my question.

Just a note although the data looks like actual pupil data the names are actually members of the British parliament and the birthdates and candidate numbers are made up. Other than that the volume and type of data is a realistic representation of what I am working with.

Currently I use it to produce a variety of paperwork all driven by forms.

I am working on a new addition with the aim of producing exam timetables showing which pupils in each subject require an arrangement/s and what that arrangement/s is/are. I have attached a word version of the timetable to illustrate what the final output will be. Currently I would laboriously transfer this data from my db to a word version of the timetable provided by my manager.

So far I have successfully managed to reproduce the barebones timetable, now I need to work out how to populate that with the pupil and arrangement information, I think I need to join the WholeSchoolTimetable Query and the SubjectLevelArrangement Query but so far I haven’t been able to get it to output just the information required.

Currently my closest attempt (WholeSchoolTimetable Query NOT WORKING) lists all pupils sitting a certain level everytime that level appears in the timetable. e.g. The first exam listed is Advanced Higher English, which lists all the pupils doing Advanced Higher Something. In actual fact I have no pupils being supported in AH English. The next exam should be Higher English which is again messed up as there should be just 7 candidates listed here.

Any suggestions anyone can make for getting this query working will be gratefully received.

I've spent some time trying to sort this out but there are some basic design flaws which you need to sort out.

Your NOT WORKING query used outer joins and was based on 3 queries. It had 7815 records including a blank record!

I've simplified this query so its now based on 2 queries with inner joins and unique records.
That helps to some extent reducing the number of records to 258 ....but its still not right



Next I looked at the underlying queries involved.
The subject level arrangement query that is used in the above is a bit of a mess. Again I've changed it to use a inner join as below.



I've excluded arrangementID values 1,24 & 27 as these need no intervention
However, the fact that it's a one (SCN) to many (Pupil) join was a clue there was something wrong.
When I checked these it makes no sense that you're getting any results at all as SCN is a 9 digit number & Pupil is their forename. How can that work?



You need to redesign these tables to use a common pupil ID value e.g. SCN in both tables. Then rebuild that query & after that rebuild your 'not working' query.

Modified FE db attached for you to review & update.
I've not uploaded the BE as I've made no changes to that

Thank you Ridders.

You have made it do exactly what I was trying to get it to do. You even read my mind and excluded the irrelevant arrangements, although I do actually need to put TBC back, as seeing that in the resulting report is the flag to chase up pupils/staff/parents who haven't provided evidence

I will need to check it back at school tomorrow by running it with my actual pupil data and comparing it's results with the actual exam timetable I produced earlier this year, but from memory it looks as if everyone is there.

I appreciate your comments about queries being a bit of a mess, but I am a Music/Biology/Suppport for Learning teacher with absolutely no experience or training in DB production, entirely self taught with the help of google, youtube and as my OP stated the kind folks here.

I think the reason its a mess is because I was using a simpletons method to solve a complex situation. 1 Pupil, has 1 candidate number, sitting many exams, each of which is at one level (but could be different from the others), each exam could have many arrangements (which could be different for each subject).

The way it works will never be elegant or even correct, but it's main job is to make my life easier in terms of producing the required documentation and it does that very well.

Now that the query appears to be working correctly I can move onto my next challenge of making the timetable input form (shouldn't be too hard), and the timetable reports (Just a matter of fiddling with formatting). One for whole school overview which I think I'll need to work on concatentating individual pupil arrangements to prevent the document being too unwieldy. I will also work on modifying the query you produced so I can produce timetables for individual pupils.

Thanks again.

Glad I could help.

I spent time on this as my business software is mainly providing databases for schools.
If interested, you can see the link to my website at the bottom of this post.
The site has various demo files & some free software.
There's also an exam timer (but at a small cost)

I've done lots of similar things to your database though, for some reason, not that specific idea

but I am a Music/Biology/Suppport for Learning teacher with absolutely no experience or training in DB production, entirely self taught with the help of google, youtube and as my OP stated the kind folks here.

Click to expand...

I am an ex-teacher (Science/ICT) and am also entirely self taught. When I started using Access in 1999, I was utterly clueless and relied extensively on online support including lurking as a guest here at AWF. My schools databases were originally written to fill gaps in existing systems ...

These days I'm here mainly to provide some payback for the help I received many years ago

If you've not seen them, I recommend Steve Bishop's Access training videos on You Tube - they are excellent.

Before you add on the next steps, I strongly recommend you sort out the basic structure of your db. It will make future development work MUCH easier if you do so.

The other thing I'd recommend if you aren't already doing so is that you 'pull' student & exam data from the schools management database e.g. SIMS.

Good luck with your project.

Hi again,

Firstly I can confirm that the query you created does indeed work as intended now that I have tested it in school on real life data.

I'll need to reply one more time after this, then my post count will be high enough to view the links in your signature. :-D

I do already import the data from our existing pupil management system. We use SEEMIS in Aberdeenshire. I have to manually copy and paste it to Excel frirst to perform an import from there. It is probably possible to move directly from SEEMIS to the access but as a teacher I don't have full access to the admin functionality of SEEMIS, so will have to stick to the half way house solution.

In terms of Table rebuild in the BE if I understand you correctly I need to leave pupil details as it is.

i.e.
TBL Pupil Details
SCN - Unique 9 digit number provided by exam board. This is the primary key.
Forename
Surname
DOB
Year
Class
Nature of ASN
Evidence
Current Support
Verification
Communication Date
Action Points
Archive - Yes/No choice.

But I need to do something with the subjectlevelarrangement table which is currently

TBL subjectlevelarrangement
ID - Autonumber - Primary Key.
Pupil - Number
SubjectID - Number
LevelID - Number
ArrangementID - Number

I don't remember what I did when I set this up originally 4 years ago but looking at the design view although it displays pupil name, the data associated with the table is the SCN number.

I do remember the reason for setting it up this way however was because I needed the ability to assign more than one arrangement to each subject for each pupil therefore with my limited knowledge it was easiest to make a record for each arrangement then work backwards tying the level, subject and pupil to that. I hope that makes sense.

What I'm not sure about is quite what I need to do to subjectlevelarrangement table to make it into a more correct structure.

Interesting to hear that you are an ex teacher, especially when I see your location. Were you a teacher in Somerset in 1999? This only piqued my interest because although I am now located in the frozen north, I trained in 2000 in Bath, subsequently working in Warminster, Swindon, and Bristol before moving up here in 2007.

Thanks again for all your help so far.

Hi Darwin - probably not Charles!

1. Good to know the query works now.

2. I remember CMIS as an alternative to SIMS but not SEEMIS.
I'm sure its very similar.

My databases get staff/student & whole school data from SIMS every night.
I do this by remotely exporting 30 or so CSV files, importing them into Access, manipulating the data then appending new records or updating existing records as appropriate. The data is stored in a SQL server BE database.
The process is complex and took a long time to develop to its current state but it works well

3. Table structure
I would strongly recommend that you don't use spaces in any table/field or query names. Much easier to type and less prone to errors.

Also avoid the use of reserved words e.g. Year for field names - use YearGroup instead

Apart from that, much of your PupilDetails table is fine.
However I would add some additional fields such as
- Active (Yes/No)
- ExamNumber (here or in 2nd table)

I would also split off all fields starting with Nature of ASN into one or more separate tables partly because its likely you will have more than one record for several fields e.g. CommunicationDate / ASNNature
Use SCN as either the PK field (if the records are unique) or as an FK field if they're not

The 2nd table definitely needs some changes
For example, replace Pupil with SCN

Suggest you also need tables which have:
- StudentContactDetails - address / parent or carer name / phone / email
- Timetable info - or at least a list of subjects / classes & teachers

Other data will almost certainly be needed.
The best strategy is to plan it all out on paper in detail - what do you need / how should it be grouped into tables / what are the relationships needed between them to make it all work

When you've done all that, start rebuilding the database to match your model. It will take extra time now but save you even more time in the long run

4. My background
I taught for many years before retiring in 2011. I moved to Bristol in the early 1980s & then Somerset in 2000. My last school in Bristol was Brislington where I was Head of Science before moving on to become Deputy Head at a school in Weston-super-Mud. I suppose its possible we could have crossed paths as Science teachers when you first trained....

Since retirement I've been developing management software for schools but recently branching out into new areas (as schools have no money to buy software!)

Anyway, I expect you'll reply if only to make 10 posts but to save you time the website is www.mendipdatasystems.co.uk

Sorry about the lengthy reply - rambling on a bit I'm afraid!

You're right not Charles, not even Darwin. It's Luke.

ridders said:

2. I remember CMIS as an alternative to SIMS but not SEEMIS.
I'm sure its very similar.

My databases get staff/student & whole school data from SIMS every night.
I do this by remotely exporting 30 or so CSV files, importing them into Access, manipulating the data then appending new records or updating existing records as appropriate. The data is stored in a SQL server BE database.
The process is complex and took a long time to develop to its current state but it works well

Click to expand...

This does sound far more complex than I am able to tackle with my existing access rights to SEEMIS, so I will stick with the manual movement to and then import from Excel.

ridders said:

3. Table structure
I would strongly recommend that you don't use spaces in any table/field or query names. Much easier to type and less prone to errors.

Click to expand...

I had thought I was pretty good at this, but on looking back I realise that no I'm not. Though in my defence, many of those fields store no information I had put them in place so that they would appear on printed reports allowing space for handwritten notes to be written. This was before I knew that it was possible to add this sort of thing to the report in design view. I really was very green when I started.

ridders said:

Also avoid the use of reserved words e.g. Year for field names - use YearGroup instead

Click to expand...

Noted and Changed

ridders said:

Apart from that, much of your PupilDetails table is fine.

Click to expand...

Nice to know I got at least some of it right

ridders said:

However I would add some additional fields such as
- Active (Yes/No)
- ExamNumber (here or in 2nd table)

Click to expand...

- Active (Yes/No) - Think this may have the same functionality as my 'archive yes/no' essentially it is used to exclude pupils from the reports who end up not being entered for exams.
- ExamNumber (here or in 2nd table) - I'm not sure on what this would be for. Would it be to give each exam paper it's own identity? i.e using autonumber English Higher Paper 1, would be 1, English Higher Paper 2 would be 2, etc

ridders said:

I would also split off all fields starting with Nature of ASN into one or more separate tables partly because its likely you will have more than one record for several fields e.g. CommunicationDate / ASNNature
Use SCN as either the PK field (if the records are unique) or as an FK field if they're not

Click to expand...

This makes sense, although as I mentioned earlier in the thread they were originally created 'imaginary' fields to make boxes appear on a report, I could probably actually delete them. Or include them properly and make proper use of them.

ridders said:

The 2nd table definitely needs some changes
For example, replace Pupil with SCN

Click to expand...

Thinking back, I probably set this up in this way before I understood that you should never manipulate the data directly in the tables, but use queries and forms, therefore I needed to see the name of the pupil when making changes. Though this is now a moot point as I spend so much time looking at these numbers that I now mostly know the pupils by number. So I will be changing this one.

ridders said:

Suggest you also need tables which have:
- StudentContactDetails - address / parent or carer name / phone / email
- Timetable info - or at least a list of subjects / classes & teachers

Click to expand...

This is a good idea, however authority data protection policy prevents me from doing this. All pupil contact data should only be stored and accessed through SEEMIS. Therefore I have a cover letter template which is stored in SEEMIS, when it is time to send paperwork home, I provide the admin staff with the list of names, they then generate and print the letters directly from SEEMIS. I can then complete a print run of the relevant reports from the database. It is clunky but policy is what it is.

On the pupil timetable note when I originally designed the DB I felt that this info was surplus to requirements, though as things have evolved and I seek more evidence from the class teachers it does make sense and I could use it for cataloguing these communications.

ridders said:

Other data will almost certainly be needed.
The best strategy is to plan it all out on paper in detail - what do you need / how should it be grouped into tables / what are the relationships needed between them to make it all work

Click to expand...

Believe it or not I actually did this, though as is often the case things evolve and develop. When I started I would never have dreamed of trying to make the timetable from the database. When I took this role on the whole process was still being completed longhand!

ridders said:

When you've done all that, start rebuilding the database to match your model. It will take extra time now but save you even more time in the long run

Click to expand...

This is good advice and I take it willingly however as you will be well aware time in school waits for no-one so for this year I will roll with what I have as I need to get on with gathering the evidence for this years cohort. However I will definitely be spending time on building a new database from the bottom up, as I accept that this version has come as far it can, my aim would be to have the new improved version in place by this time next year.

ridders said:

4. My background
I taught for many years before retiring in 2011. I moved to Bristol in the early 1980s & then Somerset in 2000. My last school in Bristol was Brislington where I was Head of Science before moving on to become Deputy Head at a school in Weston-super-Mud. I suppose its possible we could have crossed paths as Science teachers when you first trained....

Click to expand...

I am convinced our paths could have crossed. I did a lot of supply at Brislington in 2005/6. I worked in the old school which was falling down around us as we could see the shiny new one rising out of the ground next door. Might even have been involved in an inspection at this time, either way I am fairly certain the head teacher at the time was John Matthews. I also did supply in the Weston-Super-Mud schools around the same time.

ridders said:

Sorry about the lengthy reply - rambling on a bit I'm afraid!

Click to expand...

No need to apologise, everything you said has been relevant to my situation in one way or another, and has provided me with much to think about as I move this DB forward.