Results 1 to 14 of 14
  1. #1
    mulefeathers is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Oct 2009
    Posts
    39

    Find Duplicates Query

    Does anyone know if there is a way to modify the find duplicates query so it will delete the duplicates while keeping the original record.



    Say if I have a table that has 3 identical records I want to delete 2 of the 3 records.

    Thanks

  2. #2
    TheShabz is offline Court Jester
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    1,368
    Just make a new table.
    SELECT *
    INTO newTable
    FROM myTable
    GROUP BY someIDfield

  3. #3
    mulefeathers is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Oct 2009
    Posts
    39
    Upon looking at my question I was very unclear. I want to delete 2 fields from the duplicate row while leaving all other information. Can this be done.

  4. #4
    TheShabz is offline Court Jester
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    1,368
    Isn't creating a new table without duplicates the same as deleting duplicates from your current table? It's just easier IMO. To delete them you would have to use a multiple query macro or VBA. Why do things the hard way?

    Lets say you have the following table:
    1
    1
    1
    2
    2
    3

    Your resulting new table would be:
    1
    2
    3

    You can then just delete the old table and rename the new one. Further, I would investigate why it was possible to have duplicates in the first place (most likely normalization issue or query logic) and correct that to not even need to do something like this in the future.

  5. #5
    mulefeathers is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Oct 2009
    Posts
    39
    It would be the same except I don’t want to delete the entire row.

    Let’s say I have a table with 4 columns that looks like this

    1 1 1 1
    1 1 1 1
    1 1 1 1
    1 1 1 1
    2 2 2 2
    2 2 2 2
    3 3 3 3
    3 3 3 3

    After I am done I need it to look like this.
    1 1 1 1
    1 1
    1 1
    1 1
    2 2 2 2
    2 2
    3 3 3 3
    3 3


    I know this is the hard way but based on how the information is scrapped from the mainframe I don’t have much of a choice.

  6. #6
    TheShabz is offline Court Jester
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    1,368
    Now I understand what you want, but am confused about why you want it.
    In your resulting table, having:
    1111
    11
    11
    Gives you no more information than:
    1111

    What do you gain by keeping the other ones? The only thing I can come up with is that you are keeping them to see how many times it shows up. If thats the reason, theres a more efficient way of doing so.

  7. #7
    mulefeathers is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Oct 2009
    Posts
    39
    Because with the last two columns contain quantity and dollars when the report does a sum it adds the extra quantity and dollar amount. So what would be 1 row with a value of $10 turns into 3 rows worth $10 each. But I need to keep the first two columns. There are probably better way to do this and I am open to suggestions but the information comes from the mainframe to an excel file where sales people make adjustments then imported into access.

  8. #8
    TheShabz is offline Court Jester
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    1,368
    Can you explain why you need to keep the first two columns when they contain the exact same information as the first record with all four columns? A mistake people often make is treating a database table as a spreadsheet. I am asking to ensure this isn't happening as you would be not only be making your db needlessly larger, but also causing your queries to run longer.

  9. #9
    mulefeathers is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Oct 2009
    Posts
    39
    Looking at the picture:



    You can see the information circled in red the first, second, third, and fifth column is the same. I need to keep the first and fourth. As the report totals the information returns a total of 60 with a value of 2331.24. When in reality is one order of 20 value of 777.08. However I still need to be able to refference the part number and shortage on the report.

  10. #10
    TheShabz is offline Court Jester
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    1,368
    There we go. You dont have duplicates here. Your data isnt
    1 1 1 1
    1 1 1 1
    1 1 1 1
    its
    1 1 1 1
    1 1 2 1
    1 1 3 1
    These are not duplicates. This has to do with how your database tables are set up. That table should be split up into multiple tables. 1. Items (part number), 2. Parts (shortage), 3. Customer (which you probably have already) and 4. Sales. The sales table will be like the one you have here, but will be joined to all the other tables. Now, instead of giving the dollar value of an item and then try to delete all but one, you can give the dollar value of the individual parts that make up the item, then add them together (which is what i think youre trying to do here).

    Also, as a note, do not use any special characters ($) or spaces in your field names. It just leads to headaches when trying to program.

  11. #11
    mulefeathers is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Oct 2009
    Posts
    39
    I see what you are saying and I suppose I could break this down by running multiple queries and making new tables. But the table this is set up with is 20 columns and is created everyday by our mainframe. I will explorer your suggestion. Thanks

  12. #12
    TheShabz is offline Court Jester
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    1,368
    As this is a business function, and probably worth a bunch of money in knowledge, I would think it would behoove you to get together with your DBA or data architect and discuss what it is exactly that you need. Remember, your IS (information systems, which is still sadly a subset of IT in general) department is your friend.

  13. #13
    mulefeathers is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Oct 2009
    Posts
    39
    I am the IT department (so yes I am my friend). I understand .net more than I do access. Unfortunately corporate generates most of these reports for several divisions and they are 1000 miles away. Our managers have bestowed the honor of organizing this data to me.

  14. #14
    Rawb is offline Expert
    Windows XP Access 2000
    Join Date
    Dec 2009
    Location
    Somewhere
    Posts
    875
    Assuming the information generated by the mainframe is a spreadsheet (or similar) and not an Access database, I would recommend doing the splitting programatically with VBA code. Then it could be easily modified to allow you to import the daily file, split it apart, and file it in your database.

    We do something similar where I work. Every day we receive several Excel files that we import into our database. These files often repeat information in the same manner that yours does. We have a program that reads the Excel file, takes all of the duplicate information and rewrites it in proper (normallized) database format.

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

Similar Threads

  1. Find and Replace Query
    By randolphoralph in forum Queries
    Replies: 4
    Last Post: 03-17-2010, 07:25 AM
  2. Can't Find Misspelled Query Name
    By jhillbrown in forum Access
    Replies: 5
    Last Post: 03-11-2010, 02:28 PM
  3. Query to find lowest value
    By AccessNubie in forum Access
    Replies: 3
    Last Post: 11-30-2009, 07:54 AM
  4. Use query to find and sum data
    By kwelch in forum Queries
    Replies: 3
    Last Post: 11-23-2009, 04:26 AM
  5. Find Records Query
    By sullyman in forum Programming
    Replies: 1
    Last Post: 10-28-2009, 08:49 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