UPDATE and DELETE double entries

[DeteViJete]

Hello at all,

I have a question related to the Delete and Update Query.

The folowing table is given:

ContractID CarID Count Weight Earnings 

Explanation: In this table neither ContractID nor the CarID is the primary key. One example:
1 2 0.5 10 100
1 2 0.5 12 120
1 5 0.33 14 150
1 5 0.67 24 250

As you see the point of the table is to see how each car of each contract is divided. My aim is to merge those data with the same ContractID and CarID. From the example above it should result:

1 2 1 22 220
1 5 1 38 400


I was thinking to solve the problem in two steps:
1. Update Query (Sum all Count, Weight and Earnings with the same ContractID and CarID together and enter them in those fields):
1 2 1 22 220
1 2 1 22 220
1 5 1 38 400
1 5 1 38 400


2. Delete double entries (not the problem here)

So how to do the first step? Or is there another way to solve this problem?

I would be deeply grateful for any help. Thanks in advance.

BR,

Dete Vi Jete

[NeoPa]

A simple GROUP BY query is needed here. The data can stay as it is.

SELECT   ContractID,
         CarID,
         Sum([Count]) AS TotCount,
         Sum([Weight]) AS Totweight,
         Sum([Earnings]) AS TotEarnings
 
FROM     [YourTable]
 
GROUP BY ContractID,
         CarID

[DeteViJete]

Wow, thank you very much for the fast reply. I think I was thinking too complicated :).


Cheers,

Dete Vi Jete

[NeoPa]

Always a pleasure Dete.

Welcome to Bytes!