 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / September 2005Extract records with a specific field appearing more than once in the DB
Greetings everybody, I'm quite new to Access and SQL - until now worked a lot on Excel and some VBA macros... I would like to write a query that will extract me all the records where one particular field (f. ex. family name) appears more than once through the database. Exemple: Name First Name Address Age Johnson Anne 47th Ave 40 Hill John 5th Ave 57 Johnson Adrian Wall Street 33 ... would like to extract the first and the third record (because the "name" field in these records (=Johnson) apprears more than once in the database) I'm also wondering what is the best way in Access to extract records based on their frequency (in one particular field), f. ex. extract records with the unique specific field, then extract all the records with the specific field appearing twice in the whole database, then three times and so on... Suppose it should be someting easy, but don't know where to find an answer. Have also some problem with the MS Access SQL synthax. Thanks for your help on this, Mark If you click on the new query button, you will see that there is a wizard for finding duplicates. > Greetings everybody, > [quoted text clipped - 22 lines] > Thanks for your help on this, > Mark Hi, Bring the table in the designer. Click the Summation button (the capital Sigma, a rotated M by 90 degree) to get an additional line in the grid. Drag field Name in the grid, keep the proposed GROUP BY. Drag it again, a second time, this time, change the GROUP BY to COUNT. Under this, in the criteria, type >1. You should then get the family name where there count (number of time they appear) is > 1. Hoping it may help, Vanderghast, Access MVP > Greetings everybody, > [quoted text clipped - 23 lines] > Thanks for your help on this, > Mark Following Michel's lead, the second part of your question would be answered by setting the Sort to Ascending. Michel's query will give you the counts (assuming you have a checkmark in the Show box), and you can sort on the count column. Good Luck! -- Chaim > Hi, > [quoted text clipped - 35 lines] > > Thanks for your help on this, > > Mark Thanks guys for your precious help! It worked exactly as you explained. May I also have another, accessory, question regarding the example from the previous post? In fact, with your method (at least as applied by myself:-)) I just receive someting like: Name Count of Name Johnson 2 Morgan 5 Kimberley 6 How could I elaborate further on this if I would like to just extract the double (and more) records and receive something like this: Name First Name Address Age Johnson Anne 47th Ave 40 Johnson Adrian Wall Street 33 (the record concerning "Hill John" will not appear because his family name appears only once in the database) Furthermore, how could I extract all the records where AT THE SAME TIME "Name" and "First Name" appear together more than once (if "Johnson Anne" appears twice, her record will be extracted). Thanks again for your comments! Mark > Following Michel's lead, the second part of your question would be > answered [quoted text clipped - 56 lines] >> > Thanks for your help on this, >> > Mark Hi, Save that query, say, under the name Q1. Make another query, bring Q1 and your original table, make a join between them, through their common field [Name]. Drag the desired fields in the grid. That is not more complex than that. :-) Hoping it may help, Vanderghast, Access MVP > Thanks guys for your precious help! > It worked exactly as you explained. [quoted text clipped - 86 lines] >>> > Thanks for your help on this, >>> > Mark Thanks! Didn't think about it! But does it also mean that there is no direct SQL query that will do the job without multiplying the quantity of the queries? And concerning the second question, regarding "concatenation" - could any of you give me some hint how to imagine the query that will extract the duplicate records, but based on two fields ("Name" and "First Name" together)? It's not that I'm soooo lazy that I don't want to find it out by myself:-), but I'm just afraid that for the time being I don't have enough basic knowledge of MS Access/SQL to bring the solution to the table without your support. Looking forward to your comments, Mark > Hi, > [quoted text clipped - 101 lines] >>>> > Thanks for your help on this, >>>> > Mark Hi, drag the second field in the first query, keep the proposed GROUP BY. In the second query, join now on both fields. You can do in one query, using "sub query". Basically, here, it is equivalent, but easier to maintain, doing it in two queries, no? You just cannot do it GRAPHICALLY in one query, but in SQL view, you could. Hoping it may help, Vanderghast, Access MVP > Thanks! > Didn't think about it! But does it also mean that there is no direct SQL [quoted text clipped - 121 lines] >>>>> > Thanks for your help on this, >>>>> > Mark |
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.