Data entry/edit in a junction table

[davegri]

One thing I keep wondering about is the edit versus insert problem. Is there something I need to do with the form to keep track of whether I need to update or edit a record?

Moving a name from left to right adds a record to the junction table.
Moving a name from right to left deletes the record from the junction table.

The DB will not keep a record of the cancellations.

[GrandpaEd]

Will one record show both Cancel and Present? If not, Create record and have one field to flag result, not two fields. Apply filter on form so the 'cancel' records are not displayed.

Yes, one record could show both Cancel and Present, that would result in an "extra show".

What I'm thinking about now is putting all Cancel records in a new table, say, tblActMemCan, with foreign keys to tblMeeting and tblMember, and a field like amcCancel (Yes/No). I'd also maintain the existing table tblActMemAtt, which contains the same foreign keys and a the amaPresent field. I'd remove the amaCancel field from this table.

I'm thinking that would simplify things considerably, but I'm a newbie so I could very well be wrong.

Thanks again for your help.

[GrandpaEd]

Thanks dave, I think I see that. That's the reason why I think I need to create another table for cancellations (see some of the above posts). Cancellations need to be tracked separately from attendance, because in this club members get slapped on the wrist when they don't show for meetings that they're expected to attend.

[June7]

I doubt it will simplify. Sounds like data entry would be more complicated and at some point will likely need to combine the two tables and that would involve a UNION query.

Same meeting/date as both 'cancel' and 'present' makes no sense to me.

[mike60smart]

Hi

Would it not be much easier to record those who do not attend?

[GrandpaEd]

Hi

Would it not be much easier to record those who do not attend?

Probably, yes. I should set it up that way.

[davegri]

MTM Schedule Events Cancellstion-davegri-v02.zip
OK, added table for cancellations, and a cancellation report.
When the user moves a name from right listbox to left a msgbox asks if the cancellation is to be recorded.

[GrandpaEd]

Sounds like data entry would be more complicated and at some point will likely need to combine the two tables and that would involve a UNION query.

Well, I'm not sure data entry itself would be more complicated. My user currently goes through two separate processes regarding attendance. First, she records cancellations before the meeting, then, after the meeting, she records actual attendance. It seems to me like neither of those steps changes as a function of having the data in one table versus having it in two. You are right about queries, though. There will be a need to look for cases where members don't attend and did not cancel as well as cases in which they canceled but attended anyway. Those queries are going to be quicker if both cancellations and attendance (present/absent) are in the same table.

On the other hand, keeping both fields in the same table will require that I figure out how to make the form/subform arrangement, or some other mechanism, work for me. I like the way davegri's approach works, so I'd have to figure out how to integrate the two approaches

Same meeting/date as both 'cancel' and 'present' makes no sense to me.

Sorry for the confusion. I'm sure that's my fault for not explaining things clearly.

The call for cancellations goes out before the meeting. Cancellations are accepted until a couple days before the meeting is held. My user estimates the number of members who will attend the meeting by taking the total number and subtracting the number of cancellations. She uses this estimate to order food and to ensure that sufficient tables and chairs are set up to accommodate everyone. The big idea is to minimize the number of extra meals they order -- and waste.

Actual attendance is taken at the meeting. A member is either there or not, regardless of what they may have said or didn't say before the meeting. No one is prohibited from attending the meeting if they had previously cancelled. Thus it is entirely possible that a member could cancel but attend the meeting anyway; they might forget they cancelled, they might have been sick but feel better, or any number of reasons. This happens every month.

The number of cases in which the value of "cancel" is inconsistent with the value of "present" on the same record is small, but it happens every month. The club actually bills members for lunch (about $20) if they don't show for a meeting but haven't cancelled, so these cases have to be tracked.

Thanks again for your support.

[GrandpaEd]

MTM Schedule Events Cancellstion-davegri-v02.zip
OK, added table for cancellations, and a cancellation report.
When the user moves a name from right listbox to left a msgbox asks if the cancellation is to be recorded.

Thanks! But I'm afraid I've botched my explanation of "cancel" and "present". I've tried to be clearer in post 23. Please have a look. The bottom line is that cancellations are recorded before attendance is taken. It makes no sense in this context to record a cancellation at the time attendance is recorded.

So, unless I choose to record cancellations in a separate table, I'm going to have to figure out a way to handle the "edit or insert" problem, perhaps in the context of the form/subform approach suggested by June7. I don't believe, dave, that your approach handles that, am I right?

[June7]

Maybe call field something like MealCount or PlansToAttend. Oh well, as long you and anyone else working with db know what 'cancel' really means.

[GrandpaEd]

Maybe call field something like MealCount or PlansToAttend. Oh well, as long you and anyone else working with db know what 'cancel' really means.

Fair points. The word "cancel" is part of the woodwork in that club, so I need to keep using it.

You my ask, as I have, what kind of outfit tolerates members saying one thing and blithely doing something else? Well, as I said, I've asked...

Oh, and there is another side to this coin: There are two types of members, "active" and "associate". Among other things, active members are assumed to be attending unless they cancel. Associate members are assumed to not be attending unless they declare that they "will attend". The same silliness about saying one thing and doing another happens on that side of the coin too.

In summary, unless someone else has a better idea, my plans are to restructure the database to put cancellations in a different table from attendance and to adapt davegri's form to record attendance. (Dave, I'll be back if I can't figure out how to do that!) I'm also going to think about making "Yes" the default value of attendance and have my user enter "No" where appropriate. I'll need to get my user's buy-in for that. She's used to doing it the other way...

Thanks to everyone for your help.

Ed

[ssanfu]

Interesting project, but I have a few questions.

Did you draw out the database on paper (whiteboard, cardboard, window,etc) BEFORE jumping on the computer? It really helps define requirements if you design on paper first.

Did you write down the "business" rules before jumping on the computer?



For instance: ("business" rules)
"There are two types of memberships: 1) Member and 2) Associate."
"A Member/Associate can have a status of Active or Inactive."

All active members are expected to attend ALL meetings.
All active associates are assumed to not be attending meetings.
If an active member is not able to attend a meeting, that member must call in/email/text their Cancellation at least X number of days before the meeting. (If not canceled, member will be charged for the meal)
If an active associate does not declare that they "will attend" at least X number of days before the meeting, they will not have a meal available to them.


I would have cancellations and attendance in the same table. Easier for reporting attendance/no shows.
There could be a button that adds ALL active members to a meeting. (easy to add 150 members)
Not sure what to do about associates - how many associates are there and how many regularly attend meetings?
(didn't add the other "hand slap things - or is it head slaps??)


Good luck on your project.

My $0.02....

[ArviLaanemets]

In table tblActMemAtt, instead of fields amaCancel and amaPresent, I'd use a single field amaStatus. E.g.:
amaStatus = 0 - the member canceled attendance;
amaStatus = 1 - the member attended the meeting (this is defaut value);
amaStatus = 3 - the member missed the meeting.

You use single Main form (meetings form)- continuous subform (ActMemAtt form) design. The control based on field amaStatus in subform will be combobox. OnChange event of Main form checks for new record, and when a new meeting is registered in Main form, it adds a record for every active member into tlActMemAtt with amaStaus = 1;

When the user cancels attendance, and this is allowed, the attendance status is set to "Canceled" (i.e. 0) in subform. When canceling is not allowed, it is reset to "Attended" (i.e. 1), or is set to "Missed" (i.e. 2) - as you prefer;

After meeting you set attendance status - for all members with amaStatus = 1 who didn't attend the meeting - to "Missed" (i.e. 2).

It would be a good idea to have in meetings table a field for meeting status (0 - unlocked, 1 - locked). When meeting status is set to locked - at least for subform - editing, adding and deleting records will be restricted.

You can use filter property of subform to list only records with amaStatus IN (1,2), and refresh the subform using subforms OnChange event.

[GrandpaEd]

Did you draw out the database on paper (whiteboard, cardboard, window,etc) BEFORE jumping on the computer? It really helps define requirements if you design on paper first.

Did you write down the "business" rules before jumping on the computer?

Important points, ssanfu. Thanks for making them. Some background might be helpful:

I've been working with this user -- who happens to be my sister-in-law -- on the "attendance problem" for over 4 years. We've progressed from a spreadsheet that wasn't much better than pencil and paper to what we have now -- a collection of workbooks, one for each month, where the workbook for month n+1 is linked to data in month n. Each workbook contains several sheets, linked together, to facilitate recording of attendance, cancellations, etc., and reporting of results; e.g., "no-shows". During this time, I've written several documents that she's reviewed and made countless modifications to the workbooks to reflect changes in what she wants to do and what the club wants to do. I understand the problem -- and the "business rules -- very well.

The current system of workbooks actually works pretty well, but we've been seeing an increasing number of weird errors in the data linkages as we tax the capabilities of LibreOffice. Moreover, my SIL has now taken on the task of tracking member details other than attendance. As part of that added responsibility, she inherited yet another spreadsheet with member names with no connection to the workbooks she's been maintaining to track attendance. It's obvious that it's now time to gather this data together in a database to ensure accuracy, avoid errors, and keep data entry as simple as possible.

I recently retired after spending over 30 years in the IT business. I understand my way around requirements, design, testing, SDLC, etc. very well. During my time in the business, I managed to completely avoid the subject of databases generally, which is why I'm here. My aim here was to focus sharply on a very specific problem that I'm having that goes to my low level of understanding of databases and Access. With the help of this group, I'm making progress, but I'm still not where I need to be yet.

I would have cancellations and attendance in the same table. Easier for reporting attendance/no shows.

I get this. I need to wrap my arms around how to build forms in Access to deal with what I'm calling the "insert or edit" problem. It's clear from what folks are saying here that this is entirely doable, I just don't know how to do it. I find the documentation around forms in Access to be a bit confusing on this topic.

There could be a button that adds ALL active members to a meeting. (easy to add 150 members)

Yes, but then I'd need a form to change the "Present" value to "No" for those who didn't attend. I also still have the "insert or edit" problem to deal with.

Not sure what to do about associates - how many associates are there and how many regularly attend meetings?

There are about 20 associate members of which 1 or 2 attend meetings from time to time.

(didn't add the other "hand slap things - or is it head slaps??)

LOL! Maybe if a few head slaps were issued, we could simplify the requirements!

Good luck on your project.

My $0.02....

Thank you for your interest and ideas!

[GrandpaEd]

In table tblActMemAtt, instead of fields amaCancel and amaPresent, I'd use a single field amaStatus. E.g.:
amaStatus = 0 - the member canceled attendance;
amaStatus = 1 - the member attended the meeting (this is defaut value);
amaStatus = 3 - the member missed the meeting.

I don't think this will work. How would I identify members who did not attend and failed to cancel in advance?

You use single Main form (meetings form)- continuous subform (ActMemAtt form) design. The control based on field amaStatus in subform will be combobox. OnChange event of Main form checks for new record, and when a new meeting is registered in Main form, it adds a record for every active member into tlActMemAtt with amaStaus = 1;

When the user cancels attendance, and this is allowed, the attendance status is set to "Canceled" (i.e. 0) in subform. When canceling is not allowed, it is reset to "Attended" (i.e. 1), or is set to "Missed" (i.e. 2) - as you prefer;

After meeting you set attendance status - for all members with amaStatus = 1 who didn't attend the meeting - to "Missed" (i.e. 2).

It would be a good idea to have in meetings table a field for meeting status (0 - unlocked, 1 - locked). When meeting status is set to locked - at least for subform - editing, adding and deleting records will be restricted.

You can use filter property of subform to list only records with amaStatus IN (1,2), and refresh the subform using subforms OnChange event.

This helps me understand what I can do with forms. Thanks very much! I get your idea of locking records, but that wouldn't work for this club. Members are simply not disciplined about windows for cancelling. If someone calls to cancel after the deadline, my SIL calls the caterer anyway. Sometimes it works, sometimes it doesn't.