Student markbook db - access 2013 - multiple table joins

[Markthearm]

Hi all,
hoping for some help...
I've constructed a student mark book database with the following tables -

[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD][TABLE="width: 100"]
<tbody>[TR]
[TD]TblStudents[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]TblResults[/TD]
[TD]TblAssessment[/TD]
[/TR]
[TR]
[TD]StudentID[/TD]
[TD]ResultsID[/TD]
[TD]AssessmentID[/TD]
[/TR]
[TR]
[TD]StudentEmail[/TD]
[TD]StudentID[/TD]
[TD]Title[/TD]
[/TR]
[TR]
[TD]StudentName[/TD]
[TD]AssessmentID[/TD]
[TD]Date[/TD]
[/TR]
[TR]
[TD]Tutor[/TD]
[TD]Mark[/TD]
[TD]TotalMarks[/TD]
[/TR]
[TR]
[TD]Gender[/TD]
[TD][/TD]
[TD]Test,Y/N[/TD]
[/TR]
[TR]
[TD]MTG[/TD]
[TD][/TD]
[TD]Year12?[/TD]
[/TR]
[TR]
[TD]Group[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TutorGroup[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]StudentInactive[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AQAASBiologyGrade[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

works great, typical result being:

[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD]FrmShowResults[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]StudentName[/TD]
[TD]Title[/TD]
[TD]Mark[/TD]
[/TR]
[TR]
[TD]Doe, John[/TD]
[TD]DNA HW[/TD]
[TD]19[/TD]
[/TR]
[TR]
[TD]Doe, John[/TD]
[TD]Plant cells[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]Smith, Jane[/TD]
[TD]DNA HW[/TD]
[TD]17[/TD]
[/TR]
[TR]
[TD]Smith, Jayne[/TD]
[TD]Plant cells[/TD]
[TD]20[/TD]
[/TR]
</tbody>[/TABLE]

However, the head of department has asked me if I can modify the db to show all students / all assessments on the same form, like this -

[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD]FrmShowResults[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]StudentName[/TD]
[TD]Title[/TD]
[TD]Mark[/TD]
[TD]Title[/TD]
[TD]Mark[/TD]
[/TR]
[TR]
[TD]Doe, John[/TD]
[TD]DNA HW[/TD]
[TD]19[/TD]
[TD]Plant cells[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]Smith, Jane[/TD]
[TD]DNA HW[/TD]
[TD]17[/TD]
[TD]Plant cells[/TD]
[TD]20[/TD]
[/TR]
</tbody>[/TABLE]

Racked my brains trying to figure this out. Tried DLookup, but slow and there are over 60 assessments in the db.
Also tried adding copies of TblAssessment in the query builder, which worked, but I would need 60 copies of tblAssessment which would produce, I imagine, a huge SQL statement, with joins to each table. For example, just five copies of the AssessmentID table proudces the following SQL statement:

SELECT [TblResults redesign].StudentID, TblStudents.StudentName, [TblAssessment 1].Title, [TblResults redesign].Mark1, [TblAssessment 2].Title, [TblResults redesign].Mark2, [TblAssessment 3].Title, [TblResults redesign].Mark3, [TblAssessment 4].Title, [TblResults redesign].Mark4, [TblAssessment 5].Title, [TblResults redesign].Mark5
FROM TblStudents INNER JOIN ((((([TblResults redesign] INNER JOIN [TblAssessment 1] ON [TblResults redesign].Assessment1ID = [TblAssessment 1].Assessment1ID) INNER JOIN [TblAssessment 2] ON [TblResults redesign].Assessment2ID = [TblAssessment 2].Assessment2ID) INNER JOIN [TblAssessment 3] ON [TblResults redesign].Assessment3ID = [TblAssessment 3].Assessment3ID) INNER JOIN [TblAssessment 4] ON [TblResults redesign].Assessment4ID = [TblAssessment 4].Assessment4ID) INNER JOIN [TblAssessment 5] ON [TblResults redesign].Assessment5ID = [TblAssessment 5].Assessment5ID) ON TblStudents.StudentID = [TblResults redesign].StudentID
WHERE ((([TblResults redesign].StudentID)=1));

I feel sure there must be an easier way, but my Access/SQL knowledge doesn't stretch that far. I can find my way around VBA, although I am self taught, so a possible coding solution may help.

Thanks for any advice offered, even if it's 'no you can't really do that with a database', in which case I would pursue an Excel solution.

 

[Micron]

Suggest you forget that approach as it's a form of spreadsheet thinking and has no resemblance to relational database design. If he/she insists, the right tool for the job is Excel, but you will lose any of the power/convenience that a db provides.

If you can create a query to assemble records in a row fashion, then you are on the right path. I advise the creation of queries before forms since if you can't get the info you want, the form you built first is useless. If what you really want is an output of all students and all related data, then the best db tool for this is a report. Often, a main form (student) and subform (detail records) is the approach that is taken, but that's not what you've been asked for since the detail records only apply to the student who's displayed in the main form.

 

[Markthearm]

Hey, thanks for taking the time to respond.
Pretty much as I thought. They have used Excel in the past but not successfully, as you can imagine (corrupted data, formulae no longer linking correctly, not sharing the workbook, etc) because the person who put the workbook together didn't lock it down to minimise corruption.

Do you think it is poor db design to build a query that has 60 copies of one table related through different fields to another table, resulting in an SQL statement that is the length of a short story ?

At the end of the day, I think I will build a new mark book based on an Excel workbook, transfer the data over, and use code to lock it down / allow edits to prevent corruption. I will have to train them all in using a shared workbook, however, which shouldn't be too difficult.

Thanks again.

 

[Micron]

Do you think it is poor db design to build a query that has 60 copies of one table related through different fields to another table, resulting in an SQL statement that is the length of a short story.

IIRC, the max number of joins is 16, but yes - it would be bad design if you could. Still don't see what's wrong with using a report and eliminating those excel issues.

 

[xenou]

You might investigate what type of software is available specifically for managing marks. I don't know for sure though I'd imagine there many choices at all levels.

 

[Markthearm]

There is software available, sure, but they don't want to pay for it! Hence asking me...

 

[Markthearm]

Still don't see what's wrong with using a report and eliminating those excel issues.

Maybe, (with subreports?) although I don't know enough about how reports / subreports work, so I'll need to research if I can get the layout my boss wants using the reports approach. I might have a look at powerquery (which, again, I know nothing about at the moment) to see if there is a way to automatically, maybe through VBA, link or transfer the data from the db in to a predesigned wkbk, which will present the data in the layout he wants. There again it might be easier just to create a wkbk rather than using a db (but this goes against the grain for me!).

 

[Micron]

Didn't mean to imply you could do that format in a report. I meant that there's more flexibility in terms of grouping compared to forms, but sadly, someone seems to be suffering from Excelitis, which can be very debilitating and difficult to cure.

 

[xenou]

For what it's worth, typical (and pretty straightforward in Excel or Access):

StudentName	DNA HW	PlantCells
Doe, John	19	12
Smith, Jane	17	20

 

[Markthearm]

Yeah, straightforward in Excel (previously was an Excel wkbk) but not, for me, in Access. Each record is - (Primary key), Student, Assessment, Mark, so trying to get a students assessments and corresponding mark on the same line, exactly as you describe in the code window in your post (concatenating records, I guess), is beyond me, other than what I've described in my op.