Counting Duplicate Entries

[product26]

I am building a report for a company that works in classified ads.
The ads are stored in their own table, & I need to count the number of ads that are in more than one classification.

I was thinking that I could count any ads that have matching ad text fields & different 'orderid' fields. The orderid is a unique number for every entry. But I cannot seem to find a way to compare them and count each entry that is and has a matching record.

Any suggestions?

 

[lbass]

If the orderID is unique to each ad, then you can group on OrderID, and add the classification to the detail section. Then go to report->selection formula->GROUP and enter a formula like this:

distinctcount({table.classification},{table.OrderID}) > 1

Then insert a running total that does a distinctcount of {table.OrderId}, evaluate for each record, reset never. Place the result in the report footer.

-LB

 

[product26]

I was in a bit of a rush when i typed the original post.

What I have is a table called 'orders' in this table we have entries that are all given unique 'Order IDs'. Each entry in this table is a classified advertisement, in order for an ad to be placed in more than one classification, or multiple times in the same classification, it must be duplicated. This will assign a new unique Order ID to the entry.

So what we have are entries in the table 'orders' each having unique 'orderid' entry and some possibly having some with the same entry for 'adtext'

What I need to do is count the number of ads that have the exact same 'adtext' but different 'orderid'.

Lets say an ad ran 2 times in the Miscellaneous classification and 1 time in the Jewelry classification, because it has the exact same 'adtext' I would want it to count 3, on top of that there was another ad (different from the first one) that ran in 4 completely different classifications, the total count would need to be 7.

I cannot seem to compare the entries and count the ones that have the same 'adtext'.

Any help would be greatly appreciated.

 

[lbass]

I had it backwards. You should group on the adtext field, and then do a distinctcount of order ID in the group selection formula, as in:

distinctcount({table.orderID},{table.adtext}) > 1

You should NOT have an outer group on classification though.

-LB

 

[product26]

Thank you much! that did the trick!