Skip to Main Content

go to Indiana University Indiana University


Archive for the 'Databases' Category

Access Forms: Always Ready for a New Record

A question arose in a recent Access Forms workshop about a form that was being designed to capture information supplied by library patrons. To that point in the workshop, we had created several forms; but in each case when the form was opened we saw record 1 of the data set. We saw that we could use the New Record icon to set the form fields to blank when we wanted to enter new data. But in this case, the database owner wanted the form to open so that what the user sees immediately is a form with blank fields ready for a new record to be entered. The solution simply involves setting properties of the form as follows:

  1. With the Form in Design view or Layout view, open the Property Sheet and be sure you are looking at the properties of the Form
  2. To most easily find the properties you need, choose the Data tab at the top of the Property Sheet
  3. Find the Data Entry property and set it to Yes
  4. Also be sure the Allow Additions property is set to Yes

Read the rest of “Access Forms: Always Ready for a New Record” »

Make Data Entry Easier in Access with Lookup Fields

A lookup field can make data entry much simpler and allows the user to choose from a list of values. There are two types of lookup fields: (1) a lookup list pulls data from an existing table or query (based on a one-to-many relationship) and (2) a value list uses a current list of values that you add when you create the field in a table. In addition, you can store multiple values in a field. Here the focus will be on creating a value list lookup in a table that could look something like this:

 

 

 

 

Read the rest of “Make Data Entry Easier in Access with Lookup Fields” »

Customizing the Ribbon in Microsoft Office

Recently I decided to investigate the Camera command in Excel 2010. It provides one possibility for capturing data from various spreadsheets and viewing it all in a single spreadsheet. But before I could play with the camera, I had to find it and display it. In my installation of Excel it was hidden by default.

Customizing the Ribbon is possible from the Options window. Go to the Excel (or other Office 2010 application) Options window by choosing the File tab and clicking Options near the bottom of its menu. At the left side of the Options window, choose the “Customize the Ribbon” category. In this window proceed as follows: Read the rest of “Customizing the Ribbon in Microsoft Office” »

Send Multiple E-mails to an Access or Excel Recipient List

You may have a list of business e-mails already created in an Access table or Excel spreadsheet and would like to send a mass mailing using Microsoft Outlook.  Perhaps you would like to invite everyone on your list to a special event, or you would like to let everyone know that you are being promoted to a different department in your organization and, therefore, would like to provide different contact information.

If you have an Outlook profile set up on your machine, you can send multiple e-mails quickly to many recipients at once by using Microsoft Word and the Mail Merge feature. If no Outlook profile is set up, then when you try to send the e-mails, you will get a warning stating that no Outlook profile is set up on your machine.

Just follow these steps:

  1. Open Microsoft Word.
  2. Click the Start Mail Merge button on the Mailings tab of the Ribbon and choose E-mail Messages to define the type of mail merge you want to create.
  3. Click Select Recipients on the Ribbon.
  4. Choose Use Existing List
  5. Navigate to the Excel file or Access object that contains the data source with your e-mail addresses.
  6. Click Edit Recipient List to choose those records that you want to include in the mailing. Click OK when you have chosen the recipients or filtered the list.
  7. Write your e-mail message in the Word document.
  8. To finish the merge, click the Finish & Merge button on the Ribbon.
  9. You can either edit or view the individual email messages, or send the e-mail messages directly.

If you choose Send E-mail Messages, you will get this dialog box:

Merge to Email dialog box

In the To box, using the drop-down arrow, choose the field in your list that contains the e-mail address. Add a subject line and choose the desired Mail format. Click OK, and the messages will be sent immediately.

Keep in mind that if you want any formatting, you must add formatting within the Word main document before completing the merge. You can also personalize the messages (such as adding a first name) by inserting merge fields into the Word document.

Access – Collecting Data by Email

Access provides great tools for organizing, searching and analyzing data. But before you can use those great tools, you must first enter the data. In Access 2007, a wizard with the monster-name, “Collect Data Through Email Messages Wizard,” for collecting information by email was introduced.  Two aspects of this process make it particularly attractive:

1)      You can set the wizard to automatically deliver data from the email replies directly into the appropriate fields of  your database, and

2)      The data is being entered by the primary source of the information – from their keyboard to your table.

This wizard lives – as you might guess – on the External Data tab of the Ribbon in the Collect Data group. You can start the wizard by first selecting a table or query that holds the fields you want to send out and then clicking the Create E-mail icon.

Access Ribbon Read the rest of “Access — Collecting Data by Email” »

Access: Running a Query Using a Filter Dialog Box in a Form

Parameter queries are quite handy to use when you know what to type when the prompt appears. However, have you thought how great it would be if you could create a parameter query in Access with a drop-down  list  – with  options from which to choose?  A simple form with a drop-down list, such as the one shown here, allows the user to run a query to view equipment records based on the Manufacturer criteria selected from this drop-down list:

Image of Filter Dialog Box
The best way to create a drop-down list  is to create a simple form with a combo box that can be referenced by the query. The form and the query work together to provide the desired dataset. Read the rest of “Access: Running a Query Using a Filter Dialog Box in a Form” »

Access: Querying for “Preferred” Data Given Multiple Choices

A friend once presented a challenge with his Access database. A table called tblContacts held information about contacts and included the fields WorkEmail and HomeEmail. Some records had values in both fields, others had values in just one or the other, and some had no values in either field. The user wanted to query the table for a list with a name and only one email address for each record. His preference was the work email, but he would use the home email if no work email existed. The table looked something like this:

FName

LName

WorkEmail

HomeEmail

Mary

Jones

someone@examle.com

me@example.com

Jim

Smith

js@example.com

Nancy

Olson

no@example.com

Lee

Brown

Read the rest of “Access: Querying for “Preferred” Data Given Multiple Choices” »

Microsoft Office 2010 is Here!

Microsoft Office 2010 Professional Plus for Windows is now available for download to IU students, faculty and staff from IUware Online. For more information on Office 2010 at IU, you can visit the IU Knowledge Base at:  http://kb.iu.edu/data/azeu.html. Here you can get information about system requirements and installation as well as information regarding the 64-bit version (coming soon). You will also find other notes and links to relevant resources regarding Office 2010.

To find out more about important new features, click here:  What’s New in Microsoft Office 2010.

Grouping Data in Access Reports

By applying a few simple techniques, you can make your Access  reports look more professional and more informative than ever before!

Adding Alphabetic Headings

In large reports, it is often useful to emphasize alphabetical groupings by adding text boxes that contain the first character for each group:

Adding Alphabetic Headings

 

 

 

 

Read the rest of “Grouping Data in Access Reports” »

Displaying Aggregate Expressions on a Main Form from a Subform

In Microsoft Access, when a main form and subform are created based on one-to-many relationships, you can display aggregate information in the main form; for example, a total COUNT of customer records for each sales person or a SUM total of sales amounts that have been displayed in a subform.

As you scroll through this main form, many customers may be associated with each sales representative:

Aggregate Count

Read the rest of “Displaying Aggregate Expressions on a Main Form from a Subform” »

Get Training Tips via RSS Subscribe to Entries feed or Read Comments via RSS Subscribe to Comments feed.