I'm trying to figure out how to identify Former Customers. Therefore I'm trying to write a formula that checks the CustomerNo Field to determine if their Cust# is in there once, or more than once (Former customer).

So, I'm looking in our Invoicehistory table. Trying to sort thru all our invoices to check the Cust#'s for duplicates to indicate if they are brand new or not.

I'm creating a source code report to tell me where the customers came from. The source code data is in a separate Customer table. Customer table and InvHist table are linked thru the Inv#'s. I'm Picking a date range, and telling the report to return the source codes for that date range. It is grouped by Source Code. We are running summaries of how many Inv's and the sum of the Sales $ per Source Code Group. However, we need to identify how many of these Source Codes (Customers in Customer table) are actually Former Customers (Checking the Cust# in the InvHist Table and counting how many times it appears there).

I have everything made except for the Dupe check. I can't figure out a method for checking yet. I figure once I do figure it out, I would have to use a Var function to check the individual Cust#'s returned in each Source Code group, against the Dupe Check function that checks the InvHistory to see if that particular Cust is former or not.

Make Sense??? Please let me know if you have any suggestions or questions. Thank you very much!!

not sure i understand your set up but in theory i would create a view or stored proc. In that I would use the customer ID grouped and counted, join it in to your report on ther customerID. If the customerID count is >1 you have your repeat customer flagged