• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 167
  • Last Modified:

Finding mild duplication and combining what is'nt duplicated

Hi there - I am not sure where to start so I am looking for some advice.  I have a database that holds all of my inventory, I would like to find out what pieces of information are duplicated and be able to copy certain things that are not duplicated.

Here is a visual

StockNo, Make, Model, ModelNo, Year, Options, Color, VIN, Pic1

So I want to find out all units that have the same Make, Model, ModelNo, Year, Options and Color these are considered identical units.
 
Then I want to find out if one of them has Pic1 = 0 if it does I would like to be able to click a check box on the webpage that will run a script that copies and renames pictures of the stockno with the stockno of the one that has pic's  ( I can figure out the renaming of the files script ).

Maybe I should post this in the DB section? Don't know.

D--
0
poulsborv
Asked:
poulsborv
  • 2
1 Solution
 
iboutchkineCommented:
Create a copy of yor table and set Make, Model, ModelNo, Year, Options and Color as unique key. Copy all the record from old table to new. DUplicate records will not be copied. Another alternative to run Access query to find duplicates. Go to wizaed and you will be prompted how to create this query
0
 
gregoryyoungCommented:
SELECT COUNT(*) as TotalRecords, Make, Model, ModelNo, Year, Options, Color FROM INSERTYOURTABLE GROUP BY Make, Model, ModelNo, Year, Options, Color

this will give you a list of all unique types including the count of items that meet the unique value ...

To weed through the ones that are duplicates simply do ..

SELECT COUNT(*) as TotalRecords, Make, Model, ModelNo, Year, Options, Color FROM INSERTYOURTABLE GROUP BY Make, Model, ModelNo, Year, Options, Color HAVING COUNT(*) > 1

this will only show you the ones with duplicates ... if Access doesn't support "HAVING" use the following

SELECT * FROM (SELECT COUNT(*) as TotalRecords, Make, Model, ModelNo, Year, Options, Color FROM INSERTYOURTABLE GROUP BY Make, Model, ModelNo, Year, Options, Color) as t where TotalRecords > 1

this will give you the list of duplicated items ... You could then easily in code iterate through these requerying the database for the records that are duplicated in each to do your pic = 0 comparisons to delete the associated duplicates ...

Greg
0
 
iboutchkineCommented:
Access does support Having
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now