If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Extract 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 |
#2
|
|||
|
|||
If you click on the new query button, you will see that there is a wizard for
finding duplicates. "markx" wrote: 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 |
#3
|
|||
|
|||
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 "markx" wrote in message ... 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 |
#4
|
|||
|
|||
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 "Michel Walsh" vanderghast@VirusAreFunnierThanSpam wrote in message ... 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 "markx" wrote in message ... 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 |
#5
|
|||
|
|||
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 "Chaim" wrote in message ... 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 "Michel Walsh" vanderghast@VirusAreFunnierThanSpam wrote in message ... 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 "markx" wrote in message ... 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 |
#6
|
|||
|
|||
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 "markx" wrote in message ... 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 "Chaim" wrote in message ... 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 "Michel Walsh" vanderghast@VirusAreFunnierThanSpam wrote in message ... 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 "markx" wrote in message ... 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 |
#7
|
|||
|
|||
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 "Michel Walsh" vanderghast@VirusAreFunnierThanSpam wrote in message ... 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 "markx" wrote in message ... 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 "Chaim" wrote in message ... 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 "Michel Walsh" vanderghast@VirusAreFunnierThanSpam wrote in message ... 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 "markx" wrote in message ... 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 |
#8
|
|||
|
|||
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 "markx" wrote in message ... 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 "Michel Walsh" vanderghast@VirusAreFunnierThanSpam wrote in message ... 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 "markx" wrote in message ... 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 "Chaim" wrote in message ... 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 "Michel Walsh" vanderghast@VirusAreFunnierThanSpam wrote in message ... 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 "markx" wrote in message ... 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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Deleting specific records | [email protected] | General Discussion | 6 | June 22nd, 2005 11:35 PM |
DCount compare table.textfield to form.text field question | RNUSZ@OKDPS | Using Forms | 1 | March 11th, 2005 02:05 AM |
Design help, please | SillySally | Using Forms | 27 | March 6th, 2005 04:11 AM |
Counting specific records using a expression in a text box | Moche | General Discussion | 5 | February 22nd, 2005 12:33 AM |
querrying for a specific field | Bill Pratt | Running & Setting Up Queries | 1 | June 11th, 2004 02:37 PM |