By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,688 Members | 1,911 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,688 IT Pros & Developers. It's quick & easy.

UPDATE and DELETE double entries

P: 2
Hello at all,

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

The folowing table is given:


Expand|Select|Wrap|Line Numbers
  1. 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
Mar 16 '10 #1
Share this Question
Share on Google+
3 Replies


NeoPa
Expert Mod 15k+
P: 31,769
A simple GROUP BY query is needed here. The data can stay as it is.
Expand|Select|Wrap|Line Numbers
  1. SELECT   ContractID,
  2.          CarID,
  3.          Sum([Count]) AS TotCount,
  4.          Sum([Weight]) AS Totweight,
  5.          Sum([Earnings]) AS TotEarnings
  6.  
  7. FROM     [YourTable]
  8.  
  9. GROUP BY ContractID,
  10.          CarID
Mar 16 '10 #2

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


Cheers,

Dete Vi Jete
Mar 16 '10 #3

NeoPa
Expert Mod 15k+
P: 31,769
Always a pleasure Dete.

Welcome to Bytes!
Mar 16 '10 #4

Post your reply

Sign in to post your reply or Sign up for a free account.