Distinct items from recordset

[IgorM]

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

 

[Jarek Kujawa]

Excel 2003
Data->Filter->Advanced->select your ranges and tick "Unique records
only"

 

[Bob Phillips]

Igor,

Why don't you make the query return a distinct set?

 

[Mike H]

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]

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.

 

[Bob Phillips]

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.

 

[IgorM]

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.

 

[Bob Phillips]

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

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.

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