Results 1 to 4 of 4
  1. #1
    gavlap is offline Novice
    Windows XP Access 2007
    Join Date
    Oct 2011
    Posts
    13

    Deleting rows in secondary table with no related row in primary table

    I'm not very experienced with Access but have created a db for my travel business, however along the way I've made a few errors which I'm trying to put right.

    One being that I never set enforced referential integrity between some primary and secondary tables. So for example I have a [Quotes_Table] which is the primary, which then has several secondary tables eg. [Flights], [Hotels], [Car_Hire] etc.

    Over the last year or so, quotes have been deleted, but this has left all the related data in the secondary tables and it's getting full of old data which I need to clear. Also it now won't let me enforce referential integrity because of this data in the secondary tables which doesn't relate to a quote.

    All the flights, hotels, Car_hire etc have a primary key [Quote_Ref] which then links it to the quote, so is there a way to use this to find rows in the secondary tables which doesn't have a related quote in the Quotes table?

    Hope this makes sense and hope someone can help.. thanks in advance.

    Gav

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    15,779
    I think it's time to remodel/update what you are using. With a year's experience, you should have a good understanding of your requirements. Time to redesign to get the database properly designed and supporting your business.
    You can't enjoy the workarounds (hoops you must jump through) to get meaningful information.

    A tip: Do not delete things physically. Have a boolean/Yes_No field in your table to identify "DeletedYesNo". Set the field to Yes to signify Logically Deleted, No, empty, Null indicates Active. You have to adjust quesies etc to respect the field , but it prevents the mess you have at the moment.

    You might try some UNMATCHED QUERIES looking for quoteNumber in your tables where there is no quoteNumber in the Quotes table.
    You'll still have to look at these "manually" to ensure you are looking at the proper things.

  3. #3
    John_G is offline VIP
    Windows XP Access 2003
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Hi -

    If you are familiar with SQL, something like this should work:

    Delete from SecondaryTable where Secondarytable.[Quote_Ref] not in (Select [Quotes_Table].[quote_ref] from [Quotes_Table])

    Replace SecondaryTable with the name of the table you want to clean up.

    You could probably paste that text into a query (SQL view), change the table names and have it run correctly.

    Back up your database first!!

    HTH

    John

  4. #4
    gavlap is offline Novice
    Windows XP Access 2007
    Join Date
    Oct 2011
    Posts
    13
    Thanks Orange and John, will give your ideas a try and see what works best.

    I know what you mean Orange about starting over. I have thought about it numerous times but it's just such a big job. Maybe one day I'll get around to it

    Thanks again and I hope you don't mind me coming back to this thread if I get any questions when trying out the ideas.

    Gav

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 10-06-2013, 01:54 PM
  2. Replies: 1
    Last Post: 11-20-2012, 03:31 AM
  3. Go To Record with related table Primary Key
    By jamiebull21 in forum Forms
    Replies: 3
    Last Post: 02-17-2012, 09:36 AM
  4. Replies: 11
    Last Post: 11-13-2011, 06:57 PM
  5. Replies: 2
    Last Post: 10-19-2011, 06:47 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
  •  
Other Forums: Microsoft Office Forums