 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / General 1 / December 2005Removing duplicates from query, but not from table
I've been searching for awhile now on how to remove duplicates from a table within an Access db and have found plenty of articles on finding or deleting duplicates. All I want to do is remove them from within an SQL query - leaving one of the records behind of course. I have a mailing list comprised of a union query that gets records from two separate tables. I want to be able to run a query that removes one (or more) of the duplicated addresses within the query but doesn't make any modifications to the table. Please help! :) It doesn't really make sense to say you want to delete duplicate addresses from a query without modifying a table. A query is just a way of displaying what's in a table, so anything you delete via a query is deleted from the table. If you want you could put the results of your unioin query into a new table (make it a make table query), and then you could remove duplicates from that table (via a query) without modifying the original tables. hope that helped -John > I've been searching for awhile now on how to remove duplicates from a > table within an Access db and have found plenty of articles on finding [quoted text clipped - 8 lines] > > Please help! :) > I've been searching for awhile now on how to remove duplicates from a > table within an Access db and have found plenty of articles on finding [quoted text clipped - 8 lines] > > Please help! :) I'm not completely clear on what you are trying to do, but.. A UNION query will, by default, eliminate duplicate records between the member queries. If you wanted to include all records, regardless of duplicates, you would need to use UNION ALL. If you are getting duplicates from within the member queries, then what you need is the DISTINCT qualifier. SELECT DISTINCT field1, field2, field3 FROM table1 UNION SELECT DISTINCT field1, field2, field3 FROM table2 This syntax should eliminate all of the duplicates from you query output.  SignatureRandy Harris tech at promail dot com I'm pretty sure I know everything that I can remember. Thank you for your replies.... let me try to explain what I'm doing and maybe that will help me get the best of your expert advice. For example: Lets say I have two tables with names, addresses, etc: Personal table Work table I've got a Union query that merges the fields from Personal and Work together into one mailing list. However, there are people who belong on both the personal and work lists - but I don't want to send mail to those individuals twice. If each field in these records were exactly the same, then select distinct would work beautifully. Problem is, the dups might be like this: FirstName | LastName | HomeNum | HomeStreet --------------------------------------------- Jonah | Smith | 1983 | Dovecrest Lane (from work) Jon | Smith | 1983 | Dovecrest (from personal) So I would like to have a query that would deselect one of those lines based on the LastName and HomeNum fields matching up. AND, if possible, I'd like for the line that is kept to come from the Work table as that is updated more often. Is the combination of LastName and HomeNum the criteria that would always use to determine if the records were duplicates? SignatureRandy Harris tech at promail dot com I'm pretty sure I know everything that I can remember. > Thank you for your replies.... let me try to explain what I'm doing and > maybe that will help me get the best of your expert advice. [quoted text clipped - 22 lines] > possible, I'd like for the line that is kept to come from the Work > table as that is updated more often. In this case, yes. I don't know what you mean by "In this case". It would be rather easy to create a query that would do what you are asking for if that were the criteria. SignatureRandy Harris tech at promail dot com I'm pretty sure I know everything that I can remember. > In this case, yes. Randy, I meant yes -- I said "in this case" as I might want to change the criteria at some point. OK, how 'bout this? SELECT FirstName, LastName, HomeNum, HomeStreet From Table1 UNION SELECT FirstName, LastName, HomeNum, HomeStreet From Table2 Where LastName & HomeNum Not IN ( SELECT LastName & HomeNum From Table1) This should return a list inclusive of both tables but where LastName and HomeNum are duplicated, only the choice from the first table. I generally try to avoid using NOT In because it tends to be slow and inefficient, but there are occasions. HTH, Randy Harris tech at promail dot com I'm pretty sure I know everything that I can remember. > Randy, > > I meant yes -- I said "in this case" as I might want to change the > criteria at some point. I believe that did it!!!! Thank you!!!! >I believe that did it!!!! Thank you!!!! If Brad Johnson, who lives at 123 Sycamore is in your work table and Jane Johnson, who lives at 123 Honeysuckle is in your person table I hope you don't want to see Jane in the results of your query. In case I'm being too obtuse, let me put it differently: You should not have two tables. You should have one table and an extra field, which would be used to indicate whether the name is personal, work or both. Really. mike FWIW - Mike is absolutely right. I gave you a workaround but it doesn't correct the real problem. SignatureRandy Harris tech at promail dot com I'm pretty sure I know everything that I can remember. > I believe that did it!!!! Thank you!!!! Note to all: This message has been posted to at least one other newsgroup. SignatureRandy Harris tech at promail dot com I'm pretty sure I know everything that I can remember. > I've been searching for awhile now on how to remove duplicates from a > table within an Access db and have found plenty of articles on finding [quoted text clipped - 8 lines] > > Please help! :) I apologize if I have broken etiquette on that - I posted to both groups as I was not sure which one was the best one to get an answer -- clearly it is this one. > I apologize if I have broken etiquette on that - I posted to both > groups as I was not sure which one was the best one to get an answer -- > clearly it is this one. Both groups are excellent for getting answers. Many people monitor them both (and others as well). It can be irritating to spend time posting an answer only to find later that it had already been answered in another ng. Multiposting is generally considered poor netiquette. Much less objectionable is crossposting, that is posting the same message to 2 or 3 groups in a single message. That way if someone answers in one group, the answer will appear in the others. SignatureRandy Harris tech at promail dot com I'm pretty sure I know everything that I can remember. Again my apologies - I'll do it the right way next time. |
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.