Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    wheddingsjr is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jan 2017
    Posts
    26

    How can I delete lines from a table based on another table?


    Hi all

    I have a table (Table 1 that consists of 5000 lines. I made another table (Tale 2) with the count of those lines that were similar. I want to delete all the lines in Table 1 where the sum total of those similar lines in Table 2 is less than 3. Any help would be greatly appreciated. I am attaching examples for your review.

    Thanks
    Attached Files Attached Files

  2. #2
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    I cannot download your files from my current location, but what is the relationship (common fields) between the two tables?
    How are they "matched"?

  3. #3
    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
    16,716
    I downloaded and opened your database, but have no idea what you're asking.
    sum total of those similar lines in Table 2 is less than 3
    Tables contain records. Related tables would have some sort of identified relationship.
    a Table has a primary key that uniquely identifies each record in that table.

  4. #4
    wheddingsjr is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jan 2017
    Posts
    26
    If you look at table 1, it has 5 columns. The first column is "STAR", the second column is "DOS" with 3 other columns with other data. On line 2, you will see that the STAR and the DOS is exactly the same as line 3. Line 4 and 5 are the same etc etc.

    Table 2 has 3 columns. The first and second columns are exactly like the columns in Table 1. Column 3 has the sum of the lines for a particular code that appear in table 1. I.e. Table 1 code 11111 has 2 lines. On Table 2 code 11111 is lited on one line but has 2 in the sum column which represent the 2 lines that are on Table 1.

    Make sense?

    Thanks




    Quote Originally Posted by orange View Post
    I downloaded and opened your database, but have no idea what you're asking.


    Tables contain records. Related tables would have some sort of identified relationship.
    a Table has a primary key that uniquely identifies each record in that table.

  5. #5
    wheddingsjr is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jan 2017
    Posts
    26
    Hi Joe

    The first two columns on both tables ae the same. Column 1 = STAR and Column 2 equals DOS.

    Thanks


    Quote Originally Posted by JoeM View Post
    I cannot download your files from my current location, but what is the relationship (common fields) between the two tables?
    How are they "matched"?

  6. #6
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    If I understand correctly, if you want to delete all records in Table1 where the [SUM] in Table2 is less than 3.

    BTW, "Sum" is a very poor field name because it is a reserved word in Access (as is "Count")

    Also, instead of "Table 1" (with a space), you should use "Table1" (without a space). You should never use spaces in object names.

    Try tihis
    Code:
    Option Compare Database
    Option Explicit
    
    
    Public Sub DelRec()
        Dim d As DAO.Database
        Dim r As DAO.Recordset
        Dim sSQL As String
    
        Set d = CurrentDb
    
        'open recordset on Table2 where sum<3
        sSQL = "SELECT Table2.STAR"
        sSQL = sSQL & " FROM Table2"
        sSQL = sSQL & " WHERE Table2.[sum] <3"
        sSQL = sSQL & " ORDER BY STAR;"
    '    Debug.Print sSQL
    
        Set r = d.OpenRecordset(sSQL)
        If Not r.BOF And Not r.EOF Then
            r.MoveLast
            r.MoveFirst
            Do While Not r.EOF
    
                sSQL = "DELETE *"
                sSQL = sSQL & " FROM Table1"
                sSQL = sSQL & " WHERE Table1.STAR = " & r("STAR")
    '            Debug.Print sSQL
                d.Execute sSQL, dbFailOnError
    
                r.MoveNext
            Loop
        End If
    
        r.Close
        Set r = Nothing
        Set d = Nothing
    
        MsgBox "Done!!"
    
    End Sub

  7. #7
    wheddingsjr is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jan 2017
    Posts
    26
    whoa....thats a bit too deep for me my man. The example I attached is a sample of what I was asking. My "real" tables are not set up that way...just a quick can anyone answer this question. Had no idea I would be reprimanded as if you gave birth to me or something...but thanks anyway....

  8. #8
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Sorry. Wasn't meant as a reprimand - it is just a lot of people use reserved words and spaces in object names. The Access gnomes can get very persnickety when reserved words and spaces are used.
    You could manually create a delete query
    Code:
    DELETE * FROM Table1 WHERE Table1.STAR = 11111 Or Table1.STAR = 22222 Or Table1.STAR = 33333
    and change the STAR number manually for each of the records in Table2 where the count less than 3.

    Without the real tables/info, that is the best I can do.

    Again, sorry. From now on I will refrain from answering any of your posts.



    Good luck with your project.

  9. #9
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Had no idea I would be reprimanded as if you gave birth to me or something
    Reprimand? I think you are taking things WAY too personally. He is telling you best practices for programming.
    Avoiding things like spaces and reserved words in your database objects is excellent advice, and ignoring that advice can lead to a world of problems, such as errors and unexpected behavior.

    Don't bite the hand that is trying to feed you!

  10. #10
    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
    16,716
    My "real" tables are not set up that way...just a quick can anyone answer this question.
    That is good news, but why not show the real table.

    Code:
    DELETE * from [Table 1]
    Where [table 1].STAR
    IN
     (SELECT QQ.STAR
    FROM [Table 1] as QQ
    GROUP BY QQ.STAR
    Having count(Star) <3);
    I'm not sure where your table 2 fits or why it exists since it can be deduced from a query on table 1
    Code:
    SELECT [Table 1].STAR, Count([Table 1].STAR) AS CountOfSTAR
    FROM [Table 1]
    GROUP BY [Table 1].STAR
    Having count(Star) <3;

    As JoeM said Steve is advising you on best practices. Your Access journey will be a little less hectic if you heed the advice.
    Good luck with your project.

  11. #11
    wheddingsjr is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jan 2017
    Posts
    26
    YES Joe REPRIMAND!!! And I will reiterate it. It's been my experience that most people come here when they lack sufficient knowledge of things and where they can get helpful advise from the people that do, not to be spoken down to because his or her level of expertise is inferior to someone else's. Also, I am aware of the best practices (not all but some), but that is not the point. A sample was added simply so the reader could have a better idea of what I was asking, it was not the holy grail. Even your "don't bite the hand that feeds you" comment is extremely elitist. This is a wonderful tool and I have been assisted on numerous occasions, but I, nor anyone else should have to be subjected to any form of disrespect. Not only that, who are you to tell me what I a taking WAY to personally? You know me as well as you know a whole in a donut...You don't get to tell others what and how to take things whether you think it to be way too personal or not…

  12. #12
    wheddingsjr is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jan 2017
    Posts
    26
    Hi and Thanks Orange

    I don't show the real table because it's confidential information that would violate HIPPA law. I substitute or re-create the table in a condensed manner so the reader has an idea of what I am working with. The two codes above looks as if hey would be vey helpful. Table two was crated because I thought there was a way to write code that would say something like delete all lines that appear in Table2 from Table1. That was my thinking....

  13. #13
    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
    16,716
    The DELETE query (code 1) should do the job.

    I have seen posts that indicate that using Access with HIPPA related data is not acceptable. I don't deal with HIPPA so can not say for certain.

    Also, micron(frequent knowledgeable responder) has some code somewhere that you can use to anonymize data. I'll see if I can find a link and update this post later.

    Update: This is the link to a randomizer discussion with micron.

  14. #14
    CarlettoFed is offline Competent Performer
    Windows 7 64bit Access 2013 32bit
    Join Date
    Dec 2019
    Posts
    255
    The solution is given by the following query:

    Code:
    DELETE DISTINCTROW [Table 1].*
    FROM [Table 1] INNER JOIN [Table 2] ON [Table 1].STAR = [Table 2].STAR
    WHERE ((([Table 2].sum)<3));
    but be very careful because once deleted data will not recover them anymore

  15. #15
    wheddingsjr is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jan 2017
    Posts
    26
    Steve

    You seem to be a very knowledgably individual and I respect that. If you have some helpful information I would be more than happy to accept it. But as my title under my name reads, I am but a Novice on this sight. In real life I have basic knowledge of MS Access just not the heavy involved stuff like your previous code post. I have been on other forums where that is the case with a lot of the "eggheads" and "know it all's, where it seems as their entire existence is to put those who are not as knowledgeable down and though it may not have been you intent, your response unwittingly came across as if you were admonishing me for my ignorance, as if to say why am I so stupid...that is how it came across instead of just helping me....regardless of JoeM's opinion on the matter.

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

Similar Threads

  1. Delete records based on antoher table
    By lmh329 in forum Queries
    Replies: 9
    Last Post: 07-05-2019, 09:06 AM
  2. VBA to split lines based on fields in table
    By Tom123456 in forum Modules
    Replies: 4
    Last Post: 10-17-2017, 04:56 PM
  3. Replies: 1
    Last Post: 10-10-2014, 05:58 AM
  4. Replies: 1
    Last Post: 04-23-2012, 10:40 AM
  5. Replies: 3
    Last Post: 10-23-2008, 08:43 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