Listbox fill only unique field data

Databases
1

Hi. I’m trying to load data into a listbox that is unique. I want to add the last names to lbxStudents, but in the emailresults table, there can be multiple instances of a person’s last name. The sample data I am playing with for last names is: Smith, Jones, Jones, Jones, Jones, Doe, Doe. In the code below, this is adding these 7 last names to lbxStudents, but I want it to add only 3 (Smith, Jones, Doe). Can this be done, and how?

[code] For i As Integer = 0 To lbxRegCode.ListCount - 1
Dim sql As String
sql = “SELECT * FROM emailresults WHERE RegCode LIKE '%” + lbxRegCode.Cell(i, 0) + “%’”

Dim data As RecordSet
data = mdb.SQLSelect(sql)

If data <> Nil Then
  While Not data.EOF
    lbxStudents.AddRow(data.Field("LastName").StringValue)
    
    data.MoveNext
  Wend
  data.Close
End If

Next[/code]

2

Hi Ryan - I’d try changing your SQL statement to this:

sql = "SELECT DISTINCT LastName FROM emailresults WHERE RegCode LIKE '%" + lbxRegCode.Cell(i, 0) + "%'"

Which should only give you unique values. The “DISTINCT” SQL keyword is pretty widely supported, so it should work.

Also, as a general rule, doing a “SELECT *” is discouraged because it’s a lot of extra work/data happening, especially if the table has a lot of rows. It’s normally considered a best practice to explicitly list the columns you’re requesting in the query.

HTH!
Anthony

3

That did the trick Anthony, thank you! I was not aware of the DISTINCT keyword.

And I’ll start explicitly calling the requested column(s) only. Thanks for the advice!

4

Glad it worked! Always better to let the database do the “heavy lifting” if you can. :slight_smile:

5

If you are using SQLite, have a look at the book, “The SQL Guide to SQLite” by Rick van der Lans. Very practical and hands on, tons of workable examples, very good book.

6

Can you please say more about this? What exactly is the additional overhead? Assuming that I (might) need all columns, I always thought that using “*” was, in fact, allowing the database to do the “heavy lifting.”

Assuming that you are correct about this, does it vary between databases? Do some take a bigger hit than others?

7

The keyword here is “general”.

In a relational database there are, normally, a lot of columns that are pointers to other table items. Queries and subqueries do not necessarily require the relational link in all cases so it is far better to explicitly define the columns from which you require your data.

Also, if you have subsequently added further columns to the table these will be automatically included in any * query which is an additional overhead for those queries.

8

[quote=101394:@Peter Truskier]Can you please say more about this? What exactly is the additional overhead? Assuming that I (might) need all columns, I always thought that using “*” was, in fact, allowing the database to do the “heavy lifting.”

Assuming that you are correct about this, does it vary between databases? Do some take a bigger hit than others?[/quote]

If your table has 100 columns and you need 3 you shove a LOT of data back that is unused

You should always ask for exactly the columns you want so you’re not surprised by table changes (adding new columns you have no idea how to handle in your routine) and so you don’t drag back all the columns & data when its not needed

9

[quote=101416:@Norman Palardy]If your table has 100 columns and you need 3 you shove a LOT of data back that is unused

You should always ask for exactly the columns you want so you’re not surprised by table changes (adding new columns you have no idea how to handle in your routine) and so you don’t drag back all the columns & data when its not needed[/quote]
Well, yes; that’s self evident. However, if you really want/need all of them, or almost all of them, I’ve not seen any downside to using “*”.

I’ve also more than once been bitten by NOT getting a field after having added a column to the schema - because my query was explicit…

10

Do whatever suits you but I’ve been bitten more times by select * than by being explicit

11