Hopefully at the workplace you will use a full RDBMS such as Oracle, MySQL, MariaDB etc. But you can run SQL commands on Microsoft Access to at least get some experience using them.
Creating a database
Launch Access. From the Home screen click the Blank database to start.
Give the database a name when prompted.
Access then creates a database with a new table to edit already made.
Add columns
Access has already added a column to work as a key. This is a number field that automatically increments each time you add a record.
Click the dropdown beside “Click to Add”. Here you can see the data types available for the attributes.
Access datatypes
| Datatype | Description |
| Short text | Up to 255 characters with no formatting. |
| Long text | Up to approximately 65,000 characters. Allows html and other formats. |
| Rich text | A short cut to a long text field with formatting options present. |
| Number | Integers that range from -32,768 to 32,767. |
| Large number | Integers that range from -2,147,483,648 to 2,147,483,647. |
| Currency | 15 digits to the left of the decimal point and 4 digits to the right. Not rounded off. |
| Attachment | Stores files in the record. Not all extensions allowed. |
| Data and time extended | Increased precision and formatting options |
Common Access datatypes
Access defaults Number fields to use integers. If you want to use floating point numbers you will need to alter the Field Size in the table’s Design View before adding data.
Foreign keys
Use a number field for the foreign key because all the primary keys in this exercise are the automatically added number fields.
Close any open tables that you want to link together.
Click on the Database Tools menu then choose the Relationships icon.
Click and hold on the ID under Driver, drag the mouse until you are over the DriverID in the link table and release.
In the popup box tick the checkboxes so that deleting the Driver will also delete the matching items in the link table. Click Create and repeat for the VehicleID link. Save the Relationships tab.
Forms
To insert data in Access you use forms.
Click on the Create menu and choose Form Wizard from the ribbon.
Choose the columns that you want in the form. This will usually be all of them except the ID as that is automatically added by the system.
Then choose whichever layout you prefer.
Give the form a name the choose to either start adding data or change how the form looks. Choose the first option for now.
Forms aren’t just about adding data. You will see existing data too and can edit it.
If you are adding a new record, then use the arrows at the bottom of the screen to move to the end of the records until you are at a blank one.
Foreign keys have to exist before they can be used. A way to do this in forms is the combo box. Right click on the form and choose to open it in either Design or Layout view. Add a combo box from the ribbon.
In the popup wizard choose the first option to get the value
from another table. Choose the table that foreign key is from.
Choose the columns that you need, the ID plus some
description that makes more sense to the user. Choose how to
order the results in the next screen.
Leave the hide ID checkbox ticked and store the value in the
foreign key field. Finally give it a Label.
Making it look nicer
Click on the combo box in the form and then view the Properties pane to the right. Under Data -> Row Source you should see something like:
SELECT [Vehicle].[ID], [Vehicle].[Number Plate], [Vehicle].[Manufacturer], [Vehicle].[Model] FROM [Vehicle] ORDER BY [Manufacturer], [Model], [Number Plate];
Change this to something like:
SELECT [Vehicle].[ID], [Vehicle].[Number Plate] & " " & [Vehicle].[Manufacturer] & " " & [Vehicle].[Model] FROM Vehicle ORDER BY [Manufacturer], [Model], [Number Plate];
What you’ve done is combine the descriptive results into one column. Now click on the Format tab in the property pane. You want to change the column count from 4 to 2 and alter the width of the new single column.
