Author: paul.gray

For this example we need to add a record to the ‘Car and owner details’ table with a Car Reg Number that doesn’t appear in the ‘Jobs’ table.

[the_ad id=”12355″]
[the_ad id=”12356″]

Open the Car and owner details table and add a new record at the bottom of the table. Don’t add anything to the jobs table.

Close the table and reopen the query we used in the last example.

A subtract join is the opposite of an outer join, i.e. it includes only results in one table that don’t match any records in the other.

Base doesn’t have a direct option in design mode to do this, so it’s best to use some simple SQL.

To toggle into SQL editing mode, click the ‘Switch Design View On/Off’ icon.

Delete any text that is already in the SQL editor.

Then enter the text exactly as it appears in the image.

This SQL code sets up a left outer join between Car and owner details table and Jobs table, listing all the fields for any records that have a Car Reg Number in ‘Car and owner details’, that does not appear in the ‘Jobs’ table (IS NULL).

Press F5 to run the query

Notice that the query only returns the one record that is in the Car and owner details table, but not in the jobs table.

To see how this can be created without SQL code, click the Switch Design View On/Off icon

Don’t worry too much about the SQL commands for now, we’ll discuss these in more detail later on in the course.

Right click the connecting line and select edit to open join properties.

The SQL code we entered has changed the join type to a Left join. It can be modified here.

Notice in the query table, it has set the Criterion to ‘IS EMPTY’ which was the ‘IS NULL’ part of our SQL code.

This is a valid way of creating the same query without SQL.

[the_ad id=”12397″]

An outer join allows you to display ALL records from one table, but only records in the other table that match the related fields.

[the_ad id=”12355″]
[the_ad id=”12356″]

You can choose between a Left outer join or Right outer join in the Type drop-down menu.

Choosing Left join will display all records from the left-hand table, Right will display all from the right-hand table.

Joins are links between table that are used to modify the way queries select records.

[the_ad id=”12355″]
[the_ad id=”12356″]

To demonstrate, create a new query in design view.

Add the tables ‘Car and owner details’ and ‘Jobs’

In the Field drop down menus, select all the fields from both tables using the wildcard *, as shown.

Press F5 or click the Run Query icon.

Notice that it repeats some of the records multiple times.

To prevent this we need to create a join between the tables.

Press F4 to clear the query table.

To create an inner join, click and drag from the ‘Car Reg Number’ field in one table to the corresponding field in the other table.

This creates a connection between the two fields, that applies only to this query.

Press F5 to run the query.

Notice that it only returns records where the related Car Reg Number fields are identical, cutting down on the repetition.

To modify the inner join, right click on the connecting line between the tables and click Edit.

This brings up a Join Properties window where you can change the fields and type of join.

[the_ad id=”12397″]

Open the Relation Design window again, this time change both Update options to ‘Update cascade’ for both lines.

[the_ad id=”12355″]
[the_ad id=”12356″]

Update cascade – automatically updates any related foreign keys to match the primary key.

Also set the Delete options to ‘Delete cascade’.

In the ‘Orders’ table, try changing the record with Order Ref 201, to ‘450’.

This time it updates the cascade, meaning any related field in the foreign table will be instantly updated.

Open the ‘Order Details’ table to check. Notice that all the instances of 201 have been changed to 450.

The other two options are Set null and Set default.

Set null – if primary key is changed, this will empty any corresponding foreign key records.

Set default – if primary key is changed, this will set the foreign key record to the default specified in the field properties.

Referential integrity means controlling how records are altered or deleted in a relational database (i.e. one with links/relationships between fields in different tables).

[the_ad id=”12355″]
[the_ad id=”12356″]

To define the rules of how records are altered across relationships, you must right click on the relationship line in the Relation Design window and select Edit.

This allows you to change ‘Update’ and ‘Delete’ options.

Open the Relation Design window, and right-click on each line for the relationships made in the last example.

Set them to ‘No action’ for update and delete. No action means changing the primary key won’t affect foreign keys.

Close the window and open the ‘Orders’ table.

Try to change the Order ref for the first record to ‘450’ for instance.

You will get an error message like the one shown. This is because we have set the update option to ‘No action’.

The same would apply if we tried to delete the row, as we also set the Delete option to ‘No action’ for the relationship.

This option enforces referential integrity of the database.

For this example, create 3 new tables with the designs shown.

[the_ad id=”12355″]
[the_ad id=”12356″]

Note the Order Details table has 2 fields combined to create the primary key. To do this, hold down Ctrl key, click in the grey area to the left of each field, then right-click and select primary key. The reason for this will be explained in a later slide.

Then populate each table with the data shown.

A many-to-many relationship is used when a record in the first table can have matching records in the second and vice versa.

In this example, a single part may have many orders, and a single order may contain many parts.

Therefore, if we create a relationship between ‘Part Ref’ and ‘Order ref’ fields, it will be a many-to-many relationship. To do this we need a junction table.

We need a junction table as you cannot directly connect the two fields as there are multiple records in the Parts and Orders tables.

We can use the Order Details as the junction table. This is the reason we created two primary keys, so we can connect to both of the other tables.

Click Tools-Relationships, then add tables: Parts, Orders and Order Details

The junction table is the primary table, start by clicking ‘Order Ref’ field in the Order Details table and drag it over to the ‘Order Ref’ in the Orders table. (If you do it the other way around, it won’t work)

Then, click and drag ‘Part Ref’ from the Order Details table to the ‘Part Ref’ in the Parts table.

This makes 2, 1 – n lines, i.e. one-to-many. By linking through the junction table, overall it creates n-n, many-to-many.

Using a junction table has made a relationship between two fields, Part Ref and Order Ref, that would have been impossible otherwise.

If you don’t have a junction table, you can create one, as long as it contains foreign keys from both tables.

To modify the relationships, right-click on each line separately and select Edit.

A one-to-many relationship links the primary key to a field in a second table that may have more than one records (row).

[the_ad id=”12355″]
[the_ad id=”12356″]

Click Tools – Relationships from the main window ribbon.

The Relation Design window opens. Click ‘Insert’ – Add Tables.

Add the ‘Mechanics’ table.

Create a one-to-many relationship between the Staff Number field in the ‘Jobs’ table to the Staff Number field in the ‘Mechanics’ table.

It automatically sets it as a one-to-many relationship, with 1 at the primary end and ‘n’ at the other.

This is because there is only one staff number per mechanic, but there are more than one job record for each staff number.

To modify the relationship, right click and select Edit.

To delete the relationship, right click and select Delete.

For this section we need to create a new table.

Create a new table in design mode, with the two fields shown for the cost of repair jobs.

[the_ad id=”12355″]
[the_ad id=”12356″]

For the Cost field, select Decimal data type, set decimal places to 2, then click the ‘…’ icon next to Format example, in field properties.

Select Currency and choose a format that shows pounds and pence e.g. £1,234.00

Save the table with the name ‘Job Costs’ and close it.

Enter values for the cost of the various jobs.

This data could have been included on the main jobs table, but sometimes it is better to keep data in a separate table.

There are many reasons for doing this. You may want to split up a large table, keep some data separate for security reasons or if the second table has optional data that isn’t used very often.

As we have created a separate table for job costs, we need to link this to the main jobs table.

To do this we use a one-to-one relationship. Click Tools-Relationships in main window.

We are going to link the Job No primary key field from the primary table (Jobs) to the Job No foreign key field in the secondary table (Job Costs).

This is called one-to-one as there is only one record (row) for each Job No in each table.

After clicking on Relationships an ‘Add Tables’ box appears.

Select Jobs, click ‘Add’ then select Job Costs and click ‘Add’.

This adds both tables to the Relation Design window. Click ‘Close’ in the Add Tables box.

Leave the Relation Design window open.

Click and hold the Job No field in the Jobs table, then drag to the Job No field in the other table.

This creates a one-to-one relationship between them. (hence the 1 at each end)

This allows you to create queries that will reference between the two tables.

E.g. you can run a query that lists Job No, Car Reg No and Cost, although Cost is in a different table.

To modify the relationship, right click on the joining line and select Edit.

This opens a relations dialogue box, where you can change the fields involved.

The update options specify what happens when you make changes to the primary key, we’ll discuss this when we talk about referential integrity later in the course.

To delete the relationship, simply right-click the joining line, then click ‘Delete’.

[the_ad id=”12397″]

Open the ‘Jobs’ table in design mode.

If we want to ensure that data is always entered for a certain field, click on the relevant field.

[the_ad id=”12355″] [the_ad id=”12356″]

In the Field Properties box at the bottom of the screen, select ‘Yes’ from the drop-down menu next to ‘Entry required’.

To make it so entry for that field is not required, then make sure ‘No’ is selected from the drop-down menu.

In Base you can only create input masks in a form (not a table as in MS Access).

[the_ad id=”12355″]
[the_ad id=”12356″]

We are going to edit the date field to only allow dates to be inputted in the format DD** MMM YY (e.g. 21st NOV 15)

First we must open the Jobs table in design mode (right click then select ‘Edit’.

Then change the data type in the table of the Date field to Text, as shown and close the table.

Open the form ‘Jobs Entry’ in design mode by right clicking and select ‘Edit’.

Press ‘Ctrl’ and left click the text box next to ‘Date’ to select it

Right click, select ‘Replace with’ and select ‘Pattern field’. This allows us to create an input mask.

An input mask is like a template that only allows you to enter data in a specific way, this helps prevent errors in the data.

Right click the box and select ‘Control…’

Masks use the following characters: N=numbers 0-9, a=letters a-z, A=letters A-Z (converts lower case to upper case), L=locked i.e. this can’t be overwritten, c or C = all characters (a-z 0-9)
x or X = all printable characters

In Edit mask enter: NNaaLAAALNN to represent e.g. 07th FEB 13 (notice the spaces are L for locked)

In Literal mask enter: ____ ___ __ (that is 4 underscores, space, 3 underscores, space, 2 underscores)

This is what will appear as default in the input box on the form.

Select Yes for the ‘Strict format’ option, this means if data is entered in the wrong format it won’t be changed. This is useful for preventing mistakes in the data.

Toggle out of design mode by clicking the design mode icon, and click the ‘data source as table’ icon to view the table.

Try typing dates into the Date field. Notice that you have to follow the format set in the input mask.

If you try using the Tab key to cycle through the form, you may notice the order is wrong for the new box. Go back to design mode by clicking the design mode icon.

Click the ‘Activation order’ icon on the toolbar to open a dialogue box.

Select the date pattern field and move it up or down until it is between Description and Reg No.

To modify the mask, right click and select ‘Control’, then use the Edit mask option.

To delete the input mask, just change the box back to a text box by right clicking and selecting ‘Replace with’

[the_ad id=”12397″]