MS Access- Table normalization and query design problems

Question

I have a tricky thing I'm trying to get working

I have a table that contains events, and 10 fields populated with ID Numbers of employees who attended, and a comment box for each one. I tried to create a query that uses a combo-box with the ID Number to Search for the events they attended, and display them in a form cleanly (IE without displaying other peoples, or having a large number of text boxes everywhere). I got it partially working but I could not figure out how to go any farther. I can't figure out how to separate out the fields by the people. I was toying with the idea of having the event listed say 10 times with one person per record but that would cause alota bloat.

Any ideas how to do this? Different formats/other approaches would be great as well. Thanks guys.

Answer 1

I would split the tables... have one that contains the event, with an eventID field as an AutoNumber. Then have another table called Attendance with three fields: eventID containing the ID of the event, employeeID, and Comment for the comment. This would then even allow you to create another table containing more info about the employee like first name and last name for use in reports.