Microsoft Access Database Programmer Tampa FL

Microsoft Access Programmer Information

Call MS Access Solutions at (323) 285-0939 For Complimentary Consultation

The following material is shared here to indicate our commitment to professional Microsoft Access programmer services in the Tampa FL area. Access programming for business is highly complex and requires dedication and skill to create a high quality database application. We wrote the book on Microsoft Access prgramming, so you know you will get the best possible Access database solution for your business.

Establishing Outer

Joins You use outer joins when you want the records on the one side of a one-to-many relationship to be included in the query result, regardless of whether there are matching records in the table on the many side. With a Customers table and an Orders table, for example, users often want to include only customers with orders in the query output. An inner join (the default join type) does this. In other situations, users want all customers to be included in the query result, whether or not they have orders. This is when an outer join is necessary.

NOTE

In Access, there are two types of outer joins: left outer joins and right outer joins. A left outer join occurs when all records on the "one" side of a one-to-many relationship are included in the query result, regardless of whether any records exist on the "many" side. A right outer join means all records on the "many" side of a one-to-many relationship are included in the query result, regardless of whether there are any records on the "one" side. A right outer join should never occur if you are enforcing referential integrity.

To establish an outer join, you must modify the join between the tables included in the query:

1. Double-click the line joining the tables in the query grid.

2. The Join Properties window appears. Select the type of join you want to create. To create a left outer join between the tables, select Option 2 (Option 3 if you want to create a right outer join). Notice in Figure 12.28 that the description is Include ALL Records from tblClients and Only Those Records from tblProjects Where the Joined Fields Are Equal.

3. Click OK to accept the join. An outer join should be established between the tables. Notice that the line joining the two tables now has an arrow pointing to the many side of the join.

A left outer join can also be used to identify all the records on the "one" side of a join that don't have corresponding records on the "many" side. To do this, simply enter Is Null as the criterion for any required field on the "many" side of the join. A common solution is to place the criterion on the foreign key field. In the query shown in Figure 12.29, only clients without projects are displayed in the query result.

Access Programmer for Tampa FL Attribution

This material originally appeared in Alison Balter's book Mastering Microsoft Office Access 2007 Development. Reprinted here by author's permission.

When you need a Microsoft Access programmer for your Tampa FL 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 SQL Server database programming for your business as well as smaller projects, like fixing damaged Access database forms, MS Access reports, Access macros, and VBA code.

More Access programmer cities we serve: Access Database Programmer Tempe AZ.