Microsoft Access Programmer New Haven, Connecticut

Queries And Fields In Microsoft Access

Call MS Access Solutions at (323) 285-0939 for your FREE Consultation.

The material below originally appeared in Alison Balter's book Mastering Microsoft Office Access 2007 Development and is reprinted here with the author's permission. There may be references to "Figures" or "Chapters" that are not reprintable and are not used on this page.

Row Fix-Up In Multitable Queries

The Row Fix-Up feature is automatically available to you in Access. As you fill in keynvalues on the "many" side of a one-to-many relationship in a multitable query, the nonkey values are automatically looked up in the parenttable. Most database developers refer to this as enforced referential integrity. A foreign key must first exist on the "one" side of the query to be entered successfully on the "many" side. As you can imagine, you don't want to be able to add an order to your database for a nonexistent customer. For example, the query in Figure 4.16 is based on the Customers and Orders tables. The fields included in the query are Customer ID from the Orders table; Company, Address, and City from the Customers table; and Order ID from the Orders table

If the Customer ID associated with an order is changed, the Company, Address, and City are looked up from the Customers table and immediately displayed in the query result. Notice in Figure 4.17 how the information for Business AA is displayed in the query result. Figure 4.18 shows that the Company, Address, and City change automatically when the Customer ID is changed to Company AA. Don't be confused by the combo box used to select the customer ID. The presence of the combo box within the query is a result of Access's auto-lookup feature, covered in Chapter 2, "What Every Developer Needs to Know About Databases and Tables." The customer ID associated with a particular order is actually being modified in the query. If the user adds a new record to the query, Access fills in the customer information as soon as the user selects the customer ID associated with the order.

Creating Calculated Fields

One of the rules of data normalization is that the results of calculations shouldn't be included in your database. You can output the results of calculations by building those calculations into your queries, and you can display the results of the calculations on forms and reports by making the query the foundation for a form or report. You can also add controls to your forms and reports containing the calculations you want. In certain cases, this can improve performance. (This topic is covered in more depth in Chapter 16, "Debugging: Your Key to Successful Development.") The columns of your query result can hold the result of any valid expression, including the result of a user-defined function. This makes your queries extremely powerful.

Please Note . . .

You can enter any valid expression in the Field row of your query design grid. Notice that field names included in an expression are automatically surrounded by square brackets, unless your field name has spaces. If a field name includes any spaces, you must enclose the field name in brackets; otherwise, your query won't run properly. This is just one of the many reasons why field and table names shouldn't contain spaces.

---

Author Attribution

The preceding material originally appeared in Alison Balter's book Mastering Microsoft Office Access 2007 Development and is reprinted here with the author's permission.

---

Expert Microsoft Access Programmer In New Haven, Connecticut

When you need a Microsoft Access programmer for your New Haven, Connectituct business, call MS Access Solutions at (323) 285-0939. We have over 25 years experience in Microsoft Access programmer solutions. We create Access database applications for all sectors, consisting of hospitals, government agencies, the U.S. military, universities, agriculture, workers services, and insurance provider. We can take care of the most advanced as well as complicated Access and also SQL Server database programming for your business as well as smaller projects, like fixing damaged Access database forms, repairing broken MS Access reports, regenerating unproductive Microsoft Access macros, and re-program Visual Basic for Applications (VBA) code.

---

Find out more about MS Access programming service on the Microsoft Access programmer Washington, DC web page.

Use Proper Table Design and Normalize Your Data

A well-structured table ensures data consistency and minimizes redundancy. To achieve this, follow database normalization rules:

• First Normal Form (1NF): Ensure each field contains atomic values (no multiple values in a single field).
• Second Normal Form (2NF): Eliminate partial dependencies by linking related data through foreign keys.
• Third Normal Form (3NF): Remove transitive dependencies to ensure every non-key field depends only on the primary key.
• Avoid duplicating data across multiple tables. Instead, create relationships to maintain data integrity.

Choose Meaningful and Consistent Field Names

Clear, descriptive field names make databases easier to use and maintain. Keep these best practices in mind:

• Avoid spaces and special characters – use camelCase or underscores (e.g., CustomerID instead of Customer ID).
• Use names that describe the field’s purpose, such as OrderDate rather than Fld1.
• Maintain consistency across tables to ensure uniformity.

Define Primary Keys and Indexes Properly

Primary keys are essential for uniquely identifying records, while indexes improve query performance. Here’s how to manage them effectively:

• Use AutoNumber for primary keys to ensure uniqueness.
• Avoid using personally identifiable information like Social Security numbers as primary keys.
• Add indexes to frequently searched fields but avoid excessive indexing, which can slow down database operations.

Establish Proper Relationships Between Tables

Creating relationships between tables prevents redundant data and ensures consistency:

• Use foreign keys to link related tables and enforce referential integrity.
• Be cautious when enabling cascade update/delete options to prevent unintended data loss.
• Store IDs instead of full data in linked tables (e.g., store CustomerID in the Orders table instead of duplicating customer details).

Set Appropriate Data Types and Validation Rules

Choosing the correct data type improves storage efficiency and data accuracy. Follow these guidelines:

• Select the smallest necessary data type (e.g., Integer instead of Long Integer if values won’t exceed 32,767).
• Apply validation rules (e.g., setting >= 0 for price fields to prevent negative values).
• Define default values and required fields to minimize input errors.

Summary

When you implement these best practices for your Microsoft Access tables you ensure a well structured datanbase. By normalizing data, using clear field names, setting appropriate keys, establishing relationships, and defining the right data types, you can build a database that performs well and maintains data integrity.

Get The Best Microsoft Access Programmer

When you need a Microsoft Access programmer for your New Haven, Connectituct business, call MS Access Solutions at (323) 285-0939.