Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1

    Question Unanswered: Deleting records from a viewed query/subquery

    I have a table of records where the user wants to check for duplicates. The duplicate is determined by only looking at the [item name]. Other associated fields may have different data. The user wants to see the duplicates, and select which one to delete, and delete it, leaving the other.

    I created this SQL query:

    Code:
    select *
    from tblmain a
    inner join (select [item name],count([item name]) as namecount
                from tblmain
                where Offer = [enter offer]
                group by [item name]
                having count([item name]) >1) b
      on a.[item name] = b.[item name]
    This works perfectly, except if you right click on a row, DELETE is grayed out, presumably because of this query having a join.

    I tried adding another column called "Delete?" and made it a boolean with teh idea that the user could change it to a "1" if they wanted to delete it and then run a delete query, deleting where "Delete?" = 1. But this doesnt work because it won't let me change that boolean field, probably for the same reason I cant delete data from the query.

    Is there a way around this? Or perhaps a better way to accomplish what the user wants?

  2. #2
    Join Date
    Oct 2011
    Posts
    16
    It looks like Itemname should not be duplicated? Why join the table to itself? I think you can just say (Sorry, in T-SQL):

    Select *
    From tblmain a
    Having count([item name]) >1


    That removes your joining problem.

    If the tablename should be unique, consider making it a primary key after the clean-up.

  3. #3
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1
    Quote Originally Posted by anonimitie View Post
    It looks like Itemname should not be duplicated? Why join the table to itself? I think you can just say (Sorry, in T-SQL):

    Select *
    From tblmain a
    Having count([item name]) >1


    That removes your joining problem.

    If the tablename should be unique, consider making it a primary key after the clean-up.
    You cant use HAVING without a GROUP BY. And the reason I have to self join is because I only want to check duplicates on the ItemName field, but I need it to return all fields for the duplicate item names.

    Having duplicates is part of the user's process, so I can't make it a primary key.

  4. #4
    Join Date
    Oct 2011
    Posts
    16
    I think you're close but you can't use, "Having," without an aggregation like count. You don't need to join to have all the fields included. Just this:

    Select

    [FieldA]
    ,[FieldB]
    ,[FieldC]

    From TableA

    Group By
    [FieldA]
    ,[FieldB]
    ,[FieldC]

    Having Count([FieldA]) > 1


    I don't think the count needs to be part of the Select Clause.

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    an alternative approach is to leave the query alone
    but instead handle the deletion via some VBA
    say put a button alongside each row, when the user presses that button execute some VBA which in turns executes some SQL to delete the offending ro
    it doesn't need to be a button you could say get the PK of the offendign row by clicking a control.

    providing you have the PK of the row to be deleted it shouldn't be a problem. naturally I probably wouldn't actually delete the offending row, or at least not immediately. I'd probably flag it for deletion and not show it unless requested. then delete it say a couiple of weeks later. the reason for using a falg. at some time someone is going to delete the wrong row. it will have happened by magic, because no one will actually admit they deleted it (so taking a note of the user id, computerID and date time is always an advantage in such situations.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •