 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / May 2008finding non-duplicated records
Help! I have two tables, one is a "master list" and the other table has most of the files that is in the master list. Is there anyway to extract those files that are duplicated in both the master list and the other list while leaving the other files remaining? If anyone could help me, I would REALLY appreciate it. Thanks!!! Use the Find Duplicates query wizard. It will help you build a query that finds the duplicate records (not files) in the two tables. Then you can modify that into a delete query to delete the records from one of the two tables. After, of course, you make a backup copy of you data.  SignatureDave Hargis, Microsoft Access MVP > Help! I have two tables, one is a "master list" and the other table has most > of the files that is in the master list. Is there anyway to extract those > files that are duplicated in both the master list and the other list while > leaving the other files remaining? If anyone could help me, I would REALLY > appreciate it. Thanks!!! Thank you for replying, but I don't understand how this would work. I am using Access 2003, and I tried using the Find Duplicates query wizard, but it says it can only find duplicates in one table, and not both. Additionally, I have some duplicates within the table that I do not wish to disrupt, only those records that are present in BOTH tables. Can I still do this in 2003 and the Find Duplicates query wizard? Thanks!! > Use the Find Duplicates query wizard. It will help you build a query that > finds the duplicate records (not files) in the two tables. Then you can [quoted text clipped - 6 lines] > > leaving the other files remaining? If anyone could help me, I would REALLY > > appreciate it. Thanks!!! Doh! Sorry, I wasn't thinking clearly when I responded. The way to find the duplicates between two tables is to create a query that has a WHERE clause that is a subquery that looks for a match in the other table. So you need to use the field name or names that would determine if there are duplicates. It would be something like: SELECT * FROM MainTable WHERE KeyFieldName IN(SELECT OtherKeyField FROM OtherTable); SignatureDave Hargis, Microsoft Access MVP > Thank you for replying, but I don't understand how this would work. I am > using Access 2003, and I tried using the Find Duplicates query wizard, but it [quoted text clipped - 13 lines] > > > leaving the other files remaining? If anyone could help me, I would REALLY > > > appreciate it. Thanks!!! That sounds great. I have one other question. Where do I enter " SELECT * FROM MainTable WHERE KeyFieldName IN(SELECT OtherKeyField FROM > OtherTable)" ? I clicked on the new query button, then another window comes up that asks me to select "design view, simple query wizard, crosstab wizard, find duplicates query wizard, and find unmatched query wizard." Thanks for your help! > Doh! > Sorry, I wasn't thinking clearly when I responded. The way to find the [quoted text clipped - 23 lines] > > > > leaving the other files remaining? If anyone could help me, I would REALLY > > > > appreciate it. Thanks!!! Go into Design view. When you get the Select Table dialog, close it. Then in the upper left corner of your screen will be a button SQL. Click that and the designer will change to a blank screen. Put the code in there. Now, of course, you will have to change the table and field names to use your actual names. SignatureDave Hargis, Microsoft Access MVP > That sounds great. I have one other question. Where do I enter " SELECT * > FROM MainTable WHERE KeyFieldName IN(SELECT OtherKeyField FROM [quoted text clipped - 27 lines] > > > > > leaving the other files remaining? If anyone could help me, I would REALLY > > > > > appreciate it. Thanks!!! Thank you for your help. Actually what also worked was "Find unmatched query wizard." Thanks for all of your help!! It was greatly appreciated! > Go into Design view. When you get the Select Table dialog, close it. Then > in the upper left corner of your screen will be a button SQL. Click that and [quoted text clipped - 33 lines] > > > > > > leaving the other files remaining? If anyone could help me, I would REALLY > > > > > > appreciate it. Thanks!!! IF you want to return the records in Master List that are not in the other table (part one)AND the records in the other table that are not in the Master List (Part two). You can use the unmatched query wizard to do this for part one and again for part two. Then you should be able to use a UNION query of the two queries to get the result. If I've misunderstood what you want post back. John Spencer Access MVP 2002-2005, 2007-2008 Center for Health Program Development and Management University of Maryland Baltimore County > Help! I have two tables, one is a "master list" and the other table has most > of the files that is in the master list. Is there anyway to extract those > files that are duplicated in both the master list and the other list while > leaving the other files remaining? If anyone could help me, I would REALLY > appreciate it. Thanks!!! This is great, thanks. > IF you want to return the records in Master List that are not in the other > table (part one)AND the records in the other table that are not in the Master [quoted text clipped - 16 lines] > > leaving the other files remaining? If anyone could help me, I would REALLY > > appreciate it. Thanks!!!
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.