Hi
Looks like I have come unstuck here
CREATE TABLE `pupils` (
`login` varchar(255) NOT NULL,
`fname` varchar(255) default NULL,
`lname` varchar(255) default NULL,
`intake` varchar(255) default NULL,
`idPupil` int(11) NOT NULL auto_increment,
PRIMARY KEY (`login`,`idPupil`),
UNIQUE KEY `idPupil` (`idPupil`),
UNIQUE KEY `login` (`login`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `yeargroup` (
`intake` smallint(4) NOT NULL,
`YearGroup` tinyint(2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `detentions` (
`ID` int(11) NOT NULL auto_increment,
`DateGiven` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
`TimeGiven` datetime default NULL,
`detainee` int(11) NOT NULL,
`donor` int(11) default NULL,
`TutorGroup` varchar(3) default NULL,
`DayForDetention` datetime NOT NULL,
`sess` int(11) NOT NULL,
`Lunchtime` varchar(1) default NULL,
`Done` tinyint(4) default NULL,
`reason` int(11) default NULL,
`Origin` int(11) default NULL,
`MoveToDept` datetime default NULL,
`DeptHead` int(11) default NULL,
`ReasonForDept` int(11) default NULL,
`OriginalDate` datetime default NULL,
`DoneAtDept` tinyint(4) default NULL,
`MovedToAfterSchool` datetime default NULL,
PRIMARY KEY (`detainee`,`DayForDetention`,`sess`),
UNIQUE KEY `ID` (`ID`),
KEY `Lunchtime` (`Lunchtime`),
KEY `To` (`detainee`),
KEY `TutorGroup` (`TutorGroup`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
this is a shortened version of what I want to do
In the add a detention record view, based on the detentions table, I have a lookup table selector based on the Yeargroup table, the staff see year 7, 8, 9, 10, or 11. Once the yeargroup is selected, the staff see only year 7 pupils in the pupil name selector in the add record form.
The value i need to link the yeargroup table to the pupils table is the `intake` field.
The value I need to store in the detentions table is derived from the `YearGroup` field oin the `yeargroup` table, concatenated with the first letter of the `login` field from the pupils table.
So if I select year 7, and the form then filters the pupils table and only shows me the pupils with an intake field of value 2006.
If I then select the pupil John Smith, who has a login of PSmithJ, the Tutorgroup value I need to save in the TutorGroup field in the database is 7P.
How do I do this?