What's New? | Contact Us

About Us | Site Map     

Home

 

2015 Weekly Picks

Cool Websites
Publications
Key Trends
Useful Resources
Tech Tips
 
Picks Archives
 
 

Bookmark and Share

Access Tips

View an alphabetical listing of Access Tech Tips

Donations Template for Access 2007

Microsoft.com offers a variety of free templates to help you set up Office applications.  Click here to download a template which creates an Access database for tracking information about fundraising campaigns including donations made by contributors, campaign related events, and pending tasks.  It’s free and it’s a great way to get started using Access.
 

Align Controls in Access 2007 Using Keyboard Shortcuts

Keyboard shortcuts allow you to quickly access frequently used commands or operations. In the past we have published numerous tips for using keyboard shortcuts in Word and Excel.  Here are a few for Access 2007 users:

bullet

Display a form or report in Design View or Layout view

bullet

Select a control or controls

bullet

Press Ctrl + L to align the text to the left

bullet

Press Ctrl + R to align the to the right

bullet

Press Ctrl + E to center the text

When using keyboard shortcuts remember to hold the Ctrl key down while pressing the letter.  Microsoft provides a complete listing of Access 2007 keyboard shortcuts at office.microsoft.com
 

Using a Startup Form in Access 2007

Make it easy to use an Access database by automatically displaying a form when the database is opened.  Access 2007 has made this process easier than ever before.  Here’s how:

bullet

Open the database and click the Office Button

bullet

Click the Access Options button at the bottom of the menu

bullet

Click Current Database on the left pane

bullet

Under Application Options click the Display Form drop-down and choose the form you want users to see when they open the database

bullet

Click OK

You may also want to clear the checkbox next to Display Navigation Pane to further simplify the environment for users.
 

Open Access 2007 Objects in Design View

Here are 2 quick ways to open Access objects in Design View:

bullet

Click the object to select and press Ctrl + Enter, or

bullet

Right click the object and select Design View from the shortcut menu
 

Create Access 2007 Tables from Excel

Here's a simple way to create an Access 2007 table from an Excel worksheet:
 
bullet

Open the Excel worksheet

bullet

Select the data you want in your Access table

bullet

Copy the data to the clipboard and paste it into Access or you can even drag and drop it into Access
 

Add Totals to Datasheets in Access 2007

A new feature in Access 2007 makes it easy to add totals to datasheets. Here’s how:
 
bullet

Open the Datasheet

bullet

On the Home tab on the Ribbon, in the Records group, click the Totals button

bullet

A Total row is created in your datasheet

bullet

Click the field you want to total and select the type of summary
 

Back up a Microsoft Access 2007 Database

bullet

Open the Database you want to back up

bullet

Click the Microsoft Office Button (top left)

bullet

Point to Manage

bullet

Click Back up Database to open the Save As dialog box

bullet

Choose the name and the location (By default Access 2007 adds the date of the backup to the name of the file.)

bullet

Click the save button
 

Microsoft Access 2007 Online Tutorials

If you’re trying to learn Microsoft Access 2007 there are excellent online resources available. I recommend checking these out:

bullet

Microsoft Office Online offers several Access 2007 Courses

bullet

Access 2007 Tutorial developed by Florida Gulf Coast University
 

Navigation Pane in Access 2007

In Access 2007, the Navigation Pane has replaced the Database Window as the main way to get around in a database.

bullet

To change the width of the Navigation Pane, Position the pointer over the right edge of the Navigation Pane and then, when it changes to a double-sided arrow   drag the edge to increase or decrease the width.

bullet

To expand or collapse the appearance of the Navigation Pane, click the Shutter Bar Open/Close Button , or press F11 to toggle the pane between the open and closed views.
 

Display Tabbed Documents in Access 2007

A new feature in Access 2007 is the option to use a tabbed interface. This allows you to open multiple objects (tables, forms, reports, or queries) at once. Each object is a separate tab so you can easily see the available objects and click on the one you want. To use this feature:

bullet Click the Office button in the left corner of the screen
bullet Click the Access Options button at the bottom of the window
bullet Select the Current Database in the left pane
bullet Under Document Window Options, select Tabbed Documents
 

Access 2007 Report Layout View

The new Access 2007 Report Layout View is a major improvement over previous versions of Access.  This view allows you to see the report as it will print (WYSIWYG) and quickly change controls.  New features make it easier to group, filter and sort data.  Check out Quickly summarize group data in Access 2007 reports on the TechRepublic Web site for step by step instructions on summarizing group data in Access 2007 reports.
 

Upgrading to Access 2007

If you are upgrading to Access 2007 one of the first hurdles may be opening your Access file only to find your code won't run.  The solution is making sure your database is in a "trusted" location.  To do this:
 
bullet Click the Office button
bullet Click the Access Options button
bullet Select Trust Center
bullet Click the Trust Center Settings button
bullet Select Trusted Locations
bullet Click the Add new location button
bullet Browse and select the location of your database
bullet Click OK
 

Combining Text from Two Fields in Access

In Microsoft Access you can combine the text in two or more fields into a single text string by using “concatenation”.  The results can be displayed in a field in a query, or in a control on a form or report.

For example, If you have a table that contains the fields First and Last for the name, use the following expression to create a text string that displays the values of the First and Last fields separated by a space.

=[First] & " " & [Last]

Type this expression in a text box control on a form or report to display the first and last name together.  (Be sure to put a space between the double quotes in the expression.)
 

Number Entries in an Access Report

bullet Open your Access report in Design View
bullet Use the Toolbox to add a text box for displaying the number
bullet Select the text box and choose View Properties from the pull-down menu
bullet Click the Data tab
bullet Change the Control Source to =1
bullet Change the Running Sum to Over Group


Define a Default Field in an Access Database

  1. Open a table in Design view.

  2. In the upper portion of the window, click the field you want to define a default value for.

  3. In the lower portion of the window on the General tab, click the DefaultValue property box, and then type the default value (such as New York) or an expression (such as Date( )).

Note: Setting the DefaultValue property for a field has no effect on existing data. However, you can replace a field's current value with the default value by pressing CTRL+ALT+SPACEBAR.
 

Import Access Tables into Excel

Here's a quick way to copy an Access table into Excel.
  1. In the Access database window, click the table you want to export.
     

  2. On the Standard toolbar, click Office Links.
     

  3. Click Analyze It with Excel.

Excel automatically opens and displays your table in a worksheet.
 

Change the Default Working Folder in Access

Whenever you create a new Access database (.mdb) file or open an existing one, Access saves it to or opens it from the default working folder. You can change the default working folder for Access database files to any folder on your computer by typing the path for the new folder in the Default database folder box on the General tab of the Options dialog box (Tools menu).
 

Deleting Duplicate Records in Access

One way to delete duplicate records in Access is to create a new table which holds the same records but without the duplicates. Then delete the old table and rename the new one.

  1. Use a make-table query based on this table only. IMPORTANT - Ensure that you include all of the fields from your original table in the QBE Grid, otherwise you could loose data.

  2. Open the query's property sheet by using VIEW, QUERY, PROPERTIES, and set the Unique Values property to Yes

  3. Because you have selected the Unique Values to Yes when you run the query, Access creates a new table without duplicate records. You can now delete the old table and rename the new one.
     

Displaying Highest or Lowest Values in an Access Query’s Results

Open the query in Design View. In the sort cell of the relevant field click either DESCENDING or ASCENDING. Then click the TOP VALUES box that you will find in the toolbar. Enter either a percentage or the number of highest or lowest values.

For example, by selecting DESCENDING and then entering 10 in the TOP VALUES box you will get the top ten values in your query. Or if you enter ASCENDING and 25% you will get the bottom 25% of values in your query.

You can also input Top Values by setting the TopValues property in the query's property sheet.

 

Adding the Database Name to a Report in Access

To add the database name to a report  

  1. Open your database.

  2. In the Database window, right-click your report, and then click Design View.

  3. Click the Text Box tool in the toolbox, and then click in the report header where you want the name to appear.

  4. In the label for the unbound text box you just added, type Database:

  5. Right-click the text box, and then click Properties.

  6. Click the Data tab, and then type the following text in the ControlSource property box:
                =Left(CurrentProject.Name,Len(CurrentProject.Name)-4)

  7. On the View menu, click Print Preview to see the results
     

Techniques for Creating Forms and Reports in Access

It can be difficult and time consuming to align controls on forms and reports in Access. Here are several tips for helping you with this process.

Aligning controls with menus Highlight the controls you want to align and use Format, Align and then choose left, right, top or bottom.

Create alignment toolbar Create a "QuickForms" custom toolbar that has 4 buttons covering the 4 alignment options mentioned above. Enable this toolbar when you are looking to tidy your forms and reports.

Keyboard shortcuts for moving Controls It’s often easier to align the Controls when you use the keyboard to "nudge" the object into place. Highlight the Control, hold down the Ctrl key and then use the navigation arrows to move in the required direction.
 

Using Validation Rules to Restrict Data in Microsoft Access

A validation rule is an expression that can define the information that will be accepted in fields. You can type validation rules in, or you can use the Expression Builder to create them. At the field level, Access uses the rule to test an entry when you try to leave the field. At the table level, Access can use the rule to test the content of several fields when you try to leave the record. If an entry doesn’t satisfy the rule, Access rejects the entry and displays a message explaining why. 

In the Access table design window you can add a validation rule to the Phone field that will prevent the entry of an area code other than 414 or 262, by selecting the Phone field, clicking in the Validation Rule box, and typing Like "414*" Or Like "262*".

Automatically Compact and Repair Access Databases

bullet

Open the Access database that you want to compact automatically.

bullet

On the Tools menu, click Options.

bullet

Click the General tab

bullet

Select the Compact on Close check box
 

Create a Vertical Control in Access

In Access, you can create a vertical label or text box on a form or report by setting the Vertical property of the control.

To set a label or text box for vertical display  

bullet

Open the form or report in Design view.

bullet

Click the label or text box.

bullet

On the View menu, click Properties, and then click the Other tab.

bullet

Set the Vertical property to Yes.

For more information about the Vertical property, click the Vertical property box, and then press F1.
 

Insert Time/Date in Excel or Access

Here are a few keyboard shortcuts you can use to insert the current time and date in a Microsoft Access table or Microsoft Excel spreadsheet.

bullet Current date: Press CTRL+SEMICOLON
bullet Current time: Press CTRL+SHIFT+ SEMICOLON
bullet Current date and time: Press CTRL+ SEMICOLON then SPACE then CTRL+SHIFT+ SEMICOLON

In Access, this keyboard shortcut only works if you are entering data in the Datasheet or Form view.

Editor's Note: When you insert the date and time using this tip, the information remains static. To update this information automatically, you must use the TODAY and NOW functions. To learn how to do this, search for Insert the current date and time in a cell in Excel Help and then click Insert a date or time whose value is updated.

This tip comes to us from www.microsoft.com, courtesy of Kimberly Schenk, Smyrna, Tennesse.
 


"There is nothing so useless as doing efficiently that which should not be done at all." Peter F. Drucker  View Past Quotes

frank@createthefuture.com susan@createthefuture.com

2016 Creative Information Systems 

 In Association with Amazon.com

Revised: June 20, 2016