Category: Base

Understand that a report is used to print from a table or query

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

Reports are used to extract data from a table or query, by exporting to a printable text file.

It can be saved and run again if more reports are created or data is modified.

Because database reports simplify and filter the records contained in tables or queries and displays them in an efficient way, they are useful for analysing data.

For example, an airline flight booking database might produce a report to list the names of people on a certain flight.

Running a report like this will remove extra data that may not be relevant to the required report, such as cost of flight, age of customer, etc.

Other reports could be created from the same query, such as a report listing special requirements of passengers, i.e. disabilities, dietary requirements, etc.

Use a form to insert new records

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

To insert a new record, click the ‘New Record’ icon on the form navigation toolbar.

You can enter the data straight into the field boxes on the form.

Note that if you have set the ID to an integer in the table, this will generate the value automatically in increments of 1 for this field.

Once you have finished entering the record, click the disc icon to save the record.

Use a form to delete records

To delete a record, first find the one you want to delete by using the forward and backward arrows in the navigation toolbar.

Alternatively, click on the search icon to find the record by specific field data.

Once you have found the record you want to delete, click the ‘Delete Record’ icon from the toolbar.

Use a form to add, modify, delete data in a record

First, find the record you want to edit by clicking on the Search icon in the toolbar.

Use the menu to search, e.g. ‘single field – Last Name’, Search the ‘beginning of field’ for the text ‘J’ will return records with first letter of surname ‘J’.

Keep clicking ‘Search’ button until you find the record you want e.g. Jones or Jenkins.

Once you have the record you want, click the data you want to add or change.

Type data into the field box to add or modify existing data.

Highlight the data and press delete to remove the data.

Click the disc icon on the form navigation toolbar to save the changes to the record.

Add, modify text in headers, footers in a form.

First open the form in Design view. To do this, right click the form in the main window and select ‘Edit’.

To add text, click on the ‘Text Box’ icon on the Form Design toolbar.

Click and drag on the form where you want the text to go.

Type the text you want to display on the form, e.g. Form title in the header and instructions in the footer.

To modify the text, click inside the text box.

Use the Text Object toolbar to change the font, text size, style, etc.

[the_ad id=”12397″]

The simplest way to create a new form is to use the ‘Wizard’.

Click the ‘Forms’ section in the Database area of the main window and select ‘Use Wizard to Create Form’ from the Tasks area.

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

Use the drop-down menu to choose the table you want to display.

Select the fields you want to display by double clicking them in the ‘Available fields’ section one by one. If you want to display all fields, click the ‘fast forward’ icon.

Keep clicking ‘Next’ until you get to ‘5. Arrange controls’ menu.

Try clicking on the different ‘Arrangement of the main form’ icons until you find a display you like.

You can also change the label placements by clicking align left or right.
Once you’re happy with the layout, click ‘Next’.

If you want the form to be used for entering new data only, with no existing data shown, click the top option.

If you want all data to be shown, click the bottom one.

You can also check or uncheck options to disallow modification, deletion or addition of new data, depending on your needs.

Click Next until you get to the ‘Apply styles’ menu.

Click to change the background colour of the form. You can also change the style of the field border.

Click ‘Next’ when done.

[the_ad id=”12397″]

Name the form by typing in the dialogue box. You can use the same name as the used table if you like (this is the default).

If you want to do some further modifications in ‘Form Design View’ select ‘Modify the form’.

If you want to start using the form select ‘Work with form’ and click ‘Finish’.

Understand that a form is used to display and maintain records.

Forms display records one at a time in a format that can be customized.

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

Rather than reading the data from a table, it can be laid out in a way to suit your needs.
Forms provide a way for users of the database to add or edit data in a simpler fashion than entering directly into a table

An example of a database where a form could be useful is in stock taking.

A table may contain the fields Item Code, Date, Number of units on shelf, Number of units in warehouse.

A simple form can be created for this data, that allows for easy entry of stock inventory.

Bigger businesses tend to use software that automates this process by using barcode scanners that will automatically populate a database, but the principle is the same. Using a form is a cheaper alternative for smaller or growing businesses.

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″]