Solved Sum an Access Form Field based on another fields value. (1 Viewer)

[kengooch]

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.

 

[arnelgp]

you can Count it directly from your table.

=DCount("1", "yourTable", "tEvAccCat like '*A*'")

 

[kengooch]

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

 

[CJ_London]

should also be able to add a count column to your cross tab and assign as a rowsource