Is anyone willing to help out a Biology teacher?

[schallpattern]

It’s been 10 years since I’ve last used Access so I’m one of those folks hoping an experienced user can quickly build me the basic database and I can take it from there.

My students have to do practical experiments. For each student, I have to record whether..... [TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Comp 1[/TD]
[TD]Comp 2[/TD]
[TD]Comp 3[/TD]
[TD]Comp 4[/TD]
[TD]Comp 5[/TD]
[TD]Date[/TD]
[TD]Teacher comment[/TD]
[/TR]
[TR]
[TD]Light microscope[/TD]
[TD]Yes[/TD]
[TD][/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD]13/09/17[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Slide of living cells[/TD]
[TD]Yes[/TD]
[TD][/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD]20/09/17[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Water potential determination[/TD]
[TD]No[/TD]
[TD][/TD]
[TD]Yes[/TD]
[TD]yes[/TD]
[TD]No[/TD]
[TD]25/09/17[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Solute potential determination[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Membrane permeability[/TD]
[TD]Yes[/TD]
[TD][/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD]05/10/17[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Enzyme activity[/TD]
[TD]Yes[/TD]
[TD][/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD]No[/TD]
[TD]10/10/17[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

a) it is completed after a session (ie on a date)
b) Whether each of the 5 competencies have been achieved Yes/No (a blank means that Competency cannot be assessed in that particular practical)
c) Any comments made by the teacher

NB an empty row means the student was absent and missed that practical

---

There are individual sheets like this for every student so there's a table like this containing some student details;


[TABLE="width: 500"]
<tbody>[TR]
[TD]Student name[/TD]
[TD]Tutor group[/TD]
[TD]teaching group[/TD]
[TD]Target grade[/TD]
[/TR]
[TR]
[TD]Will C[/TD]
[TD]12A[/TD]
[TD]DCo[/TD]
[TD]A*[/TD]
[/TR]
[TR]
[TD]John A[/TD]
[TD]12B[/TD]
[TD]BAv[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]Liz C[/TD]
[TD]12B[/TD]
[TD]KWi[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]Beth D[/TD]
[TD]12F[/TD]
[TD]DCo[/TD]
[TD]E[/TD]
[/TR]
</tbody>[/TABLE]

---

What I ideally need is

1. a database with each sheet for every student
2. a form for each student with all Tutor group details, etc, plus a list of all the practicals, which ones they have done and a record of which Competencies they have passed, failed or not done.
3. Ideally I'd like a query to search for any students that have missed a practical (and generate a report)
4. ...and a query for any students that are missing Comp 1 (plus report)
5. .........a query for any students missing Comp 2 (plus report), etc, etc.

Anyone who could quickly throw something together would be an absolute hero in my eyes. Teacher spend hours designing and filling record and it would save me tens of hours of work as well as provide a really effective system for monitoring these student records (and thus helping them get their Biology qualification).

Many thanks,

Schallpattern

 

[ranman256]

the experiment table layout needs to be a little differnt:

StuID,[Exercise], [comp#], [complete], [EventDate],comment
123, Light microscope, Comp1, yes,1/1/17
123, Light microscope, Comp2, no,1/1/17
123, Slide of living cells, Comp1, yes,1/1/17

tExercises table: (used to fill the tExperiment table)
-------------------
Light microscope
Slide of living cells
Water potential
etc...

then when you gather each students data together,
you can run queries to find missing, finished, etc.

 

[schallpattern]

That's definitely put me in the right direction. I am very grateful for your help, thank you, ranman256. I am always impressed by the generosity of forum members.

Schall

 

[Logit]

.
Some of the code :

Option Explicit


Sub ClrReport()
On Error Resume Next
Application.ScreenUpdating = False
With Sheets("Reports")
.Cells.Clear
.Range("A1") = "Student Name"
.Range("B1") = "Compentency Missed"
.Range("C1") = "#1"
.Range("D1") = "#2"
.Range("E1") = "#3"
.Range("F1") = "#4"
.Range("G1") = "#5"
.Range("H1") = "Date"
.Range("A1:J1").Interior.Color = RGB(79, 98, 40)
.Range("A1:I1").Font.Bold = True
.Range("A1:I1").Font.Color = vbWhite
.Range("A1:I1").Font.Size = 14
.Range("K2").Select
Application.ScreenUpdating = True
End With


End Sub

Download link: https://www.amazon.com/clouddrive/share/1YN3bSGmTcI4xs17h4F3MVSAZFiMvgL3iu7x2aUIBK5

 

[Joe4]

Logit,
That looks like Excel code, not Access code.

 

[Logit]

.
Yes it is. I needed something to do ...