How to avoid Duplicate values .. but want the first value to added in group totals

[venu gopal sistla]

Hi,

I have 2 Tables with the following records.

1. Order Table
Order ID,

Order Date

Customer

Order Quantity

Order Value

1​

01/01/2017​

ABC

100​

1122
2​

02-Jan​

ABC

100​

1122
3​

03-Jan​

XYX

100​

1122
4​

04-Jan​

DEF

100​

1122
5​

04-Jan​

JKL

100​

1122​

2. Despatch Table (one-to-many despatches for a given Order ID is possible)

Despatch ID
Despatch Date

Order ID

Despatch Quantity

Despatch Value

101​

02-Jan​

1​

20​

224.4
102​

02-Jan​

1​

40​

448.8
103​

03-Jan​

2​

20​

224.4
104​

03-Jan​

3​

40​

448.8
105​

04-Jan​

3​

20​

224.4
106​

05-Jan​

3​

40​

448.8
107​

06-Jan​

5​

20​

224.4
108​

06-Jan​

5​

20​

224.4
109​

06-Jan​

5​

20​

224.4
110​

06-Jan​

5​

20​

224.4​

I

I created a query with LEFT JOIN Orders.OrderID = Despatch.OrderID and generating the following report with Duplicate Values Suppressed and Group on Order ID

The output generated is :

Order ID,

Order Date

Customer

Order Quantity

Order Value
DespatchID Despatch Date Despatch Qty Despatch Value My Remarks

1​

01/01/2017​

ABC

100​

1122​

101​

02-Jan​

20​

224.4
102​

02-Jan​

40​

448.8​

Sub Totals for Order ID: 1

200​

2244​

60​

673.2​

wrong order total, correct despatch totals

2​

02-Jan​

ABC

100​

1122​

103​

03-Jan​

20​

224.4​

Sub Totals for Order ID: 2

100​

1122​

20​

224.4​

all totals correct, bcoz there are no despatches

3​

03-Jan​

XYX

100​

1122​

104​

03-Jan​

40​

448.8
105​

04-Jan​

20​

224.4
106​

05-Jan​

40​

448.8​

Sub Totals for Order ID: 3

300​

3366​

100​

1122​

wrong order total, correct despatch totals

4​

04-Jan​

DEF

100​

1122​

Sub Totals for Order ID: 4

100​

1122​

all totals correct, bcoz there are no despatches

5​

04-Jan​

JKL

100​

1122​

107​

06-Jan​

20​

224.4
108​

06-Jan​

20​

224.4
109​

06-Jan​

20​

224.4
110​

06-Jan​

20​

224.4​

Sub Totals for Order ID: 5

400​

4488​

80​

897.6​

wrong order total, correct despatch totals
Report Totals

1100​

12342​

260​

2917.2​

wrong order total, correct despatch totals


My Query is :

How to exclude the repeated values in the group & report Totals