Excellent. Hope you made a backup before you started deleting.
You deleted commodities, which weren't linked to any companies. Check also for non-existing commodities probably linked to some companies.
Let's assume, you want to replace all those instances of same commodity with single one, e.g. with 'agricultural equipment' (CommodityID = SomeNumber0). You have to run an update query on all tables with commodity ID as FK, something like
and then to run a delete query on tblCommodities to delete abundant onesCode:UPDATE YourTable SET CommodityFK = SomeNumber0 WHERE CommodityFK IN (SELECT CommodityID FROM tblCmmodities WHERE Commodity IN ("Agricultural Equipment","ag eq","ag equip","Ag Imp","ag implements")) or UPDATE YourTable SET CommodityFK = SomeNumber0 WHERE CommodityFK IN (SomeNumber1,SomeNumber2,SomeNumber3,SomeNumber4,SomeNumber5)
You have o repeat this for all different commodities, or you create a table like tblCommodityAliases: CommodityID, CommodtyAiasID, and use it to make all replacements per linked table at one go.
Hey Avri -- That is precisely what I am working on, I just killed 1700+ duplicates in the commodity table and I am working hard on the remaining 8000+ records -- a lot of them are ok, but some need some fixing -- I use a combination of update queries and hand editing -- this database is a disaster -- one editor at some point added autos to the list of commodities as atoes... SIGHYou deleted commodities, which weren't linked to any companies. Check also for non-existing commodities probably linked to some companies.
Let's assume, you want to replace all those instances of same commodity with single one, e.g. with 'agricultural equipment' (CommodityID = SomeNumber0). You have to run an update query on all tables with commodity ID as FK, something like
and then to run a delete query on tblCommodities to delete abundant onesCode:UPDATE YourTable SET CommodityFK = SomeNumber0 WHERE CommodityFK IN (SELECT CommodityID FROM tblCmmodities WHERE Commodity IN ("Agricultural Equipment","ag eq","ag equip","Ag Imp","ag implements")) or UPDATE YourTable SET CommodityFK = SomeNumber0 WHERE CommodityFK IN (SomeNumber1,SomeNumber2,SomeNumber3,SomeNumber4,SomeNumber5)
You have o repeat this for all different commodities, or you create a table like tblCommodityAliases: CommodityID, CommodtyAiasID, and use it to make all replacements per linked table at one go.
TIM
Have you reviewed your commodities to get some "guesstimate" of the variations in spelling/naming?
Instead of doing things manually, you'll find that some queries could be helpful.(as you are finding)
If 1 of your goals is to get an authoritative Commodity table:
How many different spellings for "the same " commodity exist?
Which of the various names do you want/will be deemed the "official" name?
You can make a table for the "official name" and aliases. Update the name to the official name and record the aliases.
Since you're working on a copy, then record/document each of your steps and decisions and move forward.
Just some thoughts for consideration.
Good luck.
Orange:
Thank you so much -- excellent food for thoughts --
The goal is to have 1 name for each commodity, some have just 1 variance, or many, As I can I am using select queries to grab the variance that I can, otherwise, I am just scrolling through and using queries to change as needed. Eventually, each commodity will have a link to an "Industry" table so like all the farm equipment commodities will end up under Farming, and all the crops will be in Food Production -- etc
This is not going to be a quick project, but this db is a disaster, and unfortunately unuseable in its current state, so I'll just keep hacking away at it
Thanks for all the support and advice -- its excellent and I REALLY appreciate it
TIM