Performing Calculations Using Arithmetic Operations and Numeric Functions
Calculations are fundamental to data manipulation. Arithmetic operations such as addition (+), subtraction (−), multiplication (×), and division (÷) allow you to compute new values from existing data. Numeric functions like SUM, AVERAGE, MAXIMUM, MINIMUM, and COUNT provide quick summaries of data sets.
For example, if a table contains sales figures for different products, you can calculate the total sales using the SUM function or find the average sales per product using AVERAGE.
Creating calculated fields involves writing formulae that automatically compute values based on other fields. For instance, a calculated field 'Total Price' might multiply 'Quantity' by 'Unit Price' for each record, updating instantly when data changes.
- Arithmetic operations: +, −, ×, ÷ for basic calculations.
- Numeric functions: SUM, AVERAGE, MAX, MIN, COUNT for summarising data.
- Calculated fields update automatically when source data changes.
- Example: Total Cost = Quantity × Unit Price.
Creating Calculated Fields and Controls for Real-Time Updates
Calculated fields are special fields in a database or spreadsheet that derive their values from other fields using formulae. Controls, such as form controls or dashboard elements, can display these calculated results dynamically.
For example, in a sales database, a calculated field might show the profit by subtracting 'Cost Price' from 'Selling Price'. If either price changes, the profit updates immediately without manual recalculation.
This real-time updating ensures data integrity and saves time, especially when handling large datasets.
- Calculated fields use formulae referencing other fields.
- Controls display calculated results dynamically.
- Real-time updates maintain accuracy and reduce manual effort.
- Example: Profit = Selling Price − Cost Price.
Sorting Data by One or Multiple Criteria
Sorting arranges data records in a specific order to make analysis easier. You can sort data by one criterion, such as sorting a list of students by their last name in ascending alphabetical order.
More complex sorting involves multiple criteria. For example, sorting employees first by department (ascending) and then by salary (descending) within each department.
Sorting can be ascending (lowest to highest, A to Z) or descending (highest to lowest, Z to A). This organisation helps identify trends, duplicates, or outliers quickly.
- Sort by single or multiple criteria.
- Ascending order: smallest to largest, A to Z.
- Descending order: largest to smallest, Z to A.
- Example: Sort products by category (A-Z), then by price (high to low).
Searching and Selecting Data Using Queries with Multiple Criteria
Searching data involves extracting specific records that meet one or more conditions. Queries allow you to specify these conditions precisely.
For example, to find customers from 'London' who have made purchases over £100, you apply two criteria: City = 'London' AND Purchase Amount > 100.
Queries can combine multiple criteria using logical operators to refine results and retrieve only relevant data.
- Use queries to filter data based on criteria.
- Combine criteria with logical operators: AND, OR, NOT.
- Example: Find orders where Status = 'Shipped' AND Quantity > 10.
Using Logical and Comparison Operators and Wildcards in Searches
Logical operators help combine multiple search conditions:
AND requires all conditions to be true (e.g., City = 'Paris' AND Age > 30).
OR requires at least one condition to be true (e.g., City = 'Paris' OR City = 'Lyon').
- Logical operators: AND, OR, NOT to combine criteria.
- Comparison operators: >, <, =, >=, <=, <> to compare values.
- Wildcards like * or % represent unknown characters in pattern matching.
- Example: Searching names starting with 'Jo*' finds 'John', 'Joanna'.
Applying Wildcards to Match Patterns in Data
Wildcards are special characters used in searches to represent unknown or variable characters, enabling flexible pattern matching.
For example, the wildcard '*' or '%' can replace any number of characters. Searching for 'Smi*' might return 'Smith', 'Smithe', or 'Smiley'.
The underscore '_' or '?' wildcard represents a single character. Searching for 'Jo_n' could find 'John' or 'Joan'. This is useful when exact spelling is uncertain or when searching for variations.
- '*' or '%' matches any number of characters.
- '_' or '?' matches exactly one character.
- Use wildcards in LIKE queries to find patterns.
- Example: LIKE 'A%' finds all entries starting with 'A'.
At-a-glance summary
Comparison table
| Step | Detail | Example | Exam Use |
|---|---|---|---|
| Perform Calculations | Use arithmetic operations and numeric functions to compute values. | Calculate total cost: Quantity × Unit Price. | Show formula and substitution clearly for marks. |
| Create Calculated Fields | Define fields that update automatically using formulae. | Profit = Selling Price − Cost Price. | Explain how fields update in real-time. |
| Sort Data | Arrange records by one or more criteria in ascending/descending order. | Sort employees by Department (A-Z), then Salary (high to low). | State sorting order and criteria explicitly. |
| Search Data | Use queries with criteria combined by logical operators. | Find customers where City = 'London' AND Purchase > 100. | Write correct query conditions and operators. |
| Use Operators and Wildcards | Apply AND, OR, NOT, comparison operators and wildcards in searches. | Search names LIKE 'Jo*' to find 'John', 'Joan'. | Demonstrate correct use of operators and wildcards. |
Active recall
Quick check
1. What arithmetic operation would you use to find the total cost if you have quantity and unit price?
Multiply quantity by unit price (Total Cost = Quantity × Unit Price).
2. How would you sort a list of students first by grade (highest to lowest) and then by name (A to Z)?
Sort by grade in descending order, then by name in ascending order.
3. Which logical operator would you use to find records where both conditions must be true?
AND operator.
4. How does the wildcard '*' help in searching data?
It represents any number of characters, allowing pattern matching for partial text.