I have an excel file that contains the fields: [Event ID], [Event Name], [Required Attendees], [Actual Attendees]

All of the names of the required and actual attendees are listed in one cell in the event record. The names are listed in this format: Doe, John H; Dow, Jane M;

I need to figure out a way to create a report of absent attendees for each event. I also need to create another report of people who were not required to attend an event, but attended anyway.



Here is an image of what I am trying to attempt:



I would imagine that this needs some combination of a split function and some type of join query? I'm relatively new to Access with no VBA experience, so this is a little beyond me.