Counting non-unique records in a field
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=10689
Printed Date: 05 May 2024 at 10:27pm
Topic: Counting non-unique records in a field
Posted By: redbar0n11
Subject: Counting non-unique records in a field
Date Posted: 29 Jul 2010 at 9:57am
|
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!! |
Replies:
Posted By: redbar0n11
Date Posted: 29 Jul 2010 at 10:09am
Here's a Preview Screen Capture to help visualize what's going on in the report. Thanks again!
|
Posted By: DBlank
Date Posted: 29 Jul 2010 at 3:33pm
| 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 |