Get count of all non-blank records of a specific field

[halfdome]

Hi,

I am trying to do something that seemingly should be very simple, but I cannot figure out how to accomplish it. 

 

I have a field that I am trying to have calculate a number based on certain properties of other fields. This field is called "groupID" and should display either "1", "2", or "3". Where I'm running into trouble is in defining the "1" or "2". Basically what the equation is in layman's terms is that when the total number of non-blank records in the "DonationMethod" field in the "Donors" Table is ≤ 45, I want it to display "1" in the "groupID" field; if the total number of non-blank records in the "DonationMethod" field in the "Donors" Table is > 45, I want it to display "2".

 

I know the Case function will work to evaluate the tests that give the results, but I can't seem to find the correct tests. I've tried the Count function, the ValueCount function, and a few others as my tests, but they are not giving me the results I need. They seem to only evaluate whether a value exists in the particular record I'm viewing in Form View, where I'm looking for it to evaluate the field in the entire "Donors" table and give me a count of all non-blank records. Thanks for any advice you can pass along!

[comment]

Basically what the equation is in layman's terms is that when the total number of non-blank records in the "DonationMethod" field in the "Donors" Table is ≤ 45, I want it to display "1" in the "groupID" field; if the total number of non-blank records in the "DonationMethod" field in the "Donors" Table is > 45, I want it to display "2".

 

In which table is this calculation field supposed to be - and what is the relationship between this table and Donors?

 

It would also help if you explained the purpose of this grouping. Right now it seems very peculiar.

[halfdome]

In which table is this calculation field supposed to be - and what is the relationship between this table and Donors?

 

It would also help if you explained the purpose of this grouping. Right now it seems very peculiar.

 

This database is a record of all our donors. There are four tables: Donors, Donations, Correspondence, and a join table called DonorCorrespondence. Everything I described in the OP is in the "Donors" table.

 

We like to send correspondence to each monthly donor every other month, and to accommodate this, I've created the Group ID field. Groups 1 and 2 are our monthly donors. So Group 1 would get correspondence one month and the next month Group 2 would get correspondence. Irregular donors will be in group 3. The idea behind the groups is that I could perform a find that looks for all donors from, for instance, Group 1 and any Group 3 donors in the last month to give me a list on who to correspond with on a particular month. 

 

We have around 90 monthly donors (that's why I want two groups of 45), but around 120 donors in the database. Using the "DonorID" serial number field in the Donors Table to calculate the groups won't work because the 90 regular and 30 irregular donors are intermixed throughout. So the thinking behind using the "DonationMethod" field in the Donors Table is that anyone who has an entry in this field is a monthly donor. If I could do a count of all non-blank occurrences of the "DonationMethod" field and run it through the Case function, I could have the "GroupID" field in the Donors Table automatically populate the group number for me. The first 45 would be Group 1, 46 and up would be Group 2, and all irregular donors would be Group 3. Sorry for the lack of detail in the first post, I hope this helps shed some light on what I'm looking to do. 

[comment]

Yes, that makes more sense. Let me suggest an alternative approach. Make cGroupID a calculation field (result is Number) =

Case ( DonationMethod = "Monthly" ; Mod ( DonorID ; 2 ) ; 2 ) + 1

This will divide the donors into three groups, with the monthly donors divided into two roughly equal groups, and the other donors forming the third. This calculation does not depend on data from other records, therefore it can be stored and finding in it will be fast. In addition, the assignment of a donor to a group is permanent (as long as their own status doesn't change); a method that depends on the count of other records in the same category could not guarantee that.

[halfdome]

That works great, what a clever solution! Thank you so much!

 

I'm a curious guy by nature, and after looking up the Mod function, I'm still trying to figure out what going on in " Mod ( DonorID ; 2 ) ; 2 ) + 1 ". Would you be so kind to explain what's going on in that so I can understand it and possibly use it for myself in the future, if need be? Thanks again for your help on this, I really appreciate it!

[comment]

The Mod() function returns the remainder of a division, so:

Mod ( number ; 2 )

returns 1 for odd numbers and 0 for even ones. Given a sequence of serial IDs like {1, 2, 3, 4, 5 ... }, applying mod 2 to each will result in {1, 0, 1, 0, 1 ... }. Which is effectively saying "every other soldier take one step to the left".

[halfdome]

Ok it all makes sense now. Super clever solution, thanks for your help!