12 Replies Latest reply on Mar 14, 2016 5:28 PM by Satish Kikani

    Count records where Name appears in one of two fields

    James Keuning

      I have this data which is culled from a huge log. I run some calculations to determine the time between certain events. Anytime the time is below a certain threshold I need to know the names of the users who were involved because it means that someone did something wrong. I do not know if it was the first person who went too late or the second person who went to early, and it does not really matter.

       

      So I simply want to get a count of how frequently a person shows up in these pairs. (I have a parameter to set the threshold.)

       

      One forseeable issue is whether I want to count records or appearances, in other words, if John is in both fields does that count as 1 or 2. I do not know the answer to that yet and I am interested in a calculation for each scenario.

       

      EDIT AFTER WORKING ON THE PROBLEM: I sort of solved this problem by using a Union query in Access, which put my Names and Prior Names in one column for easy calculating. The problem I ran into is that I also want to perform a LOD calculation which ignores the date filter and I cannot do this because the results of the union query are related on User Name, the data is not all coming from the same source.

       

      Does anyone have ideas of how to do this without a union query?