Help needed for better table structure (1 Viewer)

Hi guys,

I have a crude database with poor table structure for what I need and I'm not sure what the best way forward would be.

I have attached an image of my tables' relationships to show the current structure.

My problem is that for the tblAttendance > AttendanceDate these are different for each CohortID.

To get data into the DB and make a start I manually populated the AttendanceDate for each StudentID for one whole Cohort (approx 60 students). Going forward I will have approx 10 CohortIDs each with upto 60 StudentIDs and I can't be inputting AttendanceDates for 600 records.

So my question is, how do I properly link AttendanceDate to CohortID so that I only input the dates once per CohortID and it populates for each StudentID automatically?

I think I've been looking at it for too long in the poor structure and now I can't see how to fix it.

clatham,

It is always a good idea to start with a clear description of the "business" involved in simple, plain English. I recommend you provide such a description to clarify the processes and your meaning of Trust, Module and CoHort. From this description you can build a model and create some sample data in order to test/vet the model. A tested model can be a blueprint for your database.

Here's a link re stump the model.
Good luck.

jdraw,

My database is an attendance register for our students. Each student is linked to a TrustID (third party company) who get monthly reports on their students' attendance.

ModuleID is the class being taken by the students, such as Health & Wellbring
CohortID is the grouping of students who started at the same time.

For example:
StudentA, is linked to TrustABC, and started in Jan2018
StudentB, is linked to TrustABC, and started in Apr2018
StudentC, is linked to Trust 123, and started in Jan2018

The Jan2018 Cohort of students are taught four sessions weekly on a Friday between Jan2018 and Dec2019 (excluding holiday weeks).

The Apr2018 Cohort of students are taught four sessions weekly on a Thursday between Apr2018 and Mar2020 (excluding holiday weeks).

In tblAttendance the records are laid out as below:

StudentID ModuleID AttendanceDate AttendaceSession AttendanceCode
StudentA Health & Wellbeing 02/02/2018 1 P
StudentA Health & Wellbeing 02/02/2018 2 P
StudentA Health & Wellbeing 02/02/2018 3 P
StudentA Health & Wellbeing 02/02/2018 4 A
StudentA Health & Wellbeing 09/02/2018 1 L
StudentA Health & Wellbeing 09/02/2018 2 A
StudentA Health & Wellbeing 09/02/2018 3
StudentA Health & Wellbeing 09/02/2018 4
StudentC Health & Wellbeing 02/02/2018 1 P
StudentC Health & Wellbeing 02/02/2018 2 P
StudentC Health & Wellbeing 02/02/2018 3 P
StudentC Health & Wellbeing 02/02/2018 4 P
StudentC Health & Wellbeing 09/02/2018 1 A
StudentC Health & Wellbeing 09/02/2018 2 A
StudentC Health & Wellbeing 09/02/2018 3
StudentC Health & Wellbeing 09/02/2018 4


I will soon need to add the dates for a new CohortID - Oct2019, being taught on a Tuesday between Oct2019 and Sept2020 (excluding holiday weeks), but I don't want to have to type in the dates in between Oct2019 and Sept2020 for each Student who is in that CohortID, the dates need to link to the CohortID and then populate for each student attached to that CohortID.

Does that make sense?

It sounds very much like a part time college link course for secondary school students.

I would create a procedure to add 4 session dates to your table based on a specified start date and course day. The complication is needing to exclude holiday dates. To do that I would have a separate calendar table with every weekday date and a Boolean field TermDate. Set this true for dates during college terms.
So your procedure now needs to loop through and only add dates where TermDate field is true.

Good luck

The table structure doesn't break any glaring rules. But because I just helped you on this thread (https://www.access-programmers.co.uk/forums/showthread.php?t=301733), I know you need an additional field in tblAttendanceCodes.

Because you have multiple codes that designate present or absent, and you need to group those together, you need a field in that table to designate which codes are whiche. I suggest a Yes/No field called 'Absent'. That will do the function of the table I told you to use in the prior thread and get rid of that long expression you are using.

Thanks plog, yes I'm adding that new field to my table and will use yes/no as you suggest.
(I'd have posted on the same thread, but I thought it should be in the tables section for this question?)

isladogs, I think you're right and it would be similar to a college link course. Unfortunately I don't know anything about creating a procedure. I'll do a little research and see if I can sort it. If not, I may need to post again.

Thanks all.