Duplicate entries

[IceRuby]

Hi there

I need to compile a report for Profit Loss statement, it uses three tables tables GL entry (actual amounts + dates), Budget Entry (budget amounts + dates) and G/L Account (all gl accounts).

Database is on a SQL2000. The issue is that both actual and budget tables can hold zero, one or multiple entries for each gl account. This causes duplication of the tables last entry when there are more entries from in the other table.

Example
Gl Account Date Gl Entry Gl Budget
2108 25/6/03 $500 $300
" $100 $300**(duplicate)
$39 $300**(duplicate)

I have tried a full outer join but still have same issue.

Report was initially written with actuals as main report and budget amounts as subreports which works well but it seems that the subreport values cannot be graphed with the actuals.

Appreciate your thoughts this one has been driving me insane!!!!!

 

[Madawc]

I think you need to explain again what you are doing, I don't follow it. Are you grouping by Gl Account? If not, would this be the basis of a solution?

Madawc Williams
East Anglia, Great Britain

 

[Madawc]

Also which version of Crystal? It makes a big difference to the possible solutions.

Madawc Williams
East Anglia, Great Britain

 

[IceRuby]

Thanks for reply, using Crystal Version 9.0.

Sorry, I'll try to explain issue better, I am probably too close to problem.

I need to create a Profit and Loss report. It must contain actual costs and budgeted costs for each general ledger (G/L).

There are three tables
1. G/L account (holds static G/L info such as G/L code and G/L name)
2. G/L Entry (holds G/L code and all financial $ transactions)
2. Budget Entry (holds G/L code and all budgetted $ amounts)

All tables have the key of G/L Code which I am using to
link them. See below for example:-

GL ENTRY TABLE GL Account table BUDGET ENTRY TABLE
GL Code GL Code GL Code
(1 or many records) (1 record) (1 or more records)


When grouping by G/L Code, if there is only 1 record in Budget Entry table and many within GL Entry table the budget $ value is repeated for each of the GL entries.
e.g.
(many records) (1 record)
Gl Account Date Gl Entry Gl Budget
2108 25/6/03 $500 $300
" $100 $300**(duplicate)
$39 $300**(duplicate)

I need to eliminate the record duplication.

Hope this makes more sense.

Thanks in advance.

 

[Madawc]

You could do something like
if {Gl Budget} = Previous ({Gl Budget})

Use it to suppress either the field or the detail line.


Madawc Williams
East Anglia, Great Britain