Page 2 of 2 FirstFirst 12
Results 16 to 21 of 21

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

  1. #16
    isladogs's Avatar
    isladogs is offline Very idle programmer
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    3,823
    Excellent. Hope you made a backup before you started deleting.
    Colin (Mendip Data Systems) : Website, email
    If this has helped, please click the star button and leave a comment

  2. #17
    taholmes160 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2018
    Location
    Lester, Ohio
    Posts
    60

    Backup

    Quote Originally Posted by isladogs View Post
    Excellent. Hope you made a backup before you started deleting.

    Yep, Ive got a couple

    TIM

  3. #18
    Join Date
    Apr 2017
    Posts
    996
    You deleted commodities, which weren't linked to any companies. Check also for non-existing commodities probably linked to some companies.

    Quote Originally Posted by taholmes160 View Post
    ... 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
    Code:
    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.

  4. #19
    taholmes160 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2018
    Location
    Lester, Ohio
    Posts
    60

    Updating in progress

    Quote Originally Posted by ArviLaanemets View Post
    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
    Code:
    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

  5. #20
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,329
    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.

  6. #21
    taholmes160 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2018
    Location
    Lester, Ohio
    Posts
    60

    Fixing Commodities

    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

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 11
    Last Post: 03-17-2015, 05:10 PM
  2. Replies: 3
    Last Post: 07-29-2011, 09:30 AM
  3. Replies: 6
    Last Post: 07-10-2011, 05:33 PM
  4. Replies: 1
    Last Post: 06-08-2011, 04:43 AM
  5. Highlighting Items in List Box from Table Data
    By swalsh84 in forum Programming
    Replies: 2
    Last Post: 01-25-2010, 08:55 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums