Removing Items from a table that have no data in another table

[isladogs]

Excellent. Hope you made a backup before you started deleting.

[taholmes160]

Excellent. Hope you made a backup before you started deleting.

Yep, Ive got a couple

TIM

[ArviLaanemets]

You deleted commodities, which weren't linked to any companies. Check also for non-existing commodities probably linked to some companies.

... in many cases, there were several commodities that were the same thing but with different spelling -- for example
agricultural equipment
Agricultural Equipment
ag eq
ag equip
Ag Imp
ag implements

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

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)

and then to run a delete query on tblCommodities to delete abundant ones

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.

[taholmes160]

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

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)

and then to run a delete query on tblCommodities to delete abundant ones

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... SIGH

TIM

[orange]

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.

[taholmes160]

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