Link to home
Start Free TrialLog in
Avatar of Ricky Nguyen
Ricky NguyenFlag for Australia

asked on

Help With Access Form

Hi Experts,

I've been pounding my brains for the past week but still can't figure this out. I need a form to keep track of students' attendance. Please refer to my sample data for which my current tables are built. Please also see other table relationships for a better perspective.

I can create my current form (see attached) but data entry with it is very unfriendly. Currently for each ClassProduct, you would have to select every student which belong in that class for each respective date and then ticking the attendance would complete the record keeping. However, as previously mentioned, every student along with the repeating entry of the same date for which the class is held can become very tedious and prone to error.

My aim is the get to the attached (Required Form) but I suspect my tables are not structured correct for me to do so.

Any help or nudge in the right direction would be greatly appreciated.

Thanks
Ric
Sample-Data.jpg
Current-Table-Structure.jpg
relationships.jpg
Current-Form.jpg
Required-Form.jpg
ASKER CERTIFIED SOLUTION
Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
You might also want to consider adding all of the students to the StudentClassStatus table as soon as you select a class date.  That way, you would not have to add each student individually, you would just do an append query to append all of the ClassID, StudentID, and ClassDate values to the table.  Since the subform would be bound by classID and ClassDate to the parent form, it would automatically display all of the students assigned to that class.

Then you just have to decide whether the default value for attendance field should be True or False.  Under the assumption that most of the students will be there every day, I would make probably make it true, but there might be other considerations associated with that.
Avatar of Ricky Nguyen

ASKER

Thanks Patrick, I'll give it a go right now.
Hi Patrick,

Attached is what you had in mind?

Ric
Patrick-s-Suggestion.jpg
In relation to the recent attachment would mean the rest of the way would include an append query  to transfer student id to the next class date as per fyed suggestion?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
"If you then automatically insert all of the students into the Attendances table with classProductID, MeetingID (or ClassDate), and a default Attendance value of true,"

Ok i get this part - ie use append query to automate transfer of students id to next classdate.

"then you can change the AllowAdditions property of subAttendances to No, since you won't have to add anyone."

I assume this is to maintain data integrity.
Many thanks to fyed and Patrick for your help. It's 4am in Aussie right now so i'm heading to bed.

Can I get back to you both tomorrow morning when I put it all together.

Ric
Thanks very much fellas. Much appreciated.

Ric