Here are some tips on querying your data in Access:
Using the LIKE keyword
There are two uses that I know of here:
1) To search for a string in the entire cell, regardless of its placement. To do this, you need the following syntax:
WHERE [field] LIKE "*" & "criteria string" & "*"
2)To search for a substring inside of a parent string. If you want this, the syntax is:
Using domain aggregate functions
WHERE [field] LIKE "*criteria sub string*"
There is really only one issue that hangs people up. That is the syntax of the criteria argument. It's different on all accounts. Most of the time, at least for me, I am using a DLOOKUP() and I'm using a SELECTED field that I've already specified in the criteria section of the function (all domain functions have the same arguments):
Different criteria section syntax for different data types
1) The syntax for using TEXT/MEMO/HYPERLINK fields is:
"[lookupfield] = '" & [localfield] & "'")
2) For NUMBERS/CURRENCY, use:
dlookup("[lookupfield]", "lookuptable", "[lookupfield] = " & [localfield])
3) For DATES:
"[lookupfield] = #" & [localfield] & "#")
A subquery is nothing more than a simple SELECT statement inside a set of parenthesis. It is used as part of the WHERE clause of the parent query statement. What makes them unique though are the COMPARISON and EXPRESSION operators that you can use to manipulate the output. In a general sense, these operators are: ANY, ALL, SOME, IN(), NOT IN(), EXISTS(), NOT EXISTS().
EXPRESSIONS: [NOT] IN()
This simply retrieves records in which the criteria field's value can be located in the subquery's corresponding field. On the flip side, if you use 'NOT IN' instead, the opposite records will be given. The following will give records only where the [id] field value can be found in TABLE2's [id] field:
EXPRESSIONS: [NOT] EXISTS()
SELECT * FROM table WHERE
[id] IN (SELECT [id] FROM table2)
This is almost the same, if not THE same as the IN() subquery. By using EXISTS(), you are asking for records where the subquery equals TRUE. If you use the 'NOT' predicate, the subquery must equal FALSE to return a record. The following will give records where [ID] in table1 is present in the [ID] field of table2:
SELECT * FROM table1 WHERE
[ID] EXISTS (SELECT [id] FROM table2 WHERE
table1.id = table2.id)
This is kinda confusing. Basically what this does is returns records that satisfy the criteria and takes into account ANY record that is retrieved by the subquery. For instance, the following returns records where the [id] value from table1 is greater than ANY records that result from the subquery being executed:
SELECT * FROM table1 WHERE
[id] > ANY (SELECT [ID] FROM table2)
In the above query, if we assume that both [id] fields are autonumbers, and they both started at 1, the query would not return the record with an [id] of 1 because were are asking for records where the [id] field is GREATER than ANY of the ones the subquery has to offer. If autonumbers started at 0, we could get the first record from table1, but since they don't, we can't. You would have to use a '<' sign to get it in this example
Just like in the above example using "ANY", this tells Access to give us records where the [id] in table is GREATER than ALL of the [id] field values that are returned in the subquery. Using the above example, the query would be:
SELECT * FROM table1 WHERE
[id] > ALL (SELECT [ID] FROM table2)
Assume for simplicity that both tables have the same number of records and the biggest autonumber in each table is 100. If this is so, the query will return nothing because the [id] field has to be GREATER than ALL of the [id] fields returned by the subquery records. Assume though that table 2's top autonumber is 50. If this is the case, the query will return any record where the [id] field has a value greater than 50, as that record will be included in the retrieval set by the subquery.
Functions in queries
This is practically limitless, in terms of what you can return as a value to the query. You can use functions in criteria sections of other functions (such as domain aggregates, or simply use them to create a single field. They can used with or without arguments. Here is one with an argument:
SELECT * FROM table WHERE [ID] = GetID(5)
function GetID(mynumber as integer)
GetID = mynumber
The above query will return a record with an [ID] of 5. 5 is passed as the function argument and the function also equals that argument value, via the coding, which comes back to fill the criteria portion of the WHERE clause in the query.
You can also more complicated stuff. Say for example, you have a field in a table called [file path], where you store the complete address of files on your computer. Say you want to pick a file at random from windows explorer and make the query return all records where the [file path] field has a file with the same extension in it. Try this:
SELECT * FROM table
WHERE Right([file path], (Len([file path]) -
InStrRev([file path], ".") + 1)) = GetFileType([file path])
The query will open up the explorer window for you when you run it, because of the function code:
Combining Keywords, Predicates, Functions, Expressions, etc...
Dim varitem As Variant
.Add "All Files", "*.*"
.AllowMultiSelect = False
.InitialFileName = "c:"
.InitialView = msoFileDialogViewDetails
If .Show Then
For Each varitem In .SelectedItems
GetFileType= Right(varitem, (Len(varitem) - InStrRev(varitem, ".") + 1))
Rarely have I ever written something like this, but if the need arises, combinations of all these techniques are virtually limitless. For example, this query works fine:
SELECT DISTINCT TOP 5 [orderdate]
WHERE [ID] >= ALL (
SELECT [ID] FROM table2
WHERE [ID] IN (
SELECT [ID] FROM table3
WHERE [ID] < GetID(50)))
WHERE Month([orderdate]) = 5
Using the same function that I have in the above example, here you get the following:
The top 5 distinct dates from the orders table (only if they are dates in the month of MAY), WHERE the [ID] field is greater than all of the [id] fields in the records returned from the first subquery. The first subquery though, only includes records where the [ID] field value can be found in the third subquery set, which has a criteria section based on a function.
I'm sure stuff like this is hardly ever needed if the data is structured correctly, but the combinations are endless, as you see here.