(2) Left Outer Joins

[kpeak]

I have 2 tables with multiple records per employee. I am trying to read each table separately to sum specific records in each table. The sum of the values in 1 table will be added to gross wage and the sum of the values selected in the other table will be subtracted from gross wage.
Table 1
0001 90.76
0015 19.76

should give me $110.52

Table 2
0012 13.52
0013 1.53

should give me $15.05

What appears to happen is when the records are read from table 2 the last value from table 1 is added again so the table 1 sum ends up being 4 or 5 times more than it should be.

 

[MColeman]

It sounds like you may have a record from table1 joined with every record in table 2,

and every record in table 2, joined with every record in table 1.

I would use a subreport to get the second total and pass it back to the main report.

 

[lbass]

You could also use running totals. If you lay out your fields in the details section, you can observe how the fields are duplicating. For example, you might see:

empl# {table1.ID} {table1.field} (table2.ID} {table2.field}
1 1001 100 2001 15
1 1001 100 2002 10
1 1003 75 2005 5
1 1004 75 2006 10

2 1002 50 2003 30
2 1002 50 2004 15

The values in {table1.field} are repeating, so using the running total editor, you would select {table1.field}, sum,
evaluate on change of field ({table.1.ID}), reset on change of group (Empl#). After suppressing the details you would get:

empl# {table1.ID} {table1.field} (table2.ID} {table2.field}
1 175 40
2 50 45

-LB