Solved Sum an Access Form Field based on another fields value.

kengooch

Member
Local time
Today, 13:49
Joined
Feb 29, 2012
Messages
137
So I have a form built from a Cross Tab Query (qAccmpTotCounts) that counts all records for a given Event.
[tAccName] is the Event
[tEvAccCat] is the Category that the event falls under
[CountOftEvntID] is the total number of events the cross tab query calculated for each [tAccName]

Now, I need to total the I need to get a total of the [CountOftEvntID], where [tEvAccCat] will contain a letter "A".

I have tried creating an unbound text box to show me the total for all [tEvAccCat] A and another box for all of the [tEvAccCat] that equal N. I have tried multiple equations in the query builder. I tried, "=IIf([tEvAccCat]="A",Sum([CountOftEvntID]),Sum([CountOftEvntID]))", then I tried =Sum(IIf([tEvStfCat]="A",[qAccmpTotcounts],0)). None of what I am trying has worked. Any help would GREATLY APPRECIATED.
 
you can Count it directly from your table.

=DCount("1", "yourTable", "tEvAccCat like '*A*'")
 
you can Count it directly from your table.

=DCount("1", "yourTable", "tEvAccCat like '*A*'")
Wow... sometimes you can't see the forest for the trees!! This was very insightful on your part!! Thank you so much, it worked perfectly
 
should also be able to add a count column to your cross tab and assign as a rowsource
 

Users who are viewing this thread

Back
Top Bottom