Author: paul.gray

Edit a query: add, modify, remove criteria

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

Click the criteria you want to modify or remove, highlight it then press delete to remove or type to modify it. To add criteria in another field, simply click on the Criterion cell under the field name and type the criteria.

To remove a field, right click the grey box above the field name and click ‘Delete’.

To hide or unhide a field, check or uncheck the ‘Visible’ box underneath the field name. If it is checked, when you run the query (F5) the field will be shown, if unchecked it will be hidden.

To move a field, left click and hold the grey box above the field name, then drag it to the position you want.

To add a field, click in an empty Field cell and choose Field name from the drop down menu.

Run a query

There are 4 ways to run a query:

• From the Edit menu, click ‘Run Query’
• Click the ‘Run Query’ icon on the toolbar
• Press F5 on the keyboard
• From the main database window, double click the query you want to run

[the_ad id=”12397″]

Add criteria to a query using ‘NOT’ logical operator

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

From the Queries area of the main window, right click the query and click ‘Edit’.

In the Criterion row, add criteria that uses the ‘NOT’ logical operator, e.g. in Description field type “NOT=‘Home’”. This will return any data that has a description not equal to Home, i.e. Mobile

Also, select ascending from the ‘Sort’ row for the Group field to order the results
Press F5 to run the query, then F4 to close the preview.

Add criteria to a query using ‘AND’ logical operator

In the Criterion row, add criteria that uses the ‘AND’ logical operator, e.g. in Group field type “<7 AND <>2”.

This will return any data that has a value less than 7 and not equal to 2

Add criteria to a query using ‘OR’ logical operator

In the Criterion row, add criteria e.g. in the group field type =8. Then, in the ‘Or’ row below, add another criterion, e.g. < 5. This will return data for all group values that are equal to 8 or less than 5.

Use a wildcard in a query, * or %, ? or _

Selecting the wildcard asterisk * in the field column will return all the fields from the selected table.

To run a query that searches for all Last Names beginning with J, in Criterion for Last Name field, type “LIKE J%”.

The % is a wildcard that stands for 0, 1 or more characters

In this case you could also type “LIKE J*” for the same result

Press F5 to run the query.

To run a query that searches for all First Names beginning with the letter ‘Bo’ with one character after them, in Criterion for Last Name field, type “LIKE Bo?” (or ‘_’ instead of ‘?’)

‘?’ or ‘_’ is a wildcard that stands for 1 character only.

Press F5 to run the query.

[the_ad id=”12397″]

Change arrangement of data fields and headings in a report

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

Click ‘Reports’ from the Database area of the main window and right click the report you want to change in the Reports area.

Click ‘Edit’ from the drop down menu.

To move fields and labels, simply click and drag individually to move them around the report page.

Alternatively, use the ‘Select’ arrow icon to drag a selection of fields and move them as a group.
Use the alignment icons in the Report Controls toolbar to modify alignment.

To edit the page layout, drag the bars between Header, Detail and Footer up and down.

To edit the appearance or position of text fields, click the field to highlight it. Then click the Properties icon, or right click and select properties.

The properties window allows you to change position, font, alignment, formatting, etc.

To edit headers and footers in a report, click the Label Field icon on the Report Controls toolbar.

Then click and drag inside the ‘Page Header’ area of the report to create a text box.

Click the Properties icon and enter desired text into the Label box.

To run the report, click the ‘Execute Report’ icon on the toolbar.

The report will be generated as a text document.

The text box entered into the Page Header section will be repeated at the top of each page.

We could also add text to the Page Footer section to repeat text or fields at the bottom of each page.

[the_ad id=”12397″]

To create a two-table query, first make sure you have created a second table.

The two-table query will use the ‘Company employees‘ table created in a previous tutorial.

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

Use the table design view to create a table containing personal phone numbers for home and mobile

Once created and saved, you need to create a relationship between the tables, to do this first exit the table design view to return to the main window

Relationships are used in relational databases to link data in two different tables. They create a reference between a foreign key and a primary key.

We are going to link the ’employee-ID’ primary key in the ‘Employee Basic Details’ with the ’employee-ID’ foreign key in the ‘Other phone numbers’ table.

This allows a query to be created that will contain data records pulled from both tables.

Another example of this could be stock listings. The ‘item code’ may be the primary key in a main product description table, and also a related foreign key in a ‘sales’ table.

Create a relationship between two tables. Select ‘Relationships’ from the Tools menu in the main window.

Select the two tables from the list and add them one at a time.

Click and hold on the field you want to relate to a field in the other table, then drag over to the relevant field, making a relationship between them

Right click on the line that links the fields and click ‘Edit’ to edit the relationship

In the Relations window, check ‘Update cascade’ from Update Options and check ‘Delete cascade’ from Delete Options.

This means that when the related fields are edited or deleted in either table the other table will be updated also, click OK. Click the disc icon to save the relationship.

Select ‘Queries’ and select ‘Create a query in Design View’ from the Tasks area.

Then add both tables from the ‘Add Table or Query’ window.

Select fields from both tables.

Set criteria for the data you want to extract e.g. in the Group Criterion type “<=3” to show only data from Groups 1,2 or 3

Press F5 to Run Query and F4 to close the Preview window.

Click the disc icon to Save As. In the dialogue box, type the name you want to give the query, then click ‘OK’ to save it.

[the_ad id=”12397″]

Queries are used to extract and analyse data from tables. Click on the Queries icon, then select Create Query in Design View.

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

The single table query will use the ‘Company employees’ table created in a previous tutorial.

In the ‘Add Table or Query’ window, click the table(s) you want to extract data from, click ‘Add’, and then ‘Close’ to start designing a query

To add a field to the query, double click the field name in the table window and it will appear in the table below. Alternatively, click in the field cell in the table and select the name from the drop-down menu.

To delete a field, right click in the grey box above the column and click ‘delete’.

To select all fields from a table double click the asterisk. This will create a query to extract data from every field in the table – i.e. all of the records.

To run the query, select ‘Run Query’ from the Edit menu or press F5

This will open a preview window displaying the data from all fields. To close the preview select ‘Preview’ from the View menu or press F4

To add specific search criteria, select the field then type criteria into ‘Criterion’ row, e.g. <3 will return all data less than 3 in the ‘Group’ field You can add more criteria in the ‘Or’ rows, e.g. >5 will return all data greater than 5 in the ‘Group’ field

In the example, as Last Name, First Name and Group are set to visible, if we run the query (F5), these fields will display for Group records less than 3 and more than 5

Other search criteria that can be used are = (Equal), <> (Not equal to), <= (Less than or equal to), >= (Greater than or equal to)

To name the query, select ‘Save As’ from the File menu, name the query, then click ‘OK’

Example

Another example of using a query to extract data from a single table could be a bank that wants to find out customers that have savings higher than a certain threshold in their account.

A table could contain Account number, Name, Amount.

The query could then set criteria in the Amount field of >10000 for example to list all the customers that have savings of more than 10000.

This query could then be used to produce a report, displaying these customers in a user-friendly format. More on this in a later tutorial.

[the_ad id=”12397″]

Use the search command to find specific data in a field

This tutorial demonstrates how to find specific records within a table.

This won’t cover forms or reports, which can do the same thing but display the data in a different (more user-friendly) format.

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

Sometimes you may want to just quickly check data within a table rather than go to the lengths of creating forms or reports.

This most commonly happens when you are populating the table for the first time, to check on certain data. Or it could occur when you are searching data that isn’t looked at or used very often, so the benefits of setting up a form or report aren’t worth the extra effort.

Click the search icon to open a Record Search window.

Type the data to find, or select ‘Field content is NULL’ to find blank fields, or ‘not NULL’ for non-blank fields.

Select ‘All fields’ or choose a ‘Single field’ to search within.

If you want to match the exact case of that data (e.g. male, but not female), then select ‘Match Case’ from Settings and click search.

The first match will be highlighted in red in the table and the record will be stated in the Record Search window.

If you click ‘Search’ again, it will show the next match and so on, cycling through the matches.

Apply a filter to a table

To set a filter, click the ‘Standard Filter’ icon, opening a new window. Choose the Field you want to filter by (e.g. Start Date). Choose a condition (e.g. greater than or equal to >= ), and enter a value. Click OK to filter the records to show only the results that match this filter (e.g. Start Dates later than 01/02/2013)

Remove an applied filter from a table

To remove the filter, click the ‘Reset Filter’ icon, this restores the view that shows all of the records.

[the_ad id=”12397″]

Set a field as primary key

To set the primary key, right-click the grey box to the left of the field you want to set as primary key.

Check the ‘Primary Key’ option and a yellow key symbol will appear in the grey box.

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

It’s a good idea to set the primary key to an ‘Integer’ type and select Autovalue option to ‘Yes’, so that the table auto-populates with ascending unique values.

Index a field

An index is a copy of selected columns of data from a table, allowing more efficient searching of records.

For example, if we have a big table that contains fields: First Name, Surname, Gender, Age, Address. If we wanted to run a query that finds all people named ‘David’ of a certain age range, this could take a while as the computer has to search through thousands of records.

If we were to create an index that contains only the First Name field in alphabetical order, and set up the query to search only that index it will find all of the records containing ‘Dave’ within the specified age range far more quickly, even instantly.

Click the grey box to the left of the field you want to index. Then click on the Index Design icon.

Click the New Index button, choose the Index field from the drop-down menu and sort ascending or descending.

If you don’t want duplicates to be allowed for the indexed field, check the ‘Unique’ box.

Add a field to an existing table

To open an existing table, click ‘Tables’ in the Database area of the main window. Right-click the table you want to open in the ‘Tables’ area. Select ‘Edit’ from the drop-down menu.

Once you have saved the table for the first time in Base, you cannot add fields in-between existing ones. You can only add a new field below the last entered field. Use the same process as earlier to enter a field, set the type and change the properties.

Change column width

To change the column width in a table, click the vertical line joining columns and then drag right or left.

[the_ad id=”12397″]

Create a table

To create a table, click ‘Tables’ in the Database area. Then click ‘Create Table in Design View’ from the Tasks area to open table design window.

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

In this example, we’re going to create a table that holds data on company employees.

It will contain basic details on employees such as name, gender, start date, end date, address, phone number and department.

As mentioned earlier, it is a good idea to separate different types of data into different tables, so we won’t include salary data or bonus payments in this table.

We are using design view to create the table as it gives you more versatility than the ‘wizard’ option.

Specify fields with data types

Click on a cell in the Field Name column and give the data field a name.

Click in the next column ‘Field Type’ and select type of data from drop down menu. If you want the table to auto-populate with unique ascending values, select Autovalue ‘Yes’.

Finally, type a description of the data field in the ‘Description’ column.

Add desired fields to Field Name column.

Select type of data e.g. Text, Integer (whole number), Number, Date, Time, Yes/No, etc.
If it’s a field that can’t be left blank (null), click ‘Entry required’ drop down and select ‘Yes’

Name a table

Click the disc icon to save and name table. Type in the name you want to give the table and click OK to save the table.

Apply field property settings

Click on the field you want to edit. Adjust settings in the Field Properties area.

To specify the length of field entry, e.g. 2 digits, enter ‘2’ in the box.

Set a default value and change formatting if required.

Consequences of changing data types and field properties

Once the database is created and tables have been populated, changing the data types and field properties can have a range of consequences for the database.

Links are created between tables by creating relationships. If properties are changed this can invalidate the links, causing problems with queries and reports.

The more complex a database becomes, the more serious the consequences of changing data types and properties can be.

Spend extra time and thought at the beginning planning your database tables so that changes are unlikely further down the line.

[the_ad id=”12397″]

Add or delete records in a table

To add a record, click a cell in the table.

Enter valid data into the cell, for instance, for Start Date, make sure you enter it in a date format.

If you move down to the next row, and the ID is set to Integer auto-value, the next integer will automatically appear.

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

To enter data in another cell, simply click on the desired cell and type the data.

To delete an entire row of records, right click the grey box to the left of the row and select ‘Delete Rows’ from the drop-down menu.

Delete or modify data in records

To delete data in a record, either left click on the record then delete manually, or right click the cell and select ‘Delete’ from the drop-down menu.

To modify data, simply delete existing data in the record and type in new data.

View a table in design mode

Right click the table, query, form or report in the relevant area and select ‘Edit’

This will open the table, query, form or report in a design mode window

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

Navigate between records in a table, query, form

Use the navigation toolbar to navigate between records in a table, query or form.

Use the number entry box to jump straight to a specific record by typing the number and pressing the enter key.

Click the ‘Next Record’ and ‘Previous Record’ arrows to move one record at a time.

Use the ‘Last Record’ or ‘First Record’ arrows to skip to first or last record in the table, query or form.

Sort records in a table, form, query output

Click the ‘Sort’ icon in the navigation toolbar to sort records in a table, query or form.

Choose the Field you want to sort the records by, from the ‘Field name’ drop down menu.

Select either ascending or descending from the ‘Order’ drop down menu.

[the_ad id=”12397″]