| Feedback | VBA snippets | using 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).
|
|
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?
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 |