Go Back   Access World Forums > Microsoft Access Reference > Access FAQs

 
Closed Thread
 
Thread Tools Rate Thread Display Modes
Old 02-23-2010, 11:46 PM   #1
ajetrumpet
Banned
 
Join Date: Jun 2007
Location: Universe - Local Group - Milky Way Galaxy - Orion Arm
Posts: 5,640
Thanks: 0
Thanked 97 Times in 44 Posts
ajetrumpet has a spectacular aura about ajetrumpet has a spectacular aura about
Send a message via MSN to ajetrumpet Send a message via Yahoo to ajetrumpet
Query Techniques & Methods

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:
PHP Code:
WHERE [fieldLIKE "*" "criteria string" "*" 
2)To search for a substring inside of a parent string. If you want this, the syntax is:
PHP Code:
WHERE [fieldLIKE "*criteria sub string*" 

Using domain aggregate functions
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:
PHP Code:
dlookup("[lookupfield]""lookuptable"

"[lookupfield] = '" & [localfield] & "'"
2) For NUMBERS/CURRENCY, use:
PHP Code:
dlookup("[lookupfield]""lookuptable""[lookupfield] = " & [localfield]) 
3) For DATES:
PHP Code:
dlookup("[lookupfield]""lookuptable"

"[lookupfield] = #" & [localfield] & "#"

Using Subqueries
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:
PHP Code:
SELECT FROM table WHERE

[idIN (SELECT [idFROM table2
EXPRESSIONS: [NOT] EXISTS()
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:
PHP Code:
SELECT FROM table1 WHERE

[IDEXISTS (SELECT [idFROM table2 WHERE

table1
.id table2.id
EXPRESSIONS: "ANY"
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:
PHP Code:
SELECT FROM table1 WHERE 

[id] > ANY (SELECT [IDFROM 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

EXPRESSIONS: "ALL"
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:
PHP Code:
SELECT FROM table1 WHERE 

[id] > ALL (SELECT [IDFROM 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:
PHP Code:
SELECT FROM table WHERE [ID] = GetID(5
PHP Code:
function GetID(mynumber as integer)

GetID mynumber

end 
function 
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:
PHP Code:
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:
PHP Code:
Function GetFileType()

Dim varitem As Variant
      
      With Application
.FileDialog(msoFileDialogFilePicker)

         
With .Filters
           
.Clear
           
.Add "All Files""*.*"
         
End With

      
.AllowMultiSelect False
      
.InitialFileName "c:"
      
.InitialView msoFileDialogViewDetails

    
If .Show Then

  
For Each varitem In .SelectedItems
     GetFileType
Right(varitem, (Len(varitem) - InStrRev(varitem".") + 1))                    
  
Next varitem
                    
    End 
If

      
End With

End 
Function 

Combining Keywords, Predicates, Functions, Expressions, etc...
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:
PHP Code:
SELECT DISTINCT TOP 5 [orderdate]

FROM orders

WHERE 
[ID] >= ALL (

SELECT [IDFROM table2 

WHERE 
[IDIN (

SELECT [IDFROM table3 

WHERE 
[ID] < GetID(50)))

WHERE Month([orderdate]) = 
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.


Last edited by ajetrumpet; 02-24-2010 at 12:56 AM.
ajetrumpet is offline  
The Following User Says Thank You to ajetrumpet For This Useful Post:
hassanogaibi (06-28-2016)
Closed Thread

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Query from multiple crosstab queries ramez75 Queries 0 12-23-2008 09:44 AM
help with sql syntax plz joet1984 SQL Server 1 12-14-2008 05:05 PM
Help plz with sql syntax joet1984 Other Software 0 12-11-2008 05:50 PM
Problem exporting a query using Transfertext method seattlerick Modules & VBA 4 09-11-2007 07:35 AM
Parameter Query Use/Syntax Dboyce Queries 1 10-09-2003 11:06 PM




All times are GMT -8. The time now is 11:59 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World