MS Access: Queries

Tutorials

• TUTORIAL: Pass-through Queries in Access 2003/XP/2000/97

Basics

• Create a query in Access 2007 | Access 2003/XP/2000/97
• Open a query in Design view in Access 2007 | Access 2003/XP/2000/97
• View SQL for a query in Access 2007 | Access 2003/XP/2000/97
• Add tables to a query in Access 2007 | Access 2003/XP/2000/97
• Run a query in Access 2007

Navigation Pane (Access 2010 and 2007)

• Display queries in the Navigation Pane in Access 2007
• Display all objects (tables, queries, forms, reports, modules) in the Navigation Pane in Access 2007
• Display tables and related objects in the Navigation Pane in Access 2007

LIKE Condition

• LIKE Condition (using wildcards) in Access 2007 | Access 2003/XP/2000/97
• Create a query whose LIKE clause gets its condition from a text box in Access 2003/XP/2000/97
• Create a query that excludes records that contain a wildcard character in Access 2007 | Access 2003/XP/2000/97
• Query to retrieve records that contains keywords stored in a table in Access 2007 | 2003/XP/2000/97

Group By Features

• Avg Function
• Sum Function
• Min Function
• Max Function
• Count Function

Queries using Max function

• Query to retrieve max date in Access 2007 | Access 2003/XP/2000/97
• Query to retrieve the most recent record from a table in Access 2003/XP/2000/97
• Query to retrieve max value but display all columns from original table in Access 2007 | Access 2003/XP/2000/97

Queries using Count function

• Query to retrieve the number of customers with a certain shoe size in Access 2003/XP/2000/97

Creating queries

• Only show unique values in a query in Access 2007 | Access 2003/XP/2000/97
• Prevent a query from timing out in Access 2003 | Access 2000/97
• Concatenate two fields in a query in Access 2007 | Access 2003/XP/2000/97
• Concatenate first name and last name fields with a space in the middle in Access 2007 | Access 2003/XP/2000/97
• Joining 3 tables (using left joins) in Access 2003/XP/2000/97
• Create a query using a Left Join in Access 2003/XP/2000/97
• Create a query that returns a new table based on 3 nested subqueries in Access 2003/XP/2000/97
• Include NULL values in query results in Access 2003/XP/2000/97

Update queries

• Update Query that updates one field based on the value of another field in Access 2003/XP/2000/97
• Update Query that updates values in one table with values from another table in Access 2007 | Access 2003/XP/2000/97

Filtering query results

• Filter a query based on a text box value in Access 2007 | Access 2003/XP/2000/97
• Filter a query based on InputBox command in Access 2007 | Access 2003/XP/2000/97
• Filter a query based on data entered in a text box in Access 2007 | Access 2003/XP/2000/97

Errors

• How to fix a "Compile error.in query expression" Error in Access 2003/XP/2000/97

Pass-through queries

• Create a pass-through query to retrieve the nextval from an Oracle sequence in Access 2003/XP/2000/97
• Create a pass-through query to retrieve the nextval from an Oracle sequence with VBA code in Access 2003/XP/2000/97
• Call an Oracle stored procedure using VBA code in Access 2003/XP/2000/97

Parameter queries

• Create a query that accepts "Start" date and "End" date as parameters in Access 2003/XP/2000/97
• Create a query that accepts a "barcode" number as a parameter in Access 2003/XP/2000/97
• Create a parameter query that performs a wildcard search in Access 2003/XP/2000/97
• Remove parameters from a query in Access 2003/XP/2000/97

Stack rank

• Perform a stack rank in a query in Access 2003/XP/2000/97
• Perform a stack rank (values greater than 0) in a query in Access 2003/XP/2000/97

Miscellaneous

• Use a query to find duplicates in Access 2007 | Access 2003/XP/2000/97
• Use a query to find unmatched entries between two tables in Access 2003/XP/2000/97
• View only records with uppercase letters for a certain field in Access 2003/XP/2000/97
• Complex set of queries that involve calculating the pass rate for each subject in Access 2003/XP/2000/97
• Display parameter values in a report based on a parameter query in Access 2003/XP/2000/97
• Create a query to differentiate between upper and lower case in Access 2007 | Access 2003/XP/2000/97
• Use a public string as part of the criteria in a query in Access 2003/XP/2000/97
• Convert IF formula in Excel to IIF formula in a query in Access 2003/XP/2000/97
• Retrieve Top N records from a query in Access 2007 | Access 2003/XP/2000/97
• Determine winning bids in a query in Access 2003/XP/2000/97
• Suppress system messages (Query confirmations) in Access 2003/XP/2000/97
• Create a query that displays movies loaned out in Access 2003/XP/2000/97
• Handling "Divide by Zero" errors in queries in Access 2007 | Access 2003/XP/2000/97
• Calculate a patient's weight loss between the last 2 visits in Access 2003/XP/2000/97
• Extract Last Name from a Full Name value in a query in Access 2007 | Access 2003/XP/2000/97