Information
Tr. Nay Myo
and Communication Technology
Chapter 18
1 18.1 Create a database structure
Databases
2 18.2 Manipulate Data
3 18.3 Present Data
18.1 Create a database structure
4 DATABASES
Import CSV files (Delimited, Field Name in First
Row, Primary Key)
Check Data Type, Field Name & Display Setting
Making Data Capture Form or not
Making Query using given criteria
Making Report or Produce labels
Making Export Report
5 DATABASES
Import CSV files (Delimited, Field Name in First
Row, Primary Key)
External New
From
Data Data Text file
File
Source Source
First Row Don’t
Delimited
Contain impot Primary
Field some key
Name fields/ not
6 DATABASES
Check Data Type, Field Name & Display Setting
Check Evidenc
Field
Data Display Screen
Name
Type Shot
7 DATABASES
Making Data Capture Form or not
SelectTable
Create Form Wizard Layout
Name& Fields
8 DATABASES
Making Query using given criteria
Query/simple SelectTable Making
Create
Wizard Name& Fields Criteria
9 DATABASES
Making Report
Select Layout
ReportWizard/ SelectTable
Create Making Sorting & Making Design
Labels Name& Fields
Orientation
10 DATABASES
Produce labels
ReportWizard/ Insert Labels Making Labels
Create Label Size Select Fields
Labels with order Design
11 DATABASES
Making Export Report
Select Desired Right Click Choose Export
Report Export as File Types
12 RELATIONAL DATABASES
• A relational database stores data in more than one linked
table, stored in a file.
• Relational databases are designed so that the same data is
not stored many times.
• The tables within a relational database are linked with
relationships
19 18.1 CREATE A DATABASE STRUCTURE
1. Import data from existing files
2. Set appropriate data types
3. Edit data types
4. Set the number of decimal places
5. Set the display format of Boolean fields
20 CREATE A FLAT-FILE DATABASE FROM AN EXISTING FILE
Import data from existing files
❑ select the External Data tab.
❑ In the Import & Link section, click on the New Data Source
icon, then from the drop-down menu select From File.
❑ From the sub-menu select the option for Text File as files saved
in .csv format are text files with each data item separated from
the next by a comma.
21 CREATE A FLAT-FILE DATABASE FROM AN EXISTING FILE
Import data from existing files
❑ The Import Text Wizard window will open.
❑ As comma separated value (.csv) files are delimited files (the
comma is the delimiter), select the Delimited radio button and
click on
❑ If the first row contains the field names click on the First Row
Contains Field Names tick box.
22 18.1.5 DATA TYPES AND SUB-TYPES
23 CREATE A FLAT-FILE DATABASE FROM AN EXISTING FILE
Set & Edit appropriate data types
❑ It can be made from the Home tab.
❑ In the Views section click on the Design View icon
Set the number of decimal places
❑ by clicking the left mouse button in the PPrice field
❑ viewing the number of Decimal Places in the General tab at the
bottom of the window.
❑ Set the display format of Boolean fields
24 18.1.6 CREATE A FLAT-FILE DATABASE FROM AN EXISTING FILE
25
18.1.8 ENTER DATA USING A TABLE
❑ Data is normally entered into a database using a form but, if a form is not asked
❑ for, it may be quicker to use the table to enter new data.
❑ To open the table in Datasheet View, double click the left mouse button on the table
name
❑ To add a New car at the bottom with a star next to it,
26 18.1.9 ADD A FIELD TO AN EXISTING TABLE
27 18.1.10 CREATE A RELATIONAL DATABASE
28 18.1.10 CREATE A RELATIONAL DATABASE
29 18.1.11 CREATE A DATA ENTRY FORM
❑ The best way to create a data entry form is to select the Create tab, then in the
Forms section, click on the Form Wizard
❑ The Form Wizard window opens.
30 18.1.11 CREATE A DATA ENTRY FORM
❑ Select the table that holds the fields
that will include in the form.
❑ Create the first form by selecting
tblNames
❑ To move each field across into the
form, use the single arrow key
❑ but, if want all the data from this table
on the form, use the double arrow
key.
31 18.1.11 CREATE A DATA ENTRY FORM
❑ Choose the layout of the
screen (Columnar)
❑ Change the title of the
form to frmStudents
that it is a form.
❑ Click on to open the
form.
32 18.1.11 CREATE A DATA ENTRY FORM
❑ The bottom of the form has a navigation bar which can be used
to move from record to record like the following Fig:
33
18.1.11 CREATE A DATA ENTRY FORM
34 18.1.12 EDIT A DATA ENTRY FORM
❑ Double click the left mouse button on formName
❑ Select the Home tab, in the Views, Select Design View
❑ From Form Design tab, from the Tools section, select the
Property Sheet icon.
❑ Move the cursor into the Form Header, Detail and Form Footer.
❑ In the Property Sheet, in this example,Text Align has been
changed to Center or
❑ The Fore Color and Back Color have also been changed
35 18.1.12 EDIT A DATA ENTRY FORM
❑ At Format Tab, data entry form is edited in control formatting
group, font group and so on..,
❑ At Form Design tab, controls section can also be used.
36 18.1.13 ADD A NEW FIELD TO AN EXISTING FORM
❑ Open tableName or tblStudents in Design View
❑ Enter the Field Name, or NewField Name or Year_Group
❑ Data type can be change as described in user desired
❑ Description can be also changed.
37 18.1.14 EDIT A DATA ENTRY FORM
Use radio buttons
❑ Drag the frame for the Option Group into the Detail section of the
form
❑ It will open the Option Group Wizard window
❑ Enter six or desired Label Names
❑ choose the top radio button option for Yes, the default choice is:
❑ It can typed in for No year group selected/Default choice.
• Page -378
38 18.1.14 EDIT A DATA ENTRY FORM
Use radio buttons
❑ Each label has a value assigned to it.
❑ need to change all the settings in this task.
❑ For Year 7 set the value to 7,Year 8 set to 8, and so on.
❑ For No year group selected set the value to 0.
• Page -378
39 18.1.14 EDIT A DATA ENTRY FORM
• Use radio buttons
• Page -378
40 18.1.15 WHAT DOES A WELL-DESIGNED FORM LOOK LIKE?
Page-379
41 18.1.15 WHAT DOES A WELL-DESIGNED FORM LOOK LIKE?
Use drop-down menus Page-379
❑ From the Design tab, in the Controls section
❑ There are two suitable options, a combo box or a list box, select a List box
❑ The List Box Wizard window opens
❑ Select the radio button for I want the list box to get the values from another
table or query
❑ In the next window, select Table: tblTeachers
❑ Double click on the SCode field to move it from the ‘Available fields:’ to the
‘Selected fields:’
❑ make sure that the ‘Ascending’ button is visible, to show the list in
alphabetical order
42 18.1.15 WHAT DOES A WELL-DESIGNED FORM LOOK LIKE?
❑ Adjust the SCode column width using the drag handle
❑ The value that selected will need to be stored in the Tutor field so
select from the Store that value in this field:
43 18.1.15 WHAT DOES A WELL-DESIGNED FORM LOOK LIKE?
44 18.2 MANIPULATE DATA
18.2.1 Perform searches or Query
❑ can search for data in Access using a
query.
❑ to select a subset of the data stored in
table.
❑ If new data is added to the table, when
opening a query again it will select the
subset from all the data, including the
new data.
45 18.2 MANIPULATE DATA
18.2.1 Perform searches or Query
❑ Select the Create tab and find the Queries section.
❑ Click on the Query Wizard icon.
❑ Opens the New Query window.
❑ Select the Simple Query Wizard
❑ In this Wizard window, make
sure that the correct table name
has been selected in the Tables/
Queries box.
46 18.2 MANIPULATE DATA
❑ In the Simple Query Wizard window,
❑ Move all of the fields into the query using the double arrow key.
❑ Select the required field, by clicking on the single arrow or double to
add/remove them
❑ Enter a name for the query,
❑ This query may be turned into a
report at some point
❑ the name given the query may
become the title for the report.
47 18.2 MANIPULATE DATA
❑18.2.1 Perform searches
48 18.2 MANIPULATE DATA
• 18.2.1 Perform searches
49 18.2 MANIPULATE DATA
A wildcard search
❑In Design View, in the Criteria: row of the Extras column,
enter the text *Alloy Wheels*
❑The stars tell Access that are performing which looks for the
words ‘Alloy Wheels’ (including the space) anywhere in the
Extras fields’ contents.
50 18.2 MANIPULATE DATA
❑when in Design View, enter <=4125 in the Criteria: row of the
SPrice column.
❑19 cars will be found using this search.
51 18.2 MANIPULATE DATA
❑Mathematical formulae can be used, with
❑< for less than,
❑> for greater than,
❑>= for greater than or equal to,
❑= for equals.
❑These mathematical formulae cannot be used for queries involving
text fields
❑but can be used for any numeric, date or time fields.
52 18.2 MANIPULATE DATA
❑when in Design View, Criteria: row of the Maths field and type
in AVA.
❑31 record will be found using this search.
53 18.2 MANIPULATE DATA
54 18.2 MANIPULATE DATA
18.2.2 Perform calculations
❑Use formulae in queries
55 18.2 MANIPULATE DATA
18.2.2 Perform calculations
❑Use formulae in queries
56 18.2 MANIPULATE DATA
18.2.2 Perform calculations
❑Present summary data in queries
❑ Create>Queries/simpleQW>>Summary option
57 18.2 MANIPULATE DATA
18.2.2 Perform calculations
❑Present summary data in queries
❑ Create>Queries/simpleQW>>Summary option
58 18.2 MANIPULATE DATA
18.2.3 Sort data in queries
❑Select the Home tab, click on the column heading for Avg of SPrice.
❑This will highlight this column like the follow.
❑In the Sort & Filter section, select the descending sort icon.
59 18.2 MANIPULATE DATA
18.2.3 Sort data in queries
❑Select the Home tab, click on the column heading for Avg of SPrice.
❑This will highlight this column like the follow.
❑In the Sort & Filter section, select the descending sort icon.
END of 18.2
61 18.3 PRESENT DATA
18.3.1 Produce a report
❑ The report created in Access will be the most suitable report for a task,
but sometimes it may be better to produce a report in a word
processor, copying and pasting information into a document.
❑ Select the Create tab and find the Reports section.
❑ Click on the Report Wizard icon to open the Report Wizard window.
❑ In the Tables/Queries box
to select the correct query.
❑ For this task the report will be based on
the query to select only the Fords (for e.g)
62 18.3 PRESENT DATA
18.3.1 Produce a report
❑ From Available Fields: into the Selected Fields: choose desired fields.
❑ Use the Layout section to choose how the page will be laid out; in this
case a Tabular format has been selected.
❑ Using the Orientation radio buttons, Select Landscape.
❑ Change the report name.
❑ If still need to add the subtitle and ensure that the layout is correct,
select the Modify the report’s design radio button.
❑ The Design View of the report will see.
63 18.3 PRESENT DATA
64 18.3 PRESENT DATA
• Report Result for Task18r
65 18.3 PRESENT DATA
66 18.3 PRESENT DATA
67 18.3 PRESENT DATA
Export data
❑ In the Navigation pane (on the left side of the window), find the report
that saved in Task
❑ Right click on the report name to get the
drop-down menu.
❑ Select the option to Export.
❑ To export into .rtf format, so select Word
RTF File
68 18.3 PRESENT DATA
Export data
69 18.3 PRESENT DATA
Hide data in a report
❑ Go into the Design View of the report;
❑ select all the controls for the required fields name.
❑ Open the Property Sheet and, in the Visible section, change the setting
from Yes to No, which will hide these controls.
❑ To set a black background for the PPrice data, in the Detail row of the
report select the control for PPrice.
❑ Move the cursor into the Property Sheet, selecting the Format tab. Find
the Back Color section
70 18.3 PRESENT DATA
Hide data in a report
71 18.3 PRESENT DATA
Hide data in a report
72 18.3 PRESENT DATA
Produce labels
73 18.3 PRESENT DATA
Produce labels
❑ Design a new query to extract only the cars with a sale price of less than £4000.
❑ selecting only the Make, Model, Colour, SPrice, Year and Extras fields from the
table step through the Simple Query Wizard
❑ close the query and click on the query so that it is highlighted
❑ From the Create tab, find the Reports section and click on the Labels icon.
74 18.3 PRESENT DATA
Produce labels
❑ In Label Wizard, select any label
format that contains two labels across
the page;
❑ Type the text ‘Special Offer’ in the grey
area as the top row of the label.
❑ Press <Enter> to move down to the
second row.
75 18.3 PRESENT DATA
Produce labels
❑ select the radio button for Modify the label design
76 18.3 PRESENT DATA
Produce labels
77 18.3 PRESENT DATA
Format reports
❑ Create a new report from all the data in the extract using the Report Wizard.
❑ In the Tables/Queries box select the profit calculation query as the source of the data.
❑ Move to the Detail row of the report. Hold down the <Ctrl> key and select the PPrice,
SPrice and Profit field controls.
❑ In the Property Sheet, select the Format tab and use the drop-down menu in the Format
section to select Euro.
78 18.3 PRESENT DATA
Format reports
❑ Create a new report from all the data in the extract using the Report Wizard.
❑ In the Tables/Queries box select the profit calculation query as the source of the data.
❑ Move to the Detail row of the report. Hold down the <Ctrl> key and select the PPrice, SPrice
and Profit field controls.
❑ In the Property Sheet, select the Format tab and use the drop-down menu in the Format
section to select Euro.
79 18.3 PRESENT DATA
Formulae in reports
❑ Click on the bottom edge of the Report Footer and drag this down about 2 cm
or desired space so that this footer is visible.
❑ Select the Design tab, move to the Controls section and select the Text Box
icon.
❑ Insert the formulae for report
80 18.3 PRESENT DATA
Formulae in reports
81 18.3 PRESENT DATA
Sort data in a report
❑ To produce this report, select the Create tab and click on the Report Wizard
icon. In the Tables/Queries box select the Ford or Vauxhall query.
❑ In the Sorting window, use the dropdown lists to select the Make, the Model
❑ Finally, for the SPrice field click on desending
82
End of Chapter 18
