How to Display Multiple Participant Records in an Access Form
Question details
The user wants to display comprehensive participant records, including names, case details, attendance totals, and planned days, in Microsoft Access forms and reports.

- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Creating a form or report to view and print aggregated data for individual participants based on query records.
- Observed behavior
- Currently, the Datasheet view displays each query record separately instead of grouping them by participant with aggregated totals.
Ensure your underlying data tables are properly linked by Participant ID and that you understand the difference between table fields and calculated query fields before creating aggregate functions.
Create a Grouped Query as the Form's Record Source
Use an aggregate query to summarize participant data into a single row per person, which can then be displayed in a form.
By default, standard queries might list every single event or attendance log for a participant as a separate row. By grouping your query, you force Access to calculate totals (like present days or planned days) and return only one comprehensive row per participant.
Open your Access database, navigate to the 'Create' tab on the ribbon, and click 'Query Design'. Add your participant and attendance tables to the workspace.
Add the required fields (Participant ID, names, case number, attendance, etc.) to the query grid. Click the 'Totals' button (the sigma icon) in the ribbon to turn on aggregation.
Leave the 'Total' row as 'Group By' for static fields like Participant ID and Name. For attendance or planned days, change the 'Total' row to 'Count' or 'Sum' depending on your calculation needs.
Save the grouped query. Go to the 'Create' tab, select 'Form Wizard', and choose your newly saved query as the record source. Select 'Single Form' to view one participant per screen, or 'Datasheet' to list them all.

Design a Grouped Report for Printing
Forms are optimized for screen viewing, while reports are best suited for printing participant records.
Looking for a Lightweight Alternative for Your Office Tasks?
While WPS Office does not include a relational database management tool like Microsoft Access, it offers highly compatible, robust alternatives for Word, Excel, and PowerPoint. If you manage participant data that doesn't strictly require a relational database, WPS Spreadsheet provides powerful pivot tables and data consolidation features for free.

Frequently Asked Questions
What is the difference between Datasheet view and Single Form view in Access?
Datasheet view displays multiple records in a row-and-column format similar to a spreadsheet, allowing you to see many participants at once. Single Form view displays one complete record (one participant) at a time on the screen, which is better for detailed data entry or review.
Can I use text boxes in an Access form to calculate totals?
Yes. You can add an unbound text box to a form or report and set its Control Source property to an expression, such as =Count([Attendance]) or =Sum([PlannedDays]), to calculate and display specific values on the fly.
Why does my query show duplicate participant rows instead of a single summarized record?
This happens if you haven't enabled the 'Totals' function in Query Design. By clicking 'Totals', you can group by the Participant ID and apply aggregate functions (like Sum or Count) to other fields, which condenses the data into a single row per participant.




