Get a list of unique values in a table

In an Access database you can easily get a list of the unique values in a column by running a SQL command.

1. This table contains multiple entries for many city names:

2. To get a deduped list go to Create . . . Query Design, and select 'SQL View' at the left of the toolbar.

3. Enter the following SQL command:

SELECT DISTINCT Olympics.City

FROM Olympics

The command SELECT DISTINCT picks a field in a table, and then FROM pulls that field from a designated table.

4. Click Run, and a new query will be generated listing each value in the table only once:

Running Regex searches in an Access database

If you need to run a query in an Access database using regular expression, Renaud Bompuis has posted a great solution here. In your Access database, press ALT + F11 to enter Visual Basic. Enter the below vba code in a new module. It will create a new function that you can reference in a query written in SQL.

For example, in this database, we have a table with numbers. We want to create a query which will find 7 digits numbers that begin with a '2'.

Go to Create . . . Query Design, and switch to SQL view. Structure a SQL search like this:

SELECT Field1

FROM Table1

WHERE RegexMatch(Field1, "2[0-9]{6}")

First, we SELECT the field we want in the results. Then we designate the table to be searched. The WHERE command lists the RegEx function, first referencing the field to be searched in parentheses.

Click Run on the toolbar, and your results will be displayed

As always, I have tested this script tonight in order to confirm that it works.

' ----------------------------------------------------------------------'

' Return True if the given string value matches the given Regex pattern '

' ----------------------------------------------------------------------'

Public Function RegexMatch(value As Variant, pattern As String) As Boolean

If IsNull(value) Then Exit Function

' Using a static, we avoid re-creating the same regex object for every call '

Static regex As Object

' Initialise the Regex object '

If regex Is Nothing Then

Set regex = CreateObject("vbscript.regexp")

With regex

.Global = True

.IgnoreCase = True

.MultiLine = True

End With

End If

' Update the regex pattern if it has changed since last time we were called '

If regex.pattern <> pattern Then regex.pattern = pattern

' Test the value against the pattern '

RegexMatch = regex.test(value)

End Function

Renaming Tables and Queries in Access Can Crash Your Database

While Access gives you the option to rename tables, queries, and other objects when you right click on them, you should avoiding doing so. After an object is renamed, you may receive an error such as this one:

Part of the trouble stems from the fact that other objects in the database will have trouble finding renamed objects that they have been linked to. If you select the option to 'Track name AutoCorrect info', and 'Perform name AutoCorrect' under File . . . .Options . . . Current Database, Access can avoid errors by updating object names automatically. But you need to give Access time to create the maps. Clear these options, close the database, reopen it, and re-select the options in order to make sure the maps are up to date.

Note the AutoCorrect feature will not revise object name changes in VBA code or queries written in SQL.