All programs seem to have some sort of time saving tips or special shortcuts and Microsoft Access is no exception. The casual user may not be aware of many of these tips, so at Arkware, we have shared a few query tips and tricks.
Top Values Setting
When in the design of any query, there is a setting to filter out the top records. The top values setting provides several different options. One option is to return a certain percentage of data. If you want to know the top 25% of your highest price orders, you just select the 25% option from the list. The important part of this feature is to always have the data in the proper sort order. The drop-down list has some pre-defined choices, but it allows any values entered.
Zoom Box
It is often difficult to see everything in any part of a query when typing in the criteria or field name area. A keyboard shortcut of “Shift + F2” will bring up the zoom box. The zoom box allows you to see everything in one separate window.
Datasheet View button versus Run Button
When creating a standard query, there are two choices to see the query results. You can select either the datasheet button or the run button. However, when creating action queries (queries that perform action on records), using the run button will carry out the query. Before you run a delete or update query using the run button, you may want to see what or how many records are going to be updated or changed. Selecting the datasheet button will allow you to do this. Once you are confident of what records will be updated, then the run or action query button can be used.
ODBC Timeout Setting
Microsoft Access allows you to connect to other data sources such as a SQL Server database. Depending on the design of the SQL Server database and the number of records that are retrieved, it can take some time to retrieve those records.
The default ODBC (ODBC stands for “Object Database Connectivity) timeout setting is 60 seconds. This is the time it will take for the query to return data results. If it takes longer, the user will receive a time out error. A tip for this setting is to enter the value of zero in the setting. By entering a zero, the time out is set to unlimited. This means the user will not see a timeout error if it takes longer.
Parameter Query Setting
Microsoft Access offers many options for entering query criteria. One option is the parameter query option. This option prompts the user for specific data input. For example, you can have the option prompt for a specific data range by using the Between [Enter the start date] And [Enter the end date].
Sometimes you want to see all the records for a specific situation. For example, if you do not know the starting or ending dates for example, you may be excluding some records.
The tip for this feature is to add a statement at the end of the criteria. As noted below, the way to use this feature is once the query prompts the user to enter the date range, but not entering a date for the start and end date prompts, it will return all the records from the select query, and in this case all dates.
Hopefully, these tips will help you in your work with Microsoft Access. If you need help with your project or do not know where to begin in the design of any database, please reach out to Arkware for assistance.