This lecture will introduce the idea of Reports. These are paper printouts of the data within the database.
We will look at further form development.
We will also examine the concept of redundancy.
Reports
The design process for a Report is very similar to that of a Form, but the main difference is that a report is designed to be printed on paper for distribution and a form is designed to be viewed on-screen for either instant viewing of data or data entry/ updates.
Let us think about the way in which we wish to present our data. Firstly, we need to define the information that we wish to print.
Now we need to ask ourselves a few questions:
It is entirely possible that an existing query has the elements already defined within itself to satisfy your report, saving the job of creating a report from scratch.
If you do need to create a Query from scratch, you must refer to your answers to the above questions.
When designing a Query, you have the opportunity to specify the way in which the database will present the information in the report.
You can select the method by which the database elements are ordered in the query, either ascending or descending. You can choose to filter the information too.
Example
You have a large database and wish to produce two reports to display the information concerning Student details for the two sections that deal with groupings of students. Often, when a large number of items are present, there is some division of information so that each department has its own data to work on.
The University often splits students into Surname groups, say those whose surnames fall into the grouping 'A' to 'H' and those who fall into the grouping 'I' to 'Z'.
This is done by specifying some Criterion within the criteria field of the query.
What would we need to put in the Criteria field of one query to display Students' surnames in the set 'A' to 'H' ? Similarly we need to define a criterion to group the surnames from 'I' to Z. What would we use here?
<"I*" for the set 'A' to 'H' >"I*" for the set 'I' to Z
The report itself now needs to be named appropriately and explicitly so that anyone who picks it up may see at a glance what information it contains.
There is a header field that will appear at the top of the report. This is the Report Header. It appears only once (you may have multiple pages to print). You can use this to announce the name of the report, for whom it is intended and maybe a brief summary of the contents of the report.
There is also a Page Header that will appear at the top of individual pages in the report. This can be used to print information you may wish to be seen at the top of each page.
Similarly, the same two options are available for Footer.
Redundancy occurs when information in our Tables is badly structured and could be held in more than one table to avoid unnecessary duplication.
Redundancy occurs when database information appears more than once in a table and may be rectified by identifying the repetitions and grouping these in a new table.
Consider the table shown below:
| EmployeeID | Surname | FName | Department |
| 1 | Smith | John | Manufacturing |
| 2 | Dubrovski | William | Sales |
| 3 | Jones | Terry | Cleaners |
| 4 | Blair | Tony | Cleaner |
| 5 | Cook | John | Sales |
| 6 | Straw | Terry | R&D |
| 7 | Smith | Edward | Manufacturing |
Here you can see some repetitions. Where are the major repetitions?
Can you think of any problem that this may cause?
If a department was renamed, our database would have to be updated, line by line and with a large table, this may take a long time and perhaps be a source of errors. What about Spelling Mistakes? What problem could this cause?
What do you think could be done about this?
The best solution would be to provide another table related to departments and link this to the Employee table.
Now we have two tables in which DepartmentCode is the Primary Key of the table Department and the Foreign Key of Employee.
What type of relationship would you define this as?
There is a process available to help control redundancy. This theoretical approach is time consuming, but does work.
Access allows us to analyse our original database and convert it according to the rules set out in the theory. Use Tools, Analyse, Tables and then follow the instructions to help define any new tables that may be created.
Derived Fields
There are times when we might want to display information that has been derived from data we have collected. In a Transport table we may have a costing per week for the mode of transport and also the number of weeks the transport method has been used. It may be useful to be able to display a costing for the transport over the number of weeks that the transport was used.
Expression builder allows us to do this.
In the Lab
To create a report, select the Database view and choose New. You are now
Whilst in design mode for the Report (or indeed Form) you may decide that you wish to show or print the current date. This may be accomplished by including an unbound display box, then using the expression builder to bind the box to current date. This is found by highlighting the box you wish to display the date and clicking on the Properties icon and selecting Control Source. On the right of the entry area, an icon with three dots appears. This is your gateway to the expression builder. On the left hand window, double-click 'Functions' then double click Built-in Functions. Select Date/Time from the centre box and you will be presented with a set of options in the right-most box. If you double click Date, it will appear in the space at the top of the expression builder. Now click OK and the date will appear in the position that you selected the box to appear in on the report (or form).
Deriving a value for a field in a report or a form can be accomplished when the form or report is in Design view. Create an unbound text box from the toolbox. Label the field appropriately e.g. Total Cost per Semester. Now highlight the unbound control and select Properties from the top toolbar. Search for control source and choose expression builder.
You can then select values that need to be added or multiplied etc. from the left window. You may take data from tables or queries. Expand the trees so that you can see the items within the tables or queries that you wish to operate on. Double click the fields you wish to operate on then select the appropriate mathematical operation to complete the derived value in the order that you would write them as a sum.
When you are finished click OK. If you get error messages, you have a mis-match of data types e.g. you are trying to multiply a text value by a number.
Theory
Ideally, a table has a primary key. Some tables have a composite primary
key, that is one which is composed of two or more attributes. The primary
key is composed of one or more attributes and uniquely defines an
entity’s attribute values. So if EMP_CODE is the EMPLOYEE primary key,
then knowing that value will mean that you will also know the employee’s
other attributes. The employee’s attributes are determined by the primary
key. To be more precise, the attributes that are not part of the primary
key in each table are dependent on that primary key or to be even more
precise, the attributes that are not part of the primary key display functional
dependency with respect to the primary key.
| ORDER_NUM | CUST_CODE | ORDER_DATE | CUST_NAME | PROD_DESC | PROD_PRICE | PROD_QUANT |
|
|
|
|
Williams | Hammer |
|
|
|
|
|
|
Williams | Screwdriver |
|
|
|
|
|
|
Johnson | Clipper |
|
|
|
|
|
|
Johnson | Screwdriver |
|
|
|
|
|
|
Johnson | Crowbar |
|
|
|
|
|
|
Johnson | Saw |
|
|
|
|
|
|
Lorenzo | Hammer |
|
|
|
|
|
|
Kopuisko | Saw |
|
|
|
|
|
|