Countif function

[Tazmaniac]

Hi there



I am about to start a new Access project and would like some advice and assistance with a formula to get this pulled of.

I will be creating the following tabels:
1. Projects - Containing details of Project X, Project Y and Project Z
2. Learners - People who will be enrolled on the projects
3. Junction table for the above including a Learner Status field which will be a combo box with the values: Active, Terminated and Non Starter

What I want to do is be able to count per project the number of Active, Terminated and Non starter learners.

I assume I will have to do this in a query and then use an unbound text box to display the numbers on the Project form.

Will I be able to do the above and what formula would I use?

Everytime I add a new project, load new learners and link them to the projects I obviously want all of the above automatically be calculated and displayed for the new project.

Hope what I tried to explain makes sense.

[isladogs]

Create a query joining the three tables.
Then do one of the following
1. Use a DCount formula to get the number of students in a project
2. Click the totals button in the ribbon, group by project and count the students

Either will work for an individual project.
The second method will count each project.

The value will go in your bound textbox.

Alternatively, do a list count for the listbox containing student names.
The choice is yours....

[Tazmaniac]

Thank you for the feedback. Must admit I struggled a bit with that one and eventually did the following which seemed to have worked:

Created a query with the details from my Project Table including my junction table where my learner status field sits.
I then created 3 expressions with the following formula Sum(IIf([LearnerStatus]="Active",1,0)) (1 for counting Active, 1 for counting Terminated, 1 for counting Non Starter)
From the query I created a form including the fields which counts the Active, Terminated and non starter learners.
On the form I created a inserted a subform (datasheet) based on the junction table.

The idea is to in the subform update the learner status but this does not update the Active, Terminated and Non starter fields that sits on the form (from the query). The changes only updates when I exit the form and enter the form again.

What could be the reason for this? Is there something I have to do in order to make those textboxes update as I update the learner status in the subform?

[isladogs]

In the after update event on your subform add code to requery the main form.

Parent.Requery

Or if you prefer, requery the individual controls

Parent.Active.Requery

etc

[Tazmaniac]

Parent.Requery works perfect!
Just a snag I picked up, when I open my form and update the learner status then the text boxes updates accordingly.
When I go to the next record (aka project) on my form to update the learner status on a different project, once I update the status and click somewhere else in the datasheet then it jumps to the previous record (aka previous project) I updated.

[isladogs]

Use Parent.Recalc instead

[Tazmaniac]

Unfortunately not, back to square one. Only updates if I close the form and open it again.
After I update records in the subform and closes it access asks me to save the updates. This happened with the previous formula as well.

[isladogs]

Recalc only works if there is a calculation involved which I thought might be the case

In that case revert to requery but before doing so note the current record number using VBA
After the requiery use DoCmd.GoToRecord code to return to the same record

I'm out for the rest of the day but if you don't know how to do either of those, hopefully someone will step in...or Google it

[Tazmaniac]

Thanks a million for all the help. I will try this and see how it works.