merging almost identical records into 1 record while giving the sum of 1specific field

[rhalfar]

Here is the deal:

I do a left join on 2 tables : 1 table for Invoices ('Verkoop' ) And 1 table for Orders (Archief_Temp).
So I have a Table Verkoop with both my invoices with Artikels and Archief_Temp with the original Orders and the Artikels.
I have to calculate the service level of the deliveries to the customers, so I have to get the sum of the Quantities on the invoice and the sum of the quantities on the Order.

But when I calculate the sum of the quantities it get 2 times the value that I should get.
In this specific Case, I do a selection and I get 544 instead of 277.

Can someone please help.

@sunny_talwar , @swuehl 

[edwin]

you are definitely joining a one to many related tables.  i suggest you run this step by step so you can identify which join is multiplying your records.

for example add an EXIT SCRIPT in position 1, then reload and check the number of rows.  remove the exit script and add it to position 2, reload and check:

this will tell you if you are adding a 1 to many table - this will of course multiply your records.  if it does then one solution is not to join them but keep them is separate tables.  you will of course need to change their associative keys to prevent synthetic keys.
hope that helps

[rhalfar]

Hallo Edwin,

Thanks for your advice.

[edwin]

np

[rhalfar · Accepted solution]

"if it does then one solution is not to join them but keep them is separate tables"

This was the solution to my problem. I created 2 seperate tables which, with the right keys of course, automatically connected to the fact table.