V!/dp1 is a database of A level subject choices made by Year 11 pupils.

Julian Fry supplied the layout CountByBlock and associated joins.
 
This database started life as a flat file with fields as in the table 'Pupils'.

After advice from Neil Raine I reorganised it into a relational database with 5 tables:
  Blocks - 1 field: 'Block'; 4 rows: "P","Q","R" an "S"
  Subjects - 2 fields: 'Short Sub Name' and 'Full Sub Name'; 27 rows "Art",...,
             ,...., "XXX","   "
  Forms - 1 field 'Form', 6 rows "H","M","P","S","W" and "New"
  Pupils - 15 fields of pupil details including:'Code' (unique),'Opt1',...,
           'Opt4'
  Choices - 3 fields:'Code','Block','Subject'

The 'Pupils.Opt1',...,'Pupils.Opt4' fields are a throwback to the original flat file but retained for ease of data entry. The layout Options is a view of table Pupils with a sub-view of table Choices.

The table Choices  is unpopulated initially and rows must be created (up to 4 per pupil) by a script:
 from Layout 'Scripts' click on the button 'Transfer Opt1-4 to Ordered options'
 
(Second and subsequent clicks on this button rerun the script and duplicate the data so another button 'Delete Ordered options' will run a script to empty the Choices table.)

Two other script buttons are I think self explanatory (and dangerous!):'Delete options', 'Delete Pupils'.  The remaining script button 'Upper case' forces all 'Subjects.Short Sub Name' rows to upper case.

These scripts take tens of seconds to tun on a StrongArm and are much faster on a PentiumII 266.

Various other layouts produce form lists and set lists by subject and block.

The layout Count calculates the overall numbers of pupils choosing each subject.

The layout CountByBlock (thanks Julian) breaks down the count by block.




Stephen Williams

mailto:sjw@optmax.demon.co.uk