Hi guys - thanks for your helpful tips on my last question, which is now all sorted (no pun intended)
Here's the jam
I was given a table with a list of customers, products, and the day they were purchased.
I hacked a "unique ID" - a text concatenation of the 3, so there would be an identifier to count the number of times each customer has purchased each product in a given year. The day is included because of duplicates in other fields (e.g. dispatch_date)
CUSTOMER_ID PRODUCT_ID ORDER_DATE [my "unique ID"]
1 2 01-JAN 1|2|1 [final digit is day of year, to keep it relatively simple!]
1 2 12-JAN 1|2|12 etc...
1 1 16-JAN
2 2 11-JAN
2 2 16-JAN
2 2 19-JAN
I have tried a using a field with a summary function, i.e. count of "unique ID", sorted by "unique ID" - i.e. I'd expect to get 2 orders for the first 2 rows, then 1, then 3. Instead I only get a running total (i.e. row number of the table).
What I really want to do is ExecuteSQL("SELECT DISTINCT COUNT(unique_ID) FROM MyTable GROUP BY unique_ID" ;"";"";"")
...have also tried adding WHERE unique_ID = ?, and the parameter Unique_ID to the ExecuteSQL statement.
All I get is a question mark.
I've added a row to get(lastODBC error) [pseudo code] but this is also empty.
Thanks in advance,