>
> Maybe a C/S gal can ask a few questions...

Lovely! :)

> First off, I can't quite sort out these relationships. Do you have

separate tables for Students and Contracts? The two foreign keys
would suggest that you do...and can one student have multiple
contracts? You mentioned that you have a student_contract_link table.

>
> Could you please show us the structures of these three other tables?

Ok. Here they are (I've trimmed out a few unnecessary columns and
constraint stuff)

Each company has some students. Each company can have one or more
contracts, with one or more students per a contract. Each teacher is
attached to the overall agency for whom the app is targeted. The
attendance records are filled in by the teacher . (There can be more
than one teacher used to fulfill a contract.) The hours that get
plugged into the grid (student hours grid for teacher/month/contract)
is the total that the student was taught by that teacher (for that
month and contract).


These are the three main chaps :

CREATE TABLE COMPANY (
COMPANY_ID INTEGER NOT NULL,
NAME CHAR(30) CHARACTER SET ISO8859_1 NOT NULL,
);

CREATE TABLE STUDENT (
STUDENT_ID INTEGER NOT NULL,
COMPANY_ID INTEGER NOT NULL,
NAME CHAR(40) CHARACTER SET ISO8859_1 NOT NULL,
);

CREATE TABLE TEACHERS (
TEACHER_ID INTEGER NOT NULL,
NAME CHAR(30) CHARACTER SET ISO8859_1 NOT NULL,
);

Then a contract :

CREATE TABLE CONTRACT (
CONTRACT_ID INTEGER NOT NULL,
COMPANY_ID INTEGER NOT NULL,
NAME CHAR(20) CHARACTER SET ISO8859_1,
);

Then attendance records ( which the teacher fills out on a form, a
line per a student of total hours for the month for that contract)

CREATE TABLE ATTENDANCE (
ATTENDANCE_ID INTEGER NOT NULL,
TEACHER_ID INTEGER NOT NULL,
CONTRACT_ID INTEGER NOT NULL,
AMONTH MONTHDOMAIN NOT NULL,
); //all four above constitute a unique record (month is part of
unique contraint)

CREATE TABLE ATTENDANCE_DETAIL (
ATTENDANCE_DETAIL_ID INTEGER NOT NULL, //probably not needed
ATTENDANCE_ID INTEGER NOT NULL,
STUDENT_ID INTEGER NOT NULL,
HOURS NUMERIC(15,2),
);

and a single link table :

CREATE TABLE STUDENT_CONTRACT_LINK (
CONTRACT_ID INTEGER NOT NULL,
STUDENT_ID INTEGER NOT NULL
);


The form I wish to construct looks a little like this :

Title : teacher/month/contract

grid : Student name | Hours

I know which students to get for the attendance info because of the
student-contract_link table (since each attendance sheet is for an
individual contract).

The "attendance detail" rows don't necessarily exist yet, although
the parent does. I'm hoping to set it up to be as automatic (as
little coding) as possible. But how do I do that AND get the students
name?

Yikes,

Thanks

Greg