Microsoft Access Programmer Hartford, Connecticut

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.

Pitfalls of Multitable Queries

You should be aware of some pitfalls of multi table queries; they involve updating as well as which records you see in the query output. Remember that you can't update certain fields in a multi table query. These are the join fields on the "one" side of a one-to many relationship (unless the Cascade Update Referential Integrity feature has been activated). You also can't update the join field on the "many" side of a relationship after you've updated data on the "one" side. More importantly, which fields can be updated, and the consequences of updating them, might surprise you. If you update the fields on the "one" side of a one-to-many relationship, you must be aware of that change's impact. You're actually updating that record in the original table on the "one" side of the relationship; several records on the "many" side of the relationship will be affected.

For example, Figure 4.14 shows the result of a query based on the Customers, Orders, and Order Details tables. I have changed "Company AA" to "Business AA" on a specific record of my query output. You might expect this change to affect only that specific order detail item. Pressing the down-arrow key to move off the record shows that all records associated with Company AA have been changed (see Figure 4.15). This happened because all the orders for Company AA were actually getting their information from one record in the Customers table—the record for ID 27. This is the record I modified while viewing the query result.

To get this experience firsthand, try changing the data in the City field for one of the records in the query result. Notice that the record ( as well as several other records) is modified. This happens because the City field actually represents data from the "one" side of the one-to-many relationship. In other words, when you're viewing the Country/Region and City fields for several records in the query output, the data for the fields might originate from one record. The same goes for the Order Date field because it's also on the "one" side of a one-to-many relationship. The only field in the query output that can't be modified is TotalPrice, a calculated field. Practice modifying the data in the query result and then returning to the original table and noticing which data has changed.

The second pitfall of multitable queries is figuring out which records result from such a query. So far, you have learned how to build only inner joins. Join types are covered in detail in Chapter 12, but for now, it's important to understand that the query output contains all customers, regardless if they have orders, and all orders, regardless if they have order detail. This means that not all customers and all orders are listed. In Chapter 12, you'll learn how to build queries in which you can list only customers who have orders. You'll also learn how to list only the customers without orders.

---

Author Attribution

This 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 Servcices In Hartford, CT

When you need a Microsoft Access programmer for your Hartford, Connecticut, business, call MS Access Solutions at (323) 285-0939. We have over 25 years experience as a Microsoft Access programmer and project management. We create Microsoft Access database applications for all business niches, inclucing, but not limoited to: medical offices, hospitals, government agencies - at the state, local, and federal level, the U.S. military, universities, junior colleges, school districts, agriculture, oil and gas,emplyment agencies, human resources, insurance companies, and insurance agencies.

At MS Access Solutions, we specialize in delivering comprehensive Microsoft Access and SQL Server programming services tailored to businesses of all sizes. Our expertise encompasses a wide range of solutions, from developing complex database systems to addressing specific issues such as repairing corrupted Access forms, optimizing reports, refining macros, and updating Visual Basic for Applications (VBA) code.

Our team is adept at creating scalable and efficient database applications that align with your business objectives. Whether you require the development of a new Access database integrated with SQL Server or need assistance with existing systems, we provide customized solutions to enhance your data management capabilities.

We also offer services to troubleshoot and resolve common database problems, including:

• Restoring functionality to malfunctioning Access forms and reports.
• Optimizing slow-running queries and improving overall database performance.
• Converting outdated Excel spreadsheets into structured Access databases.
• Implementing automation through advanced macros and VBA scripting.

Our commitment is to ensure your database systems operate seamlessly, providing reliable and efficient access to your critical business data.

---

Find out more about our Microsoft Access programmer services on the Access Programmer New Haven, Connectidut web page.