Same record showing twice on report

[AlvarHanso]

I am running a report for the payroll of the specific company. The report is grouped by Organizational Units, then further grouped by department. The report displays earnings for each employee in these departments. So it could show someones name twice, yet the record would be associated with a different earning code. I.E. Adam Majid,Reg,40 hours,PayAmount (then next line)Adam Majid,OT,10 hours,PayAmount. Well the report works fine except some of the Organizational Units have letters in their code (64a,45l, and so on). When these records are printed it shows the same line twice. I.E. Adam Majid,Reg,40 hours,PayAmount (then next line) Adam Majid,Reg,40 hours,PayAmount. This creates doubled subtotals and grand totals and essentially makes the report non-functional for these units. I have looked in the Db and it seems that there are no duplicate records, and the Reg and Overtime hours are correctly only entered once.

This is my selection statement:
{EPayHist.checkDate} in {?CheckDateStart} to {?CheckDateEnd} and
{EPayHistDetail.hours} <> 0.00 and
{EPayHistDetail.det} = "E"

We also have a preprocess SQL statement running each time the report is run:
UPDATE EPayHistDetail INNER JOIN EInfo ON EPayHistDetail.id = EInfo.id SET EPayHistDetail.cc1 = [EInfo]![cc1], EPayHistDetail.cc2 = [EInfo]![cc2]
WHERE (((EPayHistDetail.cc1)=""));

cc1 = Organizational Unit
cc2 = Department

Maybe the preprocess is creating two records? But there are no duplicates in the Access Db. It only does this for OU's that have a letter in the code. Any help or suggestions are very much appreciated.Let me know if you need any more info as well.

 

[kchaudhry]

Do you have the "select distinct recrods" option selected?

Kchaudhry

 

[AlvarHanso]

The select distinct records option is selected.

Amajid

 

[synapsevampire]

If you check more closely, you'l discover that they aren't true dupes, what you are experiencing is row inflation.

One of the colums in the row are not a dupe, and this is probably the result of the joins used.

Anyway, you can resolve this by using the MAX of the rows as what you base your aggregate functions, however you'll have to build manual aggregates, by adding in an additional employee grouping:

Employee Group footer formula:
maximum({table.value},{table.employee})

If you want to sum these for outer groupi9ngs, you'll need to use the 3 formula method:

Department Group Header:
numbervar MyTot:=0

Employee group footer formula:
numbervar MyTot:=MyTot+maximum({table.value},{table.employee})

Department Group Footer: //used for display
numbervar MyTot

Hope this helps.

-k