Find record count

[Richard Corso]

I am trying to found out the number of times a customer has signed in the last 30 days. I have a table named “sign_in” and each customer has an ID number. Any help to point in the right direction would be appreciated.

[jdu98a]

I did some testing and found this to work pretty well.

In your "sign_in" table create a number calculation field like this:

Name: [color:gray]LastThirty

Formula: [color:gray]If ( [date_field] ≥ (Get ( CurrentDate ) - 30); 1; "" )

(In the storage options check "Do not store calculation results)

Now, back in your "customers" table create a relationship to the "sign_in" table like this:

[color:gray]customers::customerID = sign_in::customerID

(you'll of course need to make sure that when records are created in the "sign_in" table that they have a customerID value that matches a record in your "customers" table)

Now to get your count. In the "customers" table create a calculation field like this:

Name: [color:gray]Count

Formula: [color:gray]Count ( sign_in::LastThirty )

The count function will return the number of related records that contain a valid value in the "LastThirty" field. So, only those that are within the last thirty days. The small test I put together seemed to work well. Even when I manually changed the date/time on my computer, the correct count would appear after any kind of screen refresh (layout change, record load, etc) Let me know if that helps.

Edited May 4, 2010 by Guest

[Richard Corso]

This works great, think you. I was trying to write some long script that was not working out.