Page 1 of 3 123 LastLast
Results 1 to 15 of 34
  1. #1
    GrandpaEd is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Location
    Ft. Worth, TX
    Posts
    17

    Data entry/edit in a junction table

    Hello all,

    I'm new to Access and databases, so apologies if my question is in the wrong forum. In that case, mods please move it to the correct one.

    I'm attempting to build a database with some reports and forms to manage membership data for a club and to track attendance at their monthly meetings. I have several tables so far, but the gist of the problem I'm having right now can be captured by three tables. Here are these tables and their relationships:


    Click image for larger version. 

Name:	Tables.jpg 
Views:	20 
Size:	57.9 KB 
ID:	39946


    Essentially, tblActMemAtt is a junction table between tblMember and tblMeeting. I want to use that table to record attendance at meetings by members after the meeting, and to record cancellations, which occur -- and are recorded -- before the meeting. I've defined a composite index consisting of amaMemberID and amaMeetingID for which I don't allow duplicates.

    I've managed to enter attendance data for a month in tblActMemAtt by using the datasheet view, but it was painful, time-consuming, and error prone. (There are around 150 members in this club.) I'm building the system for someone who's not computer savvy and I need to find a simpler way to enter attendance data.

    If possible, I'd like to build a form where the user would pick a meeting date from a list, and the form would display all member names from tblMember together with the "Present" column from tblActMemAtt for each member. The user would then scroll through the list of names and place a check in the amaPresent column for each member who attended the meeting. After all the check marks have been placed (or while they are being placed), I envision records being inserted into or updated in tblActMemAtt. Note that their could already be records in tblActMemAtt because a "cancel" may have already been recorded for that member and meeting. (Sometimes members cancel and attend anyway.)

    I've looked all over for an elegant way to handle this, but I haven't found anything, so I thought I'd drop by here and seek the advice of those more experienced than I. Any help or ideas would be appreciated.



    Thanks in advance.

  2. #2
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    GenericMTM Simple Schedule Members.zip

    Have a look at the attached. Form has a dropdown to select member and 2 listboxes showing available and assigned.
    I think it meets your requirements, but it's for events rather than meetings, but that could easily be modified for your use.

  3. #3
    GrandpaEd is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Location
    Ft. Worth, TX
    Posts
    17
    Thank you for the quick reply, davegri.

    I'm not sure your form is exactly what I want, but I'll look at it carefully to see if I can adapt it to my needs. That will take me awhile as I come up to speed with form creation in general, properties, and related topics.

    In the meantime, I thought I'd clarify what I'm looking for in case I haven't been clear or in case you or others have additional thoughts or ideas. This clarification might just help folks understand just how far out to lunch I am as well <smiles>!

    Here is what I'd like my form to look like:


    Select meeting date Combo box
    Member name Present at selected meeting?
    Name 1 [ ]
    Name 2 [ ]
    . .
    . .
    . .
    Name 150 [ ]

    I envision the combo box containing a list of available meeting dates from tblMeeting. Based on the meeting date selected, the form knows the mtgID, the primary key of that table.

    The list of names comes from tblMember. Every member name needs to appear in the list. Hence each mbrID is known to the form.

    The values for "Present at ..." come from the junction table, tblActMemAtt. These will either start out "checked" or not (this is "yes or no" data) or they will correspond to a name with no associated record in tblActMemAtt.

    I'd like my user to be able to scroll through the list of names and place check marks in the "Present..." column for members who attended the meeting selected on the form.

    When the user finishes her process, then I want the form to insert records into tblActMemAtt when there is no record in that table corresponding to a given meeting date and member name and set the value of "Present" in that table correctly for the inserted record.

    If there is already a record in tblActMemAtt corresponding to a given meeting date and member name, I want the form to set the value of "Present" for that record correctly.

    Again, thanks for any help!

    Ed

  4. #4
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    mtm One Form - davegri-v01.zip
    Maybe this UI is more what you're looking for.
    I see no need for the checkboxes - if the meeting is listed, the member attended. Checkbox is redundant.

  5. #5
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi Ed

    You could have an After Update Event on the Combobox for Meeting Date selection which automatically adds all members with the Checkbox set as Attended for everyone.

    You can then set only those members that did not attend.

  6. #6
    GrandpaEd is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Location
    Ft. Worth, TX
    Posts
    17
    Hi again, davegri,

    I appreciate your quick responses. Thank you!

    I apologize, but I think I'm missing something. I suspect I haven't done a good job explaining what I want to do.

    I understand that I can select a date and a member, and, by doing so, I've already identified that member as having attended the selected meeting, so the checkbox is unnecessary. "Yes" can be the default value for this field in any new record added during the attendance recording process.

    In this case, my user returns from the meeting with a sign-in sheet where members literally sign a sheet next to their name indicating that they've attended the meeting. If I understand your approach correctly, then my user needs to select a meeting date and a member name, then click on "yes", say, for simplicity. There are a couple problems with this approach that I'm trying to avoid.

    First, there are around 150 members in this club, and, in any given month over 100 attend these meetings. That means the data select, name select, box checking process needs to be repeated over 100 times. If I present the data as I've described earlier, then there is one date select and no member select. The box checking process is simplified, with my user scanning the names and checking boxes appropriately. Note that with this user interface, the checkbox column is needed, because some members don't attend.

    Second, there will be records in tblAccMemAtt for some of the amaMemberID, amaMeetingID pairs when attendance is recorded. When this happens, the form must not add a record, but rather change the value of a field in an existing record. Using the pair select approach, if my user selects a pair that corresponds to a record already in tblAccMemAtt, Access will complain and my user (or the form) will have to find the record to update.

    This pairwise process is essentially the same one I followed using the datasheet view of tblAccMemAtt. It was time consuming to click through the pairs and I occasionally encountered a duplicate, then had to scan through the names to find the record to update. I think my user would find this approach unworkable. She'd want to go back to her existing Excel spreadsheet approach where she can see all the names and place the "checkboxes" quickly.

    I hope I've explained my concerns clearly.

    Thank you again for trying to help me.

  7. #7
    GrandpaEd is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Location
    Ft. Worth, TX
    Posts
    17
    Quote Originally Posted by mike60smart View Post
    Hi Ed

    You could have an After Update Event on the Combobox for Meeting Date selection which automatically adds all members with the Checkbox set as Attended for everyone.

    You can then set only those members that did not attend.
    Thanks Mike. Setting the non-attenders will be quicker than setting the attenders, but if I understand your suggestion correctly, I still need to scan through 150 names to find the 10 or 20 who didn't attend. Is that right?

    Also, what type of form do I use to display records (or fields) from all three tables appropriately? I suspect that's a dumb question, and I apologize. My knowledge is very scant at this point.

    Finally, I believe I still have the problem of insert records vs. edit records, depending on if some members "canceled" before the meeting (see my response to davegri).

  8. #8
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    The user's task would be greatly simplified if the DB is Post#2 had the meeting in the combobox and the members in the list boxes.
    Use the dropdown to select the meeting, then use the listboxes to record attendance. The left listbox would show all members not attending the meeting and the right listbox show attendees. Very quick and easy.
    It wouldn't be very hard to modify the exsiting DB for that.

  9. #9
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    MTM Schedule Events-davegri-v01.zip
    I found my example the other way around here. Events in dropdown, members in listboxes.

  10. #10
    GrandpaEd is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Location
    Ft. Worth, TX
    Posts
    17
    Quote Originally Posted by davegri View Post
    MTM Schedule Events-davegri-v01.zip
    I found my example the other way around here. Events in dropdown, members in listboxes.
    Ah, thank you! I think I understand what you're saying, so I'll scratch my head and figure out how to apply it to my data. That approach should be very quick and easy.

    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?

  11. #11
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,824
    Could use form/subform arrangement.

    Main form bound to tblMeeting, subform bound to tblActMemAtt with a combo or list box for members.

    Search for existing meeting on main form or go to new record row and enter new meeting. This can all be done without any code using intrinsic tools. The more 'user friendly' the more code.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  12. #12
    GrandpaEd is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Location
    Ft. Worth, TX
    Posts
    17
    Quote Originally Posted by June7 View Post
    Could use form/subform arrangement.

    Main form bound to tblMeeting, subform bound to tblActMemAtt with a combo or list box for members.

    Search for existing meeting on main form or go to new record row and enter new meeting. This can all be done without any code using intrinsic tools. The more 'user friendly' the more code.
    Thank you, June7. I'll look into the form/subform approach.

    I just thought of another option myself. I could just put the cancels in another table and avoid the problem completely. In that case, when my user is recording attendance, she's always adding records to tblActMemAtt. Does that pass muster here?

  13. #13
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,824
    What do you mean by 'put the cancels in another table' - deleting a record from tblActMemAtt and 'moving' info to another table? No, I do not advise that.

    Why would there be an attendance record if person never attended? Why should there even be a 'cancel' situation?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  14. #14
    GrandpaEd is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Location
    Ft. Worth, TX
    Posts
    17
    Quote Originally Posted by June7 View Post
    What do you mean by 'put the cancels in another table' - deleting a record from tblActMemAtt and 'moving' info to another table? No, I do not advise that.

    Why would there be an attendance record if person never attended? Why should there even be a 'cancel' situation?
    I meant create a new table into which I record cancellations for members for specified meetings.

    It is assumed that active members will attend meetings unless they cancel in advance. Meals need to be ordered and tables need to be set up to accommodate expected attendance. Advance cancellations help to sharpen the estimated attendance and to minimize extra food and its associated cost. As I think about this now, I'm not seeing any reason that cancels need to be recorded in the same table as attendance, especially if it makes things complicated for me <smiles>.

    If a person doesn't attend, there won't be an attendance record, but there may be a cancel record.

    Sometimes there is not a cancel record but there is no attendance record. These are called "no shows", and a slap on the wrist is indicated <smiles>.

    And, yes, sometimes there is a cancel record and an attendance record. These are called "extra shows". Again, a slap on the wrist is sometimes issued in this case.

    Thank you for your help.

  15. #15
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,824
    Will one record show both Cancel and Present? If not, Create record and have one field to flag result, not two fields. Apply filter in form RecordSource so 'cancel' records are not displayed.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Page 1 of 3 123 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. data entry/edit form
    By Jen0dorf in forum Access
    Replies: 5
    Last Post: 06-06-2016, 08:56 AM
  2. How to edit entry in table from VBA ?
    By fluppe in forum Programming
    Replies: 5
    Last Post: 07-14-2014, 08:32 AM
  3. Replies: 4
    Last Post: 02-04-2014, 10:41 AM
  4. Data entry form with three junction tables?
    By justgeig in forum Forms
    Replies: 5
    Last Post: 08-13-2013, 04:23 PM
  5. Replies: 4
    Last Post: 03-20-2013, 01:03 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums