Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Distinct items from recordset
Hi
How to get distinct items from a recordset? Let's assume I have two columns returned from a database: countries and cities. I want to retrieve a list of unique country names. Kind regards IgorM |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Distinct items from recordset
Excel 2003
Data-Filter-Advanced-select your ranges and tick "Unique records only" On 23 Mar, 09:26, "IgorM" wrote: Hi How to get distinct items from a recordset? Let's assume I have two columns returned from a database: countries and cities. I want to retrieve a list of unique country names. Kind regards IgorM |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Distinct items from recordset
Igor,
Why don't you make the query return a distinct set? -- HTH Bob "IgorM" wrote in message ... Hi How to get distinct items from a recordset? Let's assume I have two columns returned from a database: countries and cities. I want to retrieve a list of unique country names. Kind regards IgorM |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Distinct items from recordset
Hi,
List in data in columns A & B The will treat the top row as a header row. Select the list of countries and then Data|Filter|Advanced filter Select 'Copy to another location' Check 'Unique records only' Click the icon in the 'Copy to' box and select a single cell to copy to Click the icon again. OK You now have a unique list of countries. I copied mine to column M so M2 is the first country and M1 is the header. Put this in N2 and drag down as far as required. =VLOOKUP(M2,$A$2:$B$100,2,FALSE) -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "IgorM" wrote: Hi How to get distinct items from a recordset? Let's assume I have two columns returned from a database: countries and cities. I want to retrieve a list of unique country names. Kind regards IgorM |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Distinct items from recordset
Its probably me not being too specific. I don't want to query the database
to restrict connections to database. I've been thinking about dumping values from a recordset to a collection and using the values as a collection key too. But is there a better way to do that - a buid-in unique filtering function. Użytkownik "Bob Phillips" napisał w wiadomości grup ... Igor, Why don't you make the query return a distinct set? -- HTH Bob "IgorM" wrote in message ... Hi How to get distinct items from a recordset? Let's assume I have two columns returned from a database: countries and cities. I want to retrieve a list of unique country names. Kind regards IgorM |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Distinct items from recordset
Surely, you are already querying the database to get a recordset? What I am
saying is to change the query to only return distinct records. -- HTH Bob "IgorM" wrote in message ... Its probably me not being too specific. I don't want to query the database to restrict connections to database. I've been thinking about dumping values from a recordset to a collection and using the values as a collection key too. But is there a better way to do that - a buid-in unique filtering function. Użytkownik "Bob Phillips" napisał w wiadomości grup ... Igor, Why don't you make the query return a distinct set? -- HTH Bob "IgorM" wrote in message ... Hi How to get distinct items from a recordset? Let's assume I have two columns returned from a database: countries and cities. I want to retrieve a list of unique country names. Kind regards IgorM |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Distinct items from recordset
Yes, you are right. But, for instance, if I run a query as this: SELECT
Country, Province, City, PostCode FROM tblPostCodes and I then, after assigning the results to a recordset, I want to populate form controls (drop down lists, one for data from each column) with appriopriate data I need to get a list of unique countries from the recordset - I only want one name of each country appear in a drop down list with countries. I could of course write a separate query for each column but I don't want to do this for performance reasons - I'd have than query a database for a new list of provinces, cities, etc when user selects a different country in a country drop down list. For this I use: Private Function GetProvinceList(ByRef mrsDataSet As ADODB.Recordset) Dim mcolProvinceDistinctNames As New Collection Dim mvCollectionItem As Variant On Error Resume Next Do mcolProvinceDistinctNames.Add mrsDataSet![Province], CStr(mrsDataSet![Province]) mrsDataSet.MoveNext Loop Until Not mrsDataSet.EOF On Error GoTo 0 But I thought there is a better way to do this. Użytkownik "Bob Phillips" napisał w wiadomości grup ... Surely, you are already querying the database to get a recordset? What I am saying is to change the query to only return distinct records. -- HTH Bob "IgorM" wrote in message ... Its probably me not being too specific. I don't want to query the database to restrict connections to database. I've been thinking about dumping values from a recordset to a collection and using the values as a collection key too. But is there a better way to do that - a buid-in unique filtering function. Użytkownik "Bob Phillips" napisał w wiadomości grup ... Igor, Why don't you make the query return a distinct set? -- HTH Bob "IgorM" wrote in message ... Hi How to get distinct items from a recordset? Let's assume I have two columns returned from a database: countries and cities. I want to retrieve a list of unique country names. Kind regards IgorM |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Distinct items from recordset
I presume that this is a cascading set of controls, pick a country and it
shows those provinces, pick a province and it shows those cities, and so on? If so, you will have to process it somewhere, and I would do it in VBA and build a set of associated ranges and populate the controls as and when needed. Shouldn't be that hard. -- HTH Bob "IgorM" wrote in message ... Yes, you are right. But, for instance, if I run a query as this: SELECT Country, Province, City, PostCode FROM tblPostCodes and I then, after assigning the results to a recordset, I want to populate form controls (drop down lists, one for data from each column) with appriopriate data I need to get a list of unique countries from the recordset - I only want one name of each country appear in a drop down list with countries. I could of course write a separate query for each column but I don't want to do this for performance reasons - I'd have than query a database for a new list of provinces, cities, etc when user selects a different country in a country drop down list. For this I use: Private Function GetProvinceList(ByRef mrsDataSet As ADODB.Recordset) Dim mcolProvinceDistinctNames As New Collection Dim mvCollectionItem As Variant On Error Resume Next Do mcolProvinceDistinctNames.Add mrsDataSet![Province], CStr(mrsDataSet![Province]) mrsDataSet.MoveNext Loop Until Not mrsDataSet.EOF On Error GoTo 0 But I thought there is a better way to do this. Użytkownik "Bob Phillips" napisał w wiadomości grup ... Surely, you are already querying the database to get a recordset? What I am saying is to change the query to only return distinct records. -- HTH Bob "IgorM" wrote in message ... Its probably me not being too specific. I don't want to query the database to restrict connections to database. I've been thinking about dumping values from a recordset to a collection and using the values as a collection key too. But is there a better way to do that - a buid-in unique filtering function. Użytkownik "Bob Phillips" napisał w wiadomości grup ... Igor, Why don't you make the query return a distinct set? -- HTH Bob "IgorM" wrote in message ... Hi How to get distinct items from a recordset? Let's assume I have two columns returned from a database: countries and cities. I want to retrieve a list of unique country names. Kind regards IgorM |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cannot use recordset twice with copy From recordset | Excel Programming | |||
get distinct data from multiple sheets & then consolidate distinct | Excel Programming | |||
Distinct | Excel Worksheet Functions | |||
Select Distinct Items in a Column | Excel Programming | |||
Type recordset/recordset? | Excel Programming |