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.
| 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 |
| |
