Case Study 1

ABC Garages Ltd has four garages in Swansea, Neath, Port Talbot and Bridgend. A table called 'Cars' is to be used to keep a record of used cars for sale. Create a table with the following structure:

The Location field is to be related to a lookup table called 'locations' with the structure shown below. Create this table with a key-field called 'Code' of 1 character text format and another field called 'Location' of 12 characters text format.

Adding the Locations table to the Cars table as a Lookup table
In the Design View of the Cars table select the Location field. In the Field Properties box select Lookup. Then choose List box, Table/Query, Locations, as shown below:

We are now going to link the two tables together.

  1. To relate the Cars and Locations tables together choose Tools - Relationships.
  2. Add both Cars and Locations tables to the screen.
  3. Drag the Code field in the Locations table to the Location field in the Cars table and release.
  4. A screen dialogue box appears. Click in the Enforce Referential Integrity box, the One to Many relationship is shown. Then select Create. This now ensures that the Location field in the Cars table can only contain values as stored in the code field of the Locations table.
  5. Close the Relationships window and enter the Code and Location field values as shown in the table at the top of the page - ie. S - Swansea, N- Neath etc.
  6. Enter 15 records in the Cars table entering your own data in no particular order using the Autoform.
  7. Create a Query to list the cars stored in each garage location.
  8. Create a Query and use an AND operation to list a search for two makes - eg. Rover AND Ford.
  9. List the cars stored in any two of the garages.
  10. Delete one record.
  11. Append two records.
  12. Create a query to sort on Make and Model.
  13. Create a query to sort on Price and produce a report totalling the price field.
  14. Use a Report Wizard and then add a "Sum" field as shown in the Creating Reports section.
  15. Create a Query to list the cars with a current MOT certificate in Swansea Garage.

Contents: Databases

Skill Check: Databases