The merits database that you can download (as an .mdb or .accdb file) from this site is an example of a system created in Access that hopefully contains examples of most techniques you might want to use in an A level ICT project, with the exception of preventing double-bookings, which is described on the previous page.
The Problem
A school gives out merits to students. A member of staff gives a merit for his particular subject (or maybe a tutor period). Currently it is written on a standard slip and passed onto the head of year. The head of year then keeps track of how many merits each student has, so that certificates can be awarded at the end of the year to students who receive a certain number. The merit is then passed to the student's tutor, to be given to the student.
The information recorded on the merit is as follows:
- Student name
- Staff name
- Subject
- Date
- Reason for merit
- Student's tutor
- Student's head of year
The Solution
I learnt quite a bit about Access myself while I was creating this database. I don't propose to explain the normalisation, etc., but rather the techniques used in Access to implement the solution in a user-friendly manner. I'll take you through them in logical groups (i.e. going across the Access screen a tab at a time!).
Tables
The entity-relational model (i.e. the relationship screen in Access) for the database is shown below. I don't think I've done anything particularly bizarre here - students belong to a tutor group, the tutor group belongs to a year, which has a year head who is a member of staff. Subject departments also contain members of staff, and have a department head. The merits table brings together a student and a subject teacher.
Queries
The queries are all quite straightforward, and apart from qry_student_names fall into three basic types - those that find the merits to be printed, those that gather the information for the reports, and the update queries.
The update queries are those with the yellow pencil icon. You should notice that these pair up with an ordinary query with a very similar name, e.g. qry_dept_print and qry_dept_printed. Essentially these have the same criteria - to find all the unprinted merits in a particular category (in this case a department), ready to be printed by the reports - the update query then sets the Printed field to Yes, so that it isn't found the next time the query is run.
The remaining queries gather the information for the reports for the head of year - i.e. all merits for a particular student, tutor group, or year.
All the queries are very similar and were created by copying, pasting and then amending the first query I created. The only interesting thing to note is the multiple occurrences of the staff table in the same query.
I always used to wonder why Access let you add the same table to a query several times - and now I know! On the merit sheet, you have the names of three different members of staff - the person who awarded the merit, the tutor, and the head of year. These are stored as initials in three different tables, but all the full names are stored in the same staff table, and it's the full names we want to print. If you want three different names to appear on the merit, you need to add three copies of the staff table to the query!
I said that qry_student_names was different, however. What that query does is to create a derived field that is a combination of the student's surname, forename, and tutor group. This is used to populate any combo-box used to input a student. It allows the user to find any student in the school by typing in the first few letters of the surname until the student is found.
Forms
Although there are 13 forms in the database, only one of them is bound to a table - frm_merits. All of the other forms are either menus, with buttons that open other forms, or forms that capture information for use in queries (see the Access page for details on how to do this).
Details of the merits themselves are entered in frm_merits (shown on the right). The Staff field is populated from the staff table, and the student population is populated from qry_student_names. The Date field has a default value of Now(), although Date() might be better if you don't want to store the time too.
When you select a member of staff, the Subject combo is populated with the subjects that that member of staff teaches. This is done using the properties on the combo-box to show the subjects for the member of staff selected in the field above. Some code (shown right) has been added so that when the member of staff is changed, the list is re-populated (requeried).
The remainder of the code simply selects the top subject and disables the subject field if there is only one entry in the list.
Reports
The reports used in the system are all quite unremarkable. Then ones with unprinted in the name, together with rpt_reprints are used to print the merit sheets. They're essentially copies of the same report, just attached to different queries so that they print different things.
The remaining reports count and total the number of merits by department, tutor group, year, etc., as indicated by their name. Again, these are very similar, but are bound to different queries.
Macros
I'm not really a big fan of macros - they generally seem to be a long-winded way of something that you could do by just amending a bit of the VBA attached to buttons. All the macros in here are very simple, and they nearly all do the same thing.
If you call a macro AutoExec, then it will run automatically as the database is opened. In this database, all AutoExec does is open a form that acts as the main menu for the system.
All of the remaining macros do the same thing - they enable one button to perform two actions; something that could easily be done by editing the code. There's one macro for each type of merit printing report - the reports print all unprinted merits in a given category, and then the macro runs an update query to set the Printed field to Yes so that they're not printed again.