filling a junction table with correct values

[andy49]

I need to fill the junction table (tblpaperstudent) and I don't really have any experience on Many to many tables nor how best to fill them




Tblpaperstudent has
PaperStudentID_PK
StudentID_FK
PaperID_FK
DateTest
TestScore

My student list is already in the db (but is deleted and updated daily)

My team aren't super IT efficient so I'd like to keep it as simple as possible

The only decision they make is whether a student takes a foundation or higher tier paper and this will affect the PaperID_FK that they sit. The first time they do this will be in June, the next in October, then in March then in June and so on.

DBschoolexamPapersvtest.zip
I' ve attached a db I'm playing with. feel free to comment.



Ideally, Id like them to select the Foundation or Higher Tier only then something updates the tblpaperstudent


Any help would be priceless.

[Bulzie]

Sorry not sure i understand the question. Might be helpful to see structure of Student and Paper tables. Seems you have that table as your main form, then combo boxes for Student and Paper which they select on the form and then enter the DateTest and TestScore?

[andy49]

Here's my table layout

Thanks for taking a look.

[orange]

Andy,

Please describe what your tables/relationships represent in your "business".
Why are there no relationships for Classes and Tiers?

[andy49]

I'm a teacher trying to automate a process which is currently done ad hoc using excel.

Pupils in year 9,10,11 take exams at the end of each academic session (4 months) They are listed in tblPupil

Exam papers (Listed in tblpapers) vary from session to session but are always foundation or higher. The list of papers grows as the exam board releases new papers. I have a form to add papers when they are released

Staff (my team) select whether a pupil is foundation or higher then students take those exams (3 papers)

Staff then enter results (out of 80 for each of 3 papers) which are stored on access.

These totals out of 240 are also graded by access

the bit that's throwing me is how to fill the junction table tblpaperStudent

Any ideas I run into a wall

the three unjoined tables are the start of ideas I've had which never worked!!

[orange]

Here is a draft model that may help. You can adjust as you see fit. You know your details/business bettan er than any reader.

Good luck.
An ExamBoard releases 1 or More Papers
A Paper can be 1 of Foundation or Higher Type
An Exam consists of 3 Papers
Exams are given in AcademicSessions
A Pupil takes 1 or more Exams
A Pupil is rated as Foundation or Higher
A Pupil is marked based on Exam score(s)

[orange]

Andy,

If this hasn't helped, then please post a focused question, or a copy of your current database.

[davegri]

Are all students linked to all papers? If not, how does a student become linked to a paper? How are grades entered? At the same time paper is linked to the student?
Do you have forms to do this?

[andy49]

DBschoolexamPapersvtest1111.zip

Ok

One problem sorted. I've used the frmselecttiers to allow staff to select which students are higher or foundation then an insert SQL updates the table.

Now I have another many to many I think

there are many exam slots (3 each year) and 3 exam papers for each slot Each exam paper can go in any slot too

I think this means I need another junction table???

[davegri]

I would edit the relationships to enforce referential integrity.
If the relationships allow you to create queries that satisfy the requirements for report and forms, then you're good.
We don't have the details to suggest more at this point.

[andy49]

DBschoolexamPapersvtest.zip

Thanks for the ongoing advice all. I feel my knowledge of junction tables is growing but unfortunately it seems I'm lacking in experience.

In my latest version on frmcalendarslots you'll see I've "succeeded" in creating a split form with a subfrm containing just a combobox to fill my junction table with a slotID and a paperID. This works very well but it only helps up to a certain point as each slot has three papers and the current setup only adds one paper.

Adding extra comboboxes doesn't seem to help so am looking for an alternative or maybe a slight change.


As a reminder, each slotID will link to 3 paperIDs

Many thanks again

[davegri]

From the user's point of view, does it make more sense that slots are assigned to papers or papers to slots?
In other words, would you have a frmPaper_Slots showing a paper, and allow slots to be assigned to it or
a frmSlot_Paper that showed a slot, and allow papers to be assigned to it?

[andy49]

Good question. TBH the slots are fixed and I get the info from my manager that the papers for that slot will be x,y,z and so on. That particular form would only be used by me.

ATM, I know when the next slots are (July 2017) but not the papers.

Does that help Dave?

[davegri]

DBschoolexamPapersvtest_davegri_v001.zip
See how you like the dual listbox approach. See frmAssignSlots

[andy49]

Looks great Dave and does the job too. Thanks a million mate.