sum sales 1

[codrutza]

CRXI
Help me with this. I’m stuck...
I made groups by location, conttype.
I have the location, sales for each job, in the table tsales. I make the link between tcont.jobno= tsales.jobno because I need to sum the teus of the same type (3X20ft=3 teus); each cont of 20ft has one teu, and the others have 2 teus. Everything is fine, but the sales, because the job appears twice, or more than once, the sales are added the same.
I don’t have the sales in other table.
I can’t add tables in the database.

Honolulu
JobNo ContName ContType Sales
20ft
15 S1 20ft 200
16 G1 20ft 400
64 E 20ft 300
3X20ft=3 teus
40ft
15 U16 40ft 200
83 G71 40ft 150
60 E3 40ft 500
73 T44 40ft 250
83 PG7 40ft 600
5X40ft=10 teus
40ftHQ
80 MS5 40ftHQ 100
1X40ftHQ=2 teus
45ft
80 AP6 45ft 100
80 AP30 45ft 100
2X45ft=4 teus

total 2900
total wanted 2900-200-100-100=2500

 

[lbass]

I think I'm following this except for the logic for job #83, which shows two different amounts and isn't adjusted for in the total. What is the logic?

-LB

 

[codrutza]

Oh, I'm sorry, there is 93 instead of 83 (93 PG7 40ft 600)

 

[lbass]

You can use Ken Hamady's solution for a distinct sum. First create a running total {#CountID} that does a distinctcount of ID, evaluate for each record, reset never. Then create the following formula and place it in the detail section and suppress it if you like:

WhilePrintingRecords;
NumberVar Prior;
NumberVar Tot;
If {#CountID} = Prior + 1 then
Tot:= Tot + {table.sales};
Prior:= {#CountID};
Tot

In the report footer, add this formula to display the result:

whileprintingrecords;
numbervar Tot

-LB

 

[codrutza]

Thanks, LB. It's working.