ValueCount with multiple conditions
I am having trouble with a ValueCount function that I am trying to implement. Here's the situation:
Just added a "Company Holdings" table, which lists the positions held by a Company. In addition to the individual positions, there is a field "CompanyID" which establishes the relationship between a position and the Company. There is also a field "Ticker" which establishes the relationship between the position and its various characteristics (maintained in another table "Analytics"). So a sample entry in the Company Holdings table would look like this:
and so forth going back historically
What I need to be able to do is construct a ValueCount function that returns the position value if MULTIPLE conditions are met so that I can base various summary fields off the results. For example, I would like to be able to sum the values of the positions where Company Holdings::CompanyID = 1234 and where Analytics::Group = "Industrial." Or, I would like to be able to count the positions where Company Holdings::CompanyID = 3456 and where (Company Holdings::12/31/08 - Company Holdings::9/30/08) > 0
The variety of potential criteria combinations is virtually endless, so that poses another problem. Any help at all would be greatly appreciated.