Input Form to display sessions on one page (1 Viewer)

Krayna

Member
Local time
Today, 22:50
Joined
Sep 24, 2020
Messages
601
Another component of this never-ending attendance system must allow staff to tick off who attended their sessions. Until now they have been using a Google form so I would like to replicate this somehow.
Generally they will fill in the sessions of that week starting with the week beginning entered at the top of form.

I envision a continuous form showing the days and then subjects on each day and then students registered for these sessions.
Two unbound controls will flag where students have attended and if there are any relevant notes to include. (This can quite simply be added to the session log with some VBA). My trouble is that I can't figure out how to best present the data to show the sessions of the week - something like the structure below.
staffAttendanceInput.png

Also enclosed database with my started (messed-up) form to look at!

Ps. This is the relationship between sessions - in case it is not simple to understand.
session_relations.png
 

Attachments

  • SessionStaffInputTest.accdb
    1.5 MB · Views: 147

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 22:50
Joined
Jul 9, 2003
Messages
14,455
It looks like your post has been missed so I am bumping it up the list so that it gets a second look..
 

GinaWhipp

AWF VIP
Local time
Today, 17:50
Joined
Jun 21, 2011
Messages
5,879
Hmm, not getting it. Do students sign up for these classes? Because it seem to me you pick the Staff Member and then the Session which should then show the Students signed up for that Session. You can then enter the date and check off those that are present.
 

Krayna

Member
Local time
Today, 22:50
Joined
Sep 24, 2020
Messages
601
No all the sessions are recorded on the database and can be viewed in a day-view or according to which staff member signed in.
I have been able to display a staff members sessions but not in the view I am looking for.
 

GinaWhipp

AWF VIP
Local time
Today, 17:50
Joined
Jun 21, 2011
Messages
5,879
So the Staff log on and see there Sessions? If so then why not show the Students attending that Sessions? Or do you just want to who all the Students?
 

Krayna

Member
Local time
Today, 22:50
Joined
Sep 24, 2020
Messages
601
I can do that but it got mixed up when trying to do sessions for each day on one form so they can just put in week beginning at top and all the sessions of week are shown with students, grouped by day.
 

GinaWhipp

AWF VIP
Local time
Today, 17:50
Joined
Jun 21, 2011
Messages
5,879
Well, linking your subform on Weekday so you're right that won't work. Since you are showing all the Students why are you linking those Forms? That said, if you want to store which Students attended sounds like you are going to need an update query. Also, the Sessions subform should not be on the Students subform. It's multiple Students to each Session so you need the Students on the Sessions form.
 

mike60smart

Registered User.
Local time
Today, 22:50
Joined
Aug 6, 2017
Messages
1,158
I would also suggest that both the Students & Staff tables are not yet normalised.
 

Krayna

Member
Local time
Today, 22:50
Joined
Sep 24, 2020
Messages
601
Not sure what you mean - student table only contains each students info and nothing duplicated; same with staff table - all info required for my form should be in the tables illustrated above.
Would you like a copy of the DB?
 

mike60smart

Registered User.
Local time
Today, 22:50
Joined
Aug 6, 2017
Messages
1,158
Not sure what you mean - student table only contains each students info and nothing duplicated; same with staff table - all info required for my form should be in the tables illustrated above.
Would you like a copy of the DB?
Hi
In ate attached the List on the Left are all of the fields for the Student Table.

All Other fields in the List on the Right should be in related tables.
 

Attachments

  • Student.JPG
    Student.JPG
    76.5 KB · Views: 145

Krayna

Member
Local time
Today, 22:50
Joined
Sep 24, 2020
Messages
601
I see but this is not related to my original question.
 

mike60smart

Registered User.
Local time
Today, 22:50
Joined
Aug 6, 2017
Messages
1,158
Not sure what you mean - student table only contains each students info and nothing duplicated; same with staff table - all info required for my form should be in the tables illustrated above.
Would you like a copy of the DB?
Hi
Which is the Form that you have created for the above process?
 

mike60smart

Registered User.
Local time
Today, 22:50
Joined
Aug 6, 2017
Messages
1,158
Hi
Your Form DefrmStaffLesson is constructed very wrong

The Main Form
is based on a qryStaffLessonDays which consists of 5 tables

Going by your Relationship Diagram this Form should allow you to select a Day of the Week - I would suggest that this would be based on the tblWeekday

You then have a Subform based on qryStaffLessonsByDay which consists of 10 Tables ???

Nested in this Subform is a subform based on qryStudentSessions based on 3 Tables

You need to look at this form again.

 

Krayna

Member
Local time
Today, 22:50
Joined
Sep 24, 2020
Messages
601
Yes you are right. But I really don't know the best way to do this - hence the confusion with the subforms and queries.
I think that using the qryStaffLessonsByDay as a good sample query to find the lessons for the relevant staff member is a good starter and then the qryStudentSessions should give a list of which students are registered for each session.
But as I said, I have no idea how to get it anywhere near my vision in the first post:(
 

bastanu

AWF VIP
Local time
Today, 14:50
Joined
Apr 13, 2010
Messages
1,172
@Krayna,
What you're attempting to do is not trivial, it will involve quite a bit of coding and getting the right sequence(s) right (such as running append queries to add the registered students for each attendance week, then running update queries\code, etc.).
I have been working a lot in the past with similar databases (see screen shot below) so I strongly suggest you lay down the database structure and have a good hard look at it. What is the difference between a term and a session? You define a class entity but you do not use it anywhere (not in the relationships), do you really needed. Once you go through that it should be easier to build your form. Just remember you don't need to bring all the related tables in the form's recordsource (query); you just need the actual bound table in which you want to store the data and all other controls can be made into combo boxes whose row sources are based on the related tables.
Screenshot 2021-07-21 102311.png


Cheers,
Vlad
 

Users who are viewing this thread

Top Bottom