AnsweredAssumed Answered

Keep Count of Distinct Values

Question asked by Joshua_1 on Feb 23, 2015
Latest reply on Feb 23, 2015 by philmodjunk

Title

Keep Count of Distinct Values

Post

Hi, I did not find my answer in the forum and was hoping that some one can help me. I have a table called Orders with a field called CustID. I would like to keep a count of how many times the CustID repeats in each record so that a table with the following values:

Record1  CustID=1

Record2 CustID=2

Record3 CustID=1

Will have a field that returns the following values

Record1 NumOfOrders=2

Record2 NumOfOrders=1

Record3 NumOfOrders=2

I can get a static answer if I write the following calculation in NumOfOrders:

ExecuteSQL ("SELECT Distinct COUNT (CustID) FROM Orders

WHERE CustID = '1'" ; "" ; ""  )

But I need it to be dynamic and look at the value of the CustID field of the record that it is on but I can not get it to work:

ExecuteSQL ("SELECT Distinct COUNT (CustID) FROM Orders

WHERE CustID = CustID" ; "" ; ""  )

What am I doing wrong? Please Help

Outcomes