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

Joined
Jun 5, 2017
Messages
1
Reaction score
0
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top