Data Manipulation
While importing files, make sure you select text file and not excel
file, as .csv files are plain text.
When they ask for a set number of decimal places in a field, choose
Fixed in Format.
Ensure that the dates displayed in the table are in the correct
format under Table Design view.
To form a relationship between two tables, go to the Relationships
section of the Database Tools tab and choose the required tables.
The calculations will ONLY work if placed in the report footer. If Σ
Total is used this then it apperars automatically in the report footer.
=COUNT(\[Primary_field\])
Count number of fields in
Always use primary field as it is the only
a report
unique field
Sum of fields in a report =SUM(\[Field_name\])
Average of fields in a
=AVG(\[Field_name\])
report
Minimum of fields in a
=MIN(\[Field_name\])
report
Maximum of fields in a
=MAX(\[Field_name\])
report
) | | Yes | Find values that are Yes/True | | No | Find values that are
No/False | | #01/01/2009# | Find a specific date | | >01/03/2009 | Find
dates after a given date | | <01/03/2009 | Find a date before a given date
| | Between 01/03/2009 and 31/03/2009 | Find dates in a given range |
Sorting Data in Databases
Sorting is a crucial function in databases. It helps organise and present data in a
meaningful way.
Using a Single Criterion to Sort Data
You can sort data based on a single criterion - such as by name, date, or
numerical value
For example, you might sort a list of students in ascending order by their last
names
To sort the customer's tables by LastName in either ascending or descending
order:
1. Open the table in Datasheet View
2. Click on the column header for the field to be sorted. For example, a table of
customers to be sorted by LastName, click on the LastName column header
3. Click on the "Sort Ascending" or "Sort Descending" button in the toolbar at the
top of the screen
Using Multiple Criteria to Sort Data
You can also sort data based on multiple criteria
For instance, you might want to sort a list of products first by category
(ascending), and within each category, by price (descending)
To sort the customer's table first by City, and then by LastName within each
city:
1. Open the table in Datasheet View
2. Click on the column header for the first field to be sorted. For example,
sorting by City and then by LastName within each city, first, click on
the City column header
3. Click on the "Sort Ascending" or "Sort Descending" button in the
toolbar
4. Next, hold down the Shift key and click on the column header for the
second field to be sorted by (LastName in this example)
5. While still holding down the Shift key, click on the "Sort Ascending" or
"Sort Descending" button again
Ascending and Descending Order
Ascending Order - Data is sorted from smallest to largest (e.g., from A to Z,
or from 1 to 100)
Descending Order - Data is sorted from largest to smallest (e.g., from Z to A,
or from 100 to 1)
1. Open the Query Design View
2. Add the table you want to query
3. Drag the field you want to query to the QBE grid. For instance, if you're looking for
customers from a specific city, drag the City field
4. In the Criteria row under this field, type the value you're looking for (e.g., 'London')
Using Multiple Criteria to Select Subsets of Data
You can also use multiple criteria to select data. For instance, you might want to
select all customers from a specific city who have also purchased in the last month
E.g. to return all customers from London who purchased in the last 30 days:
1. Follow the steps above to start a new query and add the City field with 'London' as
the criteria
2. Drag another field you want to query to the QBE grid. For example, if you're looking
for customers who purchased in the last month, drag the LastPurchaseDate field
3. In the Criteria row under this field, type Date()-30
Using Operators to Perform Searches
AND - Returns true if both conditions are met
OR - Returns true if at least one condition is met
NOT - Returns true if the condition is not met
LIKE - Returns true if the value matches a pattern (used with wildcards)
>, <, =, >=, <=, <> - These are comparison operators. They return true if the
comparison between the values is correct
Using Wildcards to Perform Searches
Wildcards are used with the LIKE operator to search for patterns. The most common
wildcard characters are:
o % - Represents zero, one, or multiple characters
o _ - Represents a single character
E.g. to return all customers whose names start with 'J':
1. Start a new query and drag the field you want to query to the QBE grid. For example,
if you're looking for customers whose names start with 'J', drag the Name field
2. In the Criteria row under this field, type J*
Exam Tip
Remember, the exact steps and symbols used for wildcards may vary depending on
the specific DBMS and its version. In Microsoft Access, the asterisk (*) is used as the
wildcard character to represent multiple characters, while the question mark (?)
represents a single character
When referring to field names from the exam question, make sure you copy it exactly
the way it appears in the question
Make sure you give the information asked for in the question and not a different field
Worked example
A student is setting up a database of information about volcanoes for a Geography
project. The following is part of the database.
Name_of_volcano Country Height State Last_eruption Volc
Use Japan 731 Active 2001 Stra
Tor Zawar Pakistan 2237 Dormant 2010 F
Datong China 1882 Extinct 450 Cin
Changbaishan China 2744 Active 1903 Stra
Stromboli Italy 926 Active 2016 Stra
Tengchong China 2865 Dormant 1609 Pyroc
Wudalianchi China 597 Dormant 1721 Mu
Operators such as AND, OR, NOT, LIKE, >, >=, <, <=, =, <> can be used to search
the volcano database. The search criteria for all the dormant volcanoes with a height
of less than 1000 metres would look like this:
State = “Dormant” AND Height < 1000
Use only the given operators and data to:
a. write down the search criteria that will produce a list of all the volcanoes that are
not extinct in China that also last erupted before the year 1900.
[6]
State = NOT ‘Extinct’ AND Country = ‘China’ AND Last_eruption < 1900
State = – 1 mark or State <> [1]
NOT ‘Extinct’ – 1 mark or <> ‘Extinct’ [1]
AND Country [1]
= ‘China’ [1]
AND Last_eruption [1]
< 1900 [1]
b. write down the names of the volcanoes that match the requirements of part (a).
[2]
Tengchong [1]
Wudalianchi [1]
c. The data is sorted into ascending order of height. Write down the name of the
volcano which would now be in the first record.
[1]
Wudalianchi [1]
1. Open the Query Design View
2. Add the table you want to query
3. Drag the field you want to query to the QBE grid. For instance, if you're looking for
customers from a specific city, drag the City field
4. In the Criteria row under this field, type the value you're looking for (e.g., 'London')
Using Multiple Criteria to Select Subsets of Data
You can also use multiple criteria to select data. For instance, you might want to
select all customers from a specific city who have also purchased in the last month
E.g. to return all customers from London who purchased in the last 30 days:
1. Follow the steps above to start a new query and add the City field with 'London' as
the criteria
2. Drag another field you want to query to the QBE grid. For example, if you're looking
for customers who purchased in the last month, drag the LastPurchaseDate field
3. In the Criteria row under this field, type Date()-30
Using Operators to Perform Searches
AND - Returns true if both conditions are met
OR - Returns true if at least one condition is met
NOT - Returns true if the condition is not met
LIKE - Returns true if the value matches a pattern (used with wildcards)
>, <, =, >=, <=, <> - These are comparison operators. They return true if the
comparison between the values is correct
Using Wildcards to Perform Searches
Wildcards are used with the LIKE operator to search for patterns. The most common
wildcard characters are:
o % - Represents zero, one, or multiple characters
o _ - Represents a single character
E.g. to return all customers whose names start with 'J':
1. Start a new query and drag the field you want to query to the QBE grid. For example,
if you're looking for customers whose names start with 'J', drag the Name field
2. In the Criteria row under this field, type J*
Exam Tip
Remember, the exact steps and symbols used for wildcards may vary depending on
the specific DBMS and its version. In Microsoft Access, the asterisk (*) is used as the
wildcard character to represent multiple characters, while the question mark (?)
represents a single character
When referring to field names from the exam question, make sure you copy it exactly
the way it appears in the question
Make sure you give the information asked for in the question and not a different field
Worked example
A student is setting up a database of information about volcanoes for a Geography
project. The following is part of the database.
Name_of_volcano Country Height State Last_eruption Volc
Use Japan 731 Active 2001 Stra
Tor Zawar Pakistan 2237 Dormant 2010 F
Datong China 1882 Extinct 450 Cin
Changbaishan China 2744 Active 1903 Stra
Stromboli Italy 926 Active 2016 Stra
Tengchong China 2865 Dormant 1609 Pyroc
Wudalianchi China 597 Dormant 1721 Mu
Operators such as AND, OR, NOT, LIKE, >, >=, <, <=, =, <> can be used to search
the volcano database. The search criteria for all the dormant volcanoes with a height
of less than 1000 metres would look like this:
State = “Dormant” AND Height < 1000
Use only the given operators and data to:
a. write down the search criteria that will produce a list of all the volcanoes that are
not extinct in China that also last erupted before the year 1900.
[6]
State = NOT ‘Extinct’ AND Country = ‘China’ AND Last_eruption < 1900
State = – 1 mark or State <> [1]
NOT ‘Extinct’ – 1 mark or <> ‘Extinct’ [1]
AND Country [1]
= ‘China’ [1]
AND Last_eruption [1]
< 1900 [1]
b. write down the names of the volcanoes that match the requirements of part (a).
[2]
Tengchong [1]
Wudalianchi [1]
c. The data is sorted into ascending order of height. Write down the name of the
volcano which would now be in the first record.
[1]
Wudalianchi [1]
Producing Reports to Display Data
Reports should display all the required data and labels in full. For example, if you're
creating a sales report, it should include all relevant fields, like product name,
quantity sold, and total sales
Using Appropriate Headers and Footers
Report Header: This appears at the beginning of the report. This is typically where
you would put the report title and other introductory information
Report Footer: This appears at the end of the report. This is where you might put
summary or conclusion information
Page Header: Appears at the top of each page. This might contain the page number
and the date
Page Footer: Appears at the bottom of each page. This might also contain the page
number and the date
Setting Report Titles
The report title should be set in the report header. It should be clear, concise, and
accurately reflect the contents of the report
Producing Different Output Layouts
You can control the display of data and labels in your report. For example, you might
choose a tabular format, where data is arranged in rows and columns, or a columnar
format, where each data field is listed vertically
Aligning Data and Labels
Data and labels should be aligned appropriately. For example, numeric data is often
right-aligned, and decimal points should be aligned for easy comparison
Controlling the Display Format of Numeric Data
You can control the number of decimal places displayed, the use of a currency
symbol, and the display of percentages. For example, a total sales field might be
displayed with two decimal places and a currency symbol
to add buttons in Microsoft Access, you can follow these steps:
1. Open the form in Design View.
2. Click on the Command Button icon in the Controls group of
the Design tab.
3. Click and drag the cursor to create a button on the form.
4. The Command Button Wizard will appear. Select the Form
Operations category and choose the operation you want the
button to perform.
5. Follow the prompts to complete the wizard and customize the
button as desired.
