Slides badge

Calculated Fields

Searching and sorting a database

  1. Find everyone who has 60 or more agility and 95 or more charisma and display their name, strength, agility and charisma from the BondCharacters table sorted by their names in reverse order (Z to A).

Searching and sorting a database

  1. Find everyone who has 60 or more agility and 95 or more charisma and display their name, strength, agility and charisma from the BondCharacters table sorted by their names in reverse order (Z to A).

Learning Intentions

  • Understand what a calculated field is
  • Understand the benefits of calculated fields

Success Criteria

  • I can create a database which could include a calculated field
  • I can create a database calculated field

What is a calculated field?

Parking lot database

  • The below table is like something used for a parking lot company which monitors the number of spaces in each parking lots daily. Rather than simply storing the number of spaces available, they calculate the number of spaces available. They do this using a calculated field that calculates the number left using:
    TotalSpaces - NumberOfSpacesInUse

Parking lot database

  • The previous table is like something used for a parking lot company which monitors the number of spaces in each parking lots daily. Rather than simply storing the number of spaces available, they calculate the number of spaces available. They do this using a calculated field that calculates the number left using:
    TotalSpaces - NumberOfSpacesInUse
Which field is the calculated field below?
How is the calculated field calculated?

Task

Which field is the calculated field below?
AmountMade
 
How is the calculated field calculated?
NumberSold * ProductCost

Task

Why bother with calculated fields?

  • Saves us having to update the database
  • They lead to fewer errors in calculations
  • They can do complex calculations in very little time

Calculated fields in Microsoft Access

  • Did you notice that we had a Calculated option when selecting the field types in Design View?

Calculated fields in Microsoft Access

TotalSpaces - NumberOfSpacesInUse

ProductCost * NumberSold
  • The following are examples of calculated fields. 

The Stock table

  • What’s the Calculated field here?
  • How is it calculated?

The Stock table

  • What’s the Calculated field here? Reorder
  • How is it calculated? If the QuantityInStock drops below the ReorderLevel field, we get a Yes

If statements

  • IIf(QuantityInStock < ReorderLevel,"Yes","No")
     
Write an expression to calculate the average stat for our Top Trumps. In this case, you have Strength, Agility and Intelligence fields. Calculate the average of all three of them.
How would you write a calculation to figure out if a car park was full or not?

Task

Write an expression to calculate the average stat for our Top Trumps. In this case, you have Strength, Agility and Intelligence fields. Calculate the average of all three of them.
(Strength + Agility + Intelligence) / 3
How would you write a calculation to figure out if a car park was full or not?
IIf(NumberOfSpacesInUse = TotalSpaces, "Full", "Spaces available“)

Task

Copy across the parking database file to your own area.
Open the database and complete the tasks.

Task

Presentation Overview
Close
JB
S3 Computer Science :: Databases :: Calculated fields
© J Balfour
21:53 | 30-05-2023
Slideshow Outline
Save progress Show presenter mode Toggle whiteboard
Dark Mode Bold Text Accessibility
Random Number Generator Timer Binary & decimal converter Show Python editor Show Knox 90
Provide feedback Help
Login
Keywords
    Sections
      Binary conversion
      Denary to binary conversion
      Binary to denary conversion
      Feedback 👍
      Accessibility

      Apply a filter:

      ×
      All slide files