Others

How to Display Multiple Participant Records in an Access Form

Rana GarciaRana Garcia Nov 3, 2026 868 views

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.

How to Display Multiple Participant Records in an Access Form
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.
Before you start

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.

Solution 1Recommended

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.

1
Open Query Design

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.

2
Enable Totals

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.

3
Set Grouping and 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.

4
Create the Form

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.

Create a Grouped Query as the Form's Record Source
Form Views: A Single Form view is ideal for deep-diving into individual participant details, while the Datasheet view acts like an Excel spreadsheet summarizing the entire roster.
Free Microsoft Office alternative

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.

Highly compatible with standard Microsoft Office formats including XLSX, DOCX, and PPTX.Powerful Spreadsheet features like PivotTables to summarize participant data without complex SQL queries.Lightweight installation with a user-friendly, familiar tabbed interface.Rich library of free templates for tracking attendance, cases, and claims.
microsoft office alternative - wps office

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.