Count conditions

Count conditions can be used in a query to find out information that applies to items that are linked to multiple records. For example, to find out which people are associated with more than two telephones, or which telephones are involved in multiple calls.

In the structure area, you can set a count condition on either a link or an entity. You can only set one count condition in the structure, and if the count condition is applied to an entity, it must have a single link. To set up a count condition, right-click an entity or link in the structure area and select Count.

Note: If you are using an SQL Server database, then you can use a distinct count when you want multiple links between two entities to contribute only 1 to the count.

Count conditions on entities

If you set the count condition 'more than N', on an entity in the query, then in the matching record group, there will be in total more than ' N' links from all the entities that match that structure entity, to any one entity that matches the structure entity at the other end of the structure link.

Note: If you turn on Distinct (only available in SQL databases), then multiple links between the same entities only contribute 1 to the count. So if, in the example above, an account has 11 transactions all from the same person, then turning on Distinct will exclude it from the results.

Count conditions on links

If the count condition 'more than N', is set on a link in the query, then in the matching record group there will be more than 'N' links between two entities that match the link end structure entities.

For example, if your structure contains:

query count example - telephones

You might set 'more than 15' on the link to limit the results to telephones that have more than 15 calls between each other. Each telephone in the results is involved in at least 15 calls with one other telephone in the results.

Setting count conditions in a query

  1. In the structure area of the query, select the entity or link.
  2. Click Set Count on Selected Item.
  3. Select Condition to set a count condition and select the type of count:
    Type Count must be...
    = Equal to the number you enter. You can only enter 0 as the count number on entities or links that are not set as the output.
    <> Less than or greater than the number you enter.
    < Less than the number you enter. The count number must be 3 or higher.
    Note: This condition does not include 'equal to 0'. For example, if you had a structure Telephone - Call - Telephone, with less than 3 on one of the telephones, the results would not include telephones that have made no calls. To find telephones that have made no calls, you would must explicitly use the condition equal to 0.

    Because you cannot output the entity with the condition equal to 0, make sure you set the telephone without the condition as the output.
    > Greater than the number you enter.
  4. Enter the actual count.
  5. If you are using an SQL Server database, turn on Distinct where you want multiple links between the same two entities to only contribute 1 to the count. For details, see below Using distinct counts in SQL Server databases.
  6. Click OK to confirm the changes.

Distinct counts in SQL Server databases

A distinct count is one that counts the number of different entities linked to a specific entity. In queries based on linked entities, a distinct count helps you determine how many different entities are linked to another.

As with other counts, you can look for relationships where the count is less than, equal to, or more than a number you specify.

For example, you may be interested in bank accounts where one account transfers money into only one other account, regardless of how many transactions take place. In another example, you might be interested in telephones that are used in calls with several other telephones, regardless of how many calls are made in total.

Consider this example:

Distinct count example - telephones

In the example above, 210 321 2134 is linked to three different telephones, so it has a distinct count of three. All the other telephones have a distinct count of one. The table shows the distinct and non-distinct counts for comparison.

Telephone Distinct count Non-distinct count
210 321 2134 3 8
876 352 0441 1 3
631 325 2833 1 1
520 325 2333 1 4
With these counts, you can see the effect of non-distinct and distinct count queries:
  • Working with an Access database, you can only specify a non-distinct count condition. A query based on searching for more than two telephones and counting repeated links to the same telephones is effectively a search for more than two telephone calls and would find 210 321 2134, 876 352 0441, and 520 325 2333.
  • Working with an SQL Server database, you can specify a distinct count condition. A query based on looking for links to more than two different telephones would find only 210 321 2134.
Note: A non-distinct count is shown with an asterisk, as in Count*.
To create a query involving a distinct count, turn on the Distinct check box.