Duplicate records problem

[Former Member]

Hi everyone,

I'm having a a little difficulty resolving a problem with a repeating field causing duplication of data in a report I'm working on, and was hoping someone on here can suggest something to help!

My report is designed to detail library issues during a particular period, categorised by the language of the item issued. My problem is that on the sql database that out library management system uses, it is possible for an item to have more than one language listed against it (some books will be in more than one language). When I list the loan records excluding the language data field, I get a list of distinct loan records. Bringing the language data into the report causes the loan record to repeat for each language associated with it, so if a book is both in English and French, it will cause the loan record to appear like this:

LOAN RECORD NO. LANGUAGE CODE

123456 ENG

123456 FRE

So, although the loan only occurred once I have two instances of it in my report.

I am only interested in the language that appears first and I can exclude duplicated records from the report page. I can also count only the distinct records to get an accurate overall total. My problem is that when I group the loan records by language code (I really need to do this as there are millions of loan records held in the database) the distinct count stops being a solution, as when placed at this group level it only excludes duplicates in the respective group level it's placed in. So my report would display something like this:

ENG 1

FRE 1

A distinct count of the whole report would give the correct total of 1, but a cumulative total of the figures calculated at the language code group level would total 2, and be incorrect. I've encountered similar results when using Running Totals evaluating on a formula that excludes repeated loan record no.s from the count, but again when I group on the language code this goes out of the window.

I need to find a way of grouping the loan records by language with a total count of loan records alongside each grouping that accurately reflects how many loans of that language took place.

Is this possible using a calculation formula when there are repeating fields, or do I need to find a way of merging the repeating language fields into one field so that the report would appear like:

LOAN RECORD LANGUAGE CODE

123456 ENG, FRE

Any suggestions would be greatly appreciated, as aside from this repeating language data there are quite a few other repeating database fields on the system that it would be nice to report on!

Thanks!

[Former Member · Accepted solution]

if you create a group by loan

then create a group by language

place the values in the group(loan id in the loan header)

you should only see the loan id 1x.

place the language in the language group you should only see that one time

a group header returns the 1st value of a unique id....

then in order to calculate avoiding the duplicates

use manual running totals

create a set for each summary you want- make sure each set has a different variable name

MANUAL RUNNING TOTALS

RESET

The reset formula is placed in a group header report header to reset the summary to zero for each unique record it groups by.

whileprintingrecords;

Numbervar X := 0;

CALCULATION

The calculation is placed adjacent to the field or formula that is being calculated.

(if there are duplicate values; create a group on the field that is being calculated on. If there are not duplicate records, the detail section is used.

whileprintingrecords;

Numbervar X := x + ; ( or formula)

DISPLAY

The display is the sum of what is being calculated. This is placed in a group, page or report footer. (generally placed in the group footer of the group header where the reset is placed.)

whileprintingrecords;

Numbervar X;

X

[Former Member · Accepted solution]

Thanks for the suggestion.

I have grouped the loans by loan record and can see that grouping the language only displays the first entry, but I need to group by language as the first group, as I need counts of loans by language - something like this (the loan records would be hidden for printing):

ENGLISH 4

LOAN1

LOAN2

LOAN3

LOAN4

FRENCH 3

LOAN5

LOAN6

LOAN7

I'm not sure therefore how the running total helps in this situation...

Using the grouping solution offered I imagine I could export the data into Excel and create a pivot table, were it not for the fact that my data set runs to approximately 1.2million loan records, so I need (and want) to carry out this grouping within Crystal.

Ultimately I need a count of loans during a specified period broken down by the (first listed) language of the loaned item, and I'm starting to think it's not possible to completely exclude the duplicated records from being counted when you are grouping on a repeating field like this...?

[Former Member · Accepted solution]

Ultimately I need a count of loans during a specified period broken down by the (first listed) language of the loaned item, and I'm starting to think it's not possible to completely exclude the duplicated records from being counted when you are grouping on a repeating field like this...?

if you need to count the number of loans by language then group by language

then in the detail section place the loan id and other data

if you have duplicate records then 2nd group should be loan id. it will return one value for each unique id

create a count formula

if not isnull(loanid) then 1 else 0

place this in the group header of the loan id

you can then sum this but the summary wizard sometimes accts for dups

use the manual running totals and in the calc formula place the count formula

the display should display the correct amount