Count records in subform meeting criteria

Posted on 2008-01-29
Medium Priority
Last Modified: 2013-11-28
I have a main form, with a sub form, linked by ClientID. I would like to have a text box in the subform to display the count of records within the subform that meet one criterion.

Main form - Clients, PK ID
Subform - Appointments, PK AppointmentID, FK ClientID

I would like the text box to show count of AppointmentID where Attendance = "Attended".
Question by:tmccrank
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 20774675
try placing this in the control source of the textbox

LVL 10

Expert Comment

ID: 20774944
You'll need to add criteria for ClientID matching
LVL 28

Accepted Solution

TextReport earned 2000 total points
ID: 20775940
In the subform put in a control in th form header or footer called txtCOUNT with a control source of =SUM(IIF(Attendance = "Attended",1,0))

Then in your main form create another textbox with a controlsource of =[SubformControlName].Form!txtCOUNT

Cheers, Andrew

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…
Suggested Courses
Course of the Month7 days, 11 hours left to enroll

607 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question