How to Add a Calculation to a Query (Grade 12 NSC Matric Computer Application Technology): Revision Notes
How to Add a Calculation to a Query
Introduction
Queries are powerful tools that help you find specific information in your database. One of the most useful features is the ability to add calculations to queries, which allows you to create new fields that perform mathematical operations on your existing data. This makes your queries much more accurate and helps you generate detailed, specific reports.
Adding calculated fields
When working with student databases containing test scores and exam results, you often need to calculate averages or totals. Instead of doing this manually, you can add a calculated field directly to your query that automatically performs these calculations for you.
Creating a basic calculation
To add a calculation to your query, you need to understand the fundamental process of creating these dynamic fields:
Worked Example: Creating an Average Calculation
Step 1: Open your database and navigate to the Query in Design View
Step 2: Select a new field and type the field name followed by a colon
Step 3: Enter your calculation using mathematical operators: Average: (Test 1)+(Test 2)+(Exam)/3
Step 4: Switch to Datasheet View to verify your results
For calculating a student's average across three assessments, the complete field would look like:
Important calculation rules
When creating calculations in queries, understanding the syntax and mathematical principles is crucial for success:
Critical Calculation Rules:
- Use square brackets around field names to ensure the database recognises them correctly
- Apply mathematical operators such as plus (+), minus (-), divide (/), and multiply (*)
- Use brackets to control the order of operations - just like in mathematics, brackets are calculated first, followed by multiplication and division, then addition and subtraction
You can also use text functions within calculated fields. For instance, :coderef[Code([field name],2)] will display only the first two characters of the text in that field.
Formatting calculated fields
Once you've created your calculated field, you might want to change how the results appear. This is particularly useful when working with currency or percentages.
Using the Property Sheet
The Property Sheet is a powerful tool that gives you complete control over how your calculated results are displayed:
To access formatting options:
- Right-click on your calculated field in Design View
- Select "Properties" from the menu
- Choose your preferred format from the Format dropdown
This approach ensures your data is presented in the most appropriate way for your audience.
The formatting options include:
- General Number: Displays numbers as entered (3456.789)
- Currency: Shows values with currency symbol (R3 456,79)
- Euro: Displays with Euro symbol (€3 456,79)
- Fixed: Shows set number of decimal places (3456.79)
- Standard: Includes thousands separators (3 456,79)
- Percent: Converts to percentage format (123.00%)
- Scientific: Uses scientific notation (3.46E+03)
Using operators to philtre results
Calculations can enhance how your data is displayed, but operators help you find exactly the information you need. Database operators allow you to combine multiple search conditions and create very specific queries.
Logical operators
Logical operators are fundamental tools that help you combine two or more conditions in your search criteria:
And operator: This operator only returns results when both conditions are true. For example, if you want students who scored above 80 in Test 1 AND also took the exam, both conditions must be met.
Or operator: This operator returns results when at least one condition is true. You might use this to find students who either scored below 40 in Test 1 OR didn't take the exam.
Not operator: This operator returns the opposite result. Use "Not" to exclude specific values, such as finding all students who are NOT in Class B.
Special operators
Special operators help you work with missing or incomplete data, which is common in real-world databases:
Is Null: This checks for empty fields. Use this when you want to find records where no data has been entered, such as students who haven't taken an exam yet.
Is Not Null: This finds records that contain data. You'd use this to identify students who HAVE completed their exams.
Wildcard operators
Wildcard operators are extremely useful for searching text fields when you don't know the exact spelling or want to find patterns:
Understanding Wildcard Patterns:
Asterisk (*) operator: This represents multiple unknown characters. For example, searching for "he*" would find "hello", "heart", and "helicopter". You can place it anywhere in your search term.
Question mark (?) operator: This represents exactly one unknown character. Searching for "h?llo" would find "hello" and "hallo", but not "hllo" or "heello".
Practical examples
Understanding how these operators work in practice is essential for creating effective queries:
Worked Example: Using Operators Effectively
Finding absent students: Use the :coderef[IS NULL] operator in the Exam field to identify students who didn't take the exam.
Filtering by performance: Use :coderef[>=80 OR <40] in the Test 1 field to find students who either performed excellently or need extra support.
Email domain searches: Use :coderef["*gmail.com*"] in the Email field to find all students with Gmail accounts.
Combining operators
You can use multiple operators together to create very specific searches. When you place operators in the same criteria row, the database uses the AND operator automatically. When you place operators in different criteria rows, it uses the OR operator.
Operator Placement Rules:
- Same row: Test 1 >=80 AND Exam IS NOT NULL (students who scored well AND took the exam)
- Different rows: Test 1 >=80 OR Test 1 <40 (students who either excelled or struggled)
Understanding this placement is crucial for getting the results you expect from your queries.
Exam tips
When preparing for assessments on database queries, focus on these critical areas that commonly appear in examinations:
Common Mistakes to Avoid:
- Forgetting brackets around field names in calculations
- Mixing up * and ? wildcard operators
- Placing criteria in wrong rows when trying to create OR conditions
- Not checking calculation results for mathematical errors
Always test your queries in Datasheet View to verify they're working correctly. This practice helps you catch errors before submitting your work and ensures your calculations produce the expected results.
Key Points to Remember:
- Calculated fields allow you to perform mathematical operations directly within your queries, making them more powerful and accurate
- Square brackets around field names are essential for calculations to work properly in database queries
- The Property Sheet lets you format calculated results as currency, percentages, or other number formats for better presentation
- Logical operators (And, Or, Not) help you combine multiple search conditions to find exactly the data you need
- Wildcard operators (* for multiple characters, ? for single characters) are perfect for searching text fields when you need pattern matching
- Mathematical operator precedence follows standard rules: brackets first, then multiply/divide, finally add/subtract
- Operator placement determines whether conditions are combined with AND (same row) or OR (different rows)