Question Regarding Combining Duplicate Records

  • Thread starter Thread starter wwachsberger
  • Start date Start date
W

wwachsberger

I have a situation where I have quasi-duplicate data which looks like
the following:

Unique ID Name Location Color Revenue
1 A New York Green 100
2 A New York Green 200
3 A New York Green 300

There are about 10,000 examples of seperate duplicates like this within
my database. I have written a duplicate query in order to identify all
of these quasi-duplicate records. I now wish to combine all of the
duplicates in order to have the records look like the following:

Unique ID Name Location Color Revenue
1 A New York Green 0
2 A New York Green 0
3 A New York Green 600

I can then ignore the Revenues equal to 0. Many thanks for your help
in advance!

Warren

Reply »
 
I have a situation where I have quasi-duplicate data which looks like
the following:

Unique ID Name Location Color Revenue
1 A New York Green 100
2 A New York Green 200
3 A New York Green 300

There are about 10,000 examples of seperate duplicates like this within
my database. I have written a duplicate query in order to identify all
of these quasi-duplicate records. I now wish to combine all of the
duplicates in order to have the records look like the following:

Unique ID Name Location Color Revenue
1 A New York Green 0
2 A New York Green 0
3 A New York Green 600

I can then ignore the Revenues equal to 0. Many thanks for your help
in advance!
Click to expand...


Leave the data alone and use a query that provides the
desired result:

SELECT Max([Unique ID]) As ID, [Name], Location, Color,
Sum(Revenue) As TotalRevenue
FROM thetable
GROUP BY [Name], Location, Color
 
Thanks for all your help. This seems to work.


Marshall said:
I have a situation where I have quasi-duplicate data which looks like
the following:

Unique ID Name Location Color Revenue
1 A New York Green 100
2 A New York Green 200
3 A New York Green 300

There are about 10,000 examples of seperate duplicates like this within
my database. I have written a duplicate query in order to identify all
of these quasi-duplicate records. I now wish to combine all of the
duplicates in order to have the records look like the following:

Unique ID Name Location Color Revenue
1 A New York Green 0
2 A New York Green 0
3 A New York Green 600

I can then ignore the Revenues equal to 0. Many thanks for your help
in advance!
Click to expand...


Leave the data alone and use a query that provides the
desired result:

SELECT Max([Unique ID]) As ID, [Name], Location, Color,
Sum(Revenue) As TotalRevenue
FROM thetable
GROUP BY [Name], Location, Color
Click to expand...
 
Back
Top