Hi guys,

I have a relationship probelm i'm trying to solve.
I have an External examiners DB that i need to design. The tables are as follows:
External Examiner - person table with name, address, phone, number.
Current Year - examiner appoinment details specific to that year such as application letter sent, date replied.
Programme - the course details that the examiner has been appointed to such as programme name, dept, school.
Report - Feedback given by examiner regarding his appoinment to a programme. Comments, date report received.
UG - Under graduate infomation - UG programme, Ug Jacs code.
PG - Post graduate infomation - PG programme, PG Jacs code.

The first tables are pretty straight forward. An examiner can be appointed for several programme in the same year. The relationship is as follows: External examiner --- 1-to-Many --- Current Year --- 1-to-1 --- Programme.
The confusion occurs with the report, PG & UG Table.
If the examiner was appointed to a UG programme then a single UG report is produced, If the examiner was appointed to a PG programme then a single PG report is produced. It is, however, possible that within the same record that an Examiner is appointed to both UG & PG programmes. This would require 2 reports - 1 for UG & 1 for PG. These all need to be linked back to the current year & specific examiner.
I would need to view the data by external examiner (if a selected examiner 001 i would see the peronal details of the examiner, the current year details & the programme details. I would also see the PG & UG details. From this, if both UG &PG details were filled then i would be able to slect a reports tab and both of the report (1 UG, 1 PG) would be viewable for that specific examiner.

Any ideas how i would do this? What would the relationships be? I know it is difficult to determine what i'm after from the description above but it's hard to explain what i'm trying to do.
If you need further clarification then plesae let me know and i'll do what i can.

Thanks,

Shuja.

I would suggest a structure like this:

The people info:

tblExaminers
pkExamID autonumber, primary key
txtFname
txtMI
txtLname
txtAddr
etc...

The activities: application letter sent, application reply, appointment, etc would be records in this table

tblProgramActivities
pkProgActID autonumber, primary key
txtActivityDesc

The program types: 2 records UG and PG

tblProgramTypes
pkProgTypeID autonumber, primary key
txtProgTypeDesc

All of the possible programs the examiners can enroll in:

tblPrograms
pkProgramID autonumber, primary key
txtProgramDesc
fkProgTypeID foreign key that relates to the type of program

The transactions related to the examiner and the programs and the activities

tblProgramTransactions
pkProgTransID autonumber, primary key
fkExamID foreign key relating to the examiner
fkProgramID foreign key relating to the program
fkProgActID foreign key relating to the activity for the specific examiner and program
transdate

I would not set it up as current year. All transactions from all years go into the tblProgramTransactions. You can extract what you need using queries with date criteria. For the UG/PG reports you can use a query that limits the records returned by the programtypeID field (UG only, PG only or both).

Hopefully this will help you out.

Thanks jzwp11. Really appreciate the detail of help you put in.

I'm not sure about the relationship you put in between tblPrograms and tblProgramTypes.
tblProgramTypes would be where the UG & PG details are selected but you have the pkProgTypeID as a foreign key in tblPrograms. The problem is that each program could possibly have both UG & PG options selected. Each UG & PG has fields which are free text entries which specify details of the programme the examiner has been assigned to. The description for each record will be different & unique (i.e. if examinr is appointed to both UG & PG for a specific school then the UG record will have a free-text field which outlines the exact detials of the UG programme like program name, qualification. PG will have the same field but for PG programme details.)
Also, the report table is required and i'm not sure where it would go. The report table holds data on the feedback from the examiner for each UG & PG programme. Examiner can have one report (UG or PG) or 2 reports (UG & PG). These need to be related directly to the UG & PG records as the feedback is on the specific programme and type.
Also, i'm not sure how to implement this as an application form. I assume that the progType would be a subform within the Program but how would i display each report relating to UG & PG records?
I had wanted to have a system where i have a tab which shows the reports section (i.e. if an examiner has both PG & UG sections selected then clicking the report section tab will bring up a section where 2 feedback reports records were created - 1 for each progtype (ug & pg).

Any ideas on how to implement this?

your help would be really appreciated.

thank you,

Shuja.

Now that I have a slightly better understanding, we need to tweak the design a little

The program types: 2 records UG and PG

tblProgramTypes
pkProgTypeID autonumber, primary key
txtProgTypeDesc


The list of programs
tblPrograms
pkProgramID autonumber, primary key
txtProgram

tblSchools
pkSchoolID autonumber primary key
txtSchoolName

tblDept
pkDeptID autonumber primary key
txtDeptName

Junction table that relates schools and depts
tblSchoolDept
pkSchoolDeptID autonumber, primary key
fkSchoolID foreign key related to tblSchools
fkDeptID foreign key related to tblDept


We need a junction table that relates both program type (UG/PG) and the Program and the pertinent details
tblProgramDetails
pkProgDetailID autonumber, primary key
fkProgTypeID foreign key saying whether it is a UG or PG
fkProgramID foreign key relating to the program
fkSchoolDeptID foreign key relating to school/dept junction table
txtQualifications
other fields

The transactions related to the examiner and the programs and the activities

tblProgramTransactions
pkProgTransID autonumber, primary key
fkExamID foreign key relating to the examiner
fkProgDetailID foreign key relating to the program/detail junction table
fkProgActID foreign key relating to the activity for the specific examiner and program
transdate

What constitutes the report you talk about. Is it like a questionnaire where there are specific questions and the examiner supplies the answers? Can you give more details on that?

Hi,

The report is a set feedback form which the has fixed sections to fill. Not like a questionnaire but it does have fixed fields (Set of checkboxes which state what the examiner did, comments field, report received date, schools response, response date, etc).
Departments are organised into specific schools but we can't have them related as the departments get reorganised into different schools on a regular basis (i.e. dept 1 is under school 1, dept 2 is under school 1, dept 3 is under school 2 - 6 months later: dept 1 is under school 1, dept 2 is under school 1, dept 3 is under school 1).
Also, for the time being, i would like to leave the dept as free-text entry. School is a drop down with a list of all schools.

The basic idea of the application is to select examiners for school examinations and record all the relevant detail at the beginning. After the examiner has carried out his work, he/she fills out the feedback which is then also recorded onto the specific examiner record, which should already exist within the database. the difficulty is if the the examiner is required to do both UG & PG examinations for same school & dept as this would be recorded in the same single examiner record but 2 seperate feedback reports are needed to be logged at the end (1 UG, 1 PG).

Please let me know if you need more info.

Thank you,

Shuja.

You, in effect, have a questionnaire. You have a series of things that the examiner checked (questions) and the checked boxes or comments are the responses. So you will need a table for questions and a table for responses. The examiner would fill one out for each assessment. The examiner's responses are tied back to details of the assesment captured in tblProgramDetails via the fkProgDetailID

tblQuestions
pkQuesID primary key, autonumber
lngQuestionSeqNo a sequence number if you want to arrange questions in a certain order
txtQuestion the text of the question or item being reviewed

tblResponses
pkResponseID primary key, autonumber
fkQuesID foreign key relating back to the question
fkProgDetailID foreign key relating to the junction table that hold the unique examiner, program, UG/PG info
txtresponse a field to capture the response

Questionnaire type databases come up often on the forum, so you should be able to find more detailed structures if you do a search.

You would then use a query that extract the info you need to create the assessment report.

Thanks jzwp11,

Really appreciate your help, mate. I have a better understanding of what i need to do - thanks to you.
I will try and implement this and see what i come up with.
I'll probably have to pester you in a few days time for more advice - hope you don't mind!
Thanks once again for all your help.

Regards,

Shuja.

Edited by: dabooj on Thu Mar 29 6:40:03 EDT 2007.

No problem, we are all here to help.