MeadInKent
| Feedback | VBA snippets | using Access Queries |

Records with no link in Access queries

When two tables (or queries) are joined in a query using a common field, any records which do not contain the common field in both tables will be omitted. For example, if one table contains customer orders and another table contains the customer contact details, they could be joined on a common field of the customer number (a unique reference or key for each customer).

Table: Customers ox
CustNo LastName ForeName Address Discount
120 Smith John The Orchard 20%
125 Jones Bob 224 The Warren 5%
130 Black Micheal 56 Castle Mount 10%
 
Table: OrderDetails ox
Item Price CustCode
Canon printer 67.25 120
HP Scanner 89.34 140
Blank CDRW 23.25 120
 

The two tables can be linked to create a useful dataset for preparing invoices. The person who ordered the scanner has an unrecognised customer number (140) and this cannot be linked to any address details. In a query with a normal join between the tables, the HP Scanner will simply be ignored.

Would you like to learn more?

Find out about this book

Microsoft Office Access 2003 Step by Step (with CD)

Select Query: OrdersWithCustomers ox
Item Price CustCode LastName ForeName Discount
Canon printer 67.25 120 Smith John 20%
Blank CDRW 23.25 120 Smith John 20%
 

Access contains a query wizard which will highlight records which do not contain a matching record in another table. This could be used to highlight the HP Scanner record and the potential problems that may arise.

Alternatively, you may wish to modify the query join properties in order to display both matched and unmatched records.

To change the join properties, right click on the line linking the two fields in the query design. Choose join properties and then select the appropriate option: All records from OrderDetails and only those records from Customers where the joined fields are equal. The line will then change to show an arrow head pointing at the OrderDetails table.

The customer details will however be missing from one record. The missing field values are not simply blank (i.e. "" or 0), they are said to have a Null value. It is therefore possible to say that if the LastName field value is Null, then substitute 'Not Found', otherwise use the actual LastName field. If the discount rate is to be used in any calculations, the calculation will fail unless any Null values are replaced with zeros.

Select Query: OrdersWithCustomers (Design) ox
 
Field Item Price CustCode QryLastName: IIf(IsNull([CustNo]),"Not Found",[lastname]) EarnedDiscount: IIf(IsNull([CustNo]), 0,[Price]*[Discount])
Table OrderDetails OrderDetails OrderDetails
Sort
 

The output from the query will now appear as follows:

Table: OrderDetails ox
Item Price CustCode QryLastName EarnedDiscount
Canon printer 67.25 120 Smith 13.45
HP Scanner 89.34 140 Not Found 0
Blank CDRW 23.25 120 Smith 4.65
 

file: AccNull.htm Page last updated Mar14 MeadInKent.co.uk 2014