Be able to set sub-types of numeric data including percentage, number of decimal places

Topic 18: Databases – Setting Numeric Sub‑types (Cambridge IGCSE ICT 0417)

Learning Objective

Students will be able to choose the correct field type, size and sub‑type for numeric data (including percentages and a fixed number of decimal places), set the appropriate Format and Validation properties, and explain how these choices affect data entry, calculations, sorting, searching, importing, and reporting.

Why Sub‑types Matter

• Data‑entry control – prevents non‑numeric input or values outside an allowed range.
• Accurate calculations – the stored value (e.g., a decimal fraction for a percentage) is used directly in queries, forms and reports.
• Consistent presentation – the Format property determines how the value appears on screens and printed reports.
• Sorting & searching – numeric values stored correctly sort numerically and can be searched with numeric criteria.
• Import / export reliability – the defined sub‑type tells the DBMS how to interpret numbers when data are moved between files (CSV, Excel, etc.).

Key Terminology (Cambridge IGCSE ICT)

Choosing the Correct Field Type & Size

• Number vs. Text – Use Number when the value will be used in calculations, sorting or searching. Use Text only for identifiers that contain letters or symbols (e.g., product codes like “A‑12”).
• Field Size for whole‑number keys

  • Byte – 0 to 255 (suitable for very small lookup tables).
  • Integer – –32 768 to 32 767.
  • Long Integer – –2 147 483 648 to 2 147 483 647 (default for most ID fields).

• Field Size for decimal data

  • Single – ~7 decimal digits of precision (good for simple percentages).
  • Double – ~15 decimal digits (recommended for percentages that need two or more decimal places).
  • Currency – fixed‑point with four decimal places, ideal for money.
  • Decimal – user‑defined precision and scale (available in some DBMSs).

Primary & Foreign Keys – Numeric Sub‑type Requirements

• Keys must be whole numbers; therefore the sub‑type is normally Integer or Long Integer.
• Do not use Percentage, Decimal or Floating‑point as a key sub‑type – this would break referential integrity and is not permitted by the syllabus.
• When linking two tables, ensure that the foreign‑key field has the exact same data type, field size and sub‑type as the primary‑key field it references.

Numeric Sub‑type Options (IGCSE ICT)

Setting a Numeric Sub‑type – General Procedure (Access & LibreOffice Base)

1. Open the table in Design View.
2. Select (or create) the field you wish to define.
3. In the Data Type column choose Number (or Currency where available).
4. Pick an appropriate Field Size that covers the range and precision you need.
5. In the Field Properties pane set the Sub‑type:

   • Percentage for rates.
   • Decimal (or Currency) for monetary or measurement values.
   • Integer for IDs and keys.

6. Define the Format property to control how the value appears:

   • Percentage with one decimal place → 0.0%
   • Currency with two decimal places → £0.00
   • General decimal → 0.00

7. (Optional) Set a Validation Rule to restrict entry:

   • Percentage field → >=0 AND <=1 (because the stored value is a fraction).
   • Tax rate up to 20 % → >=0 AND <=0.20.
   • Positive price → >=0.

8. Enter a helpful Validation Text (e.g., “Enter a value between 0 % and 100 %”).
9. Save the table design.

Form‑Design Considerations

• When a field’s sub‑type is Percentage, the form control automatically appends a “%” symbol and will reject any entry greater than 1 (i.e., 100 %).
• For Decimal or Currency fields, set the control’s Input Mask or Format property to match the table format (e.g., £0.00).
• Use Combo Box or Option Group for fields that have a limited set of percentages (e.g., 5 %, 10 %, 15 %).
• Provide clear Validation Text on the form so users understand why a value is rejected.

Example 1 – Setting a Percentage Field (Discount %)

1. Create a field called DiscountRate.
2. Data Type → Number.
3. Field Size → Double (gives enough precision for two‑decimal‑place percentages).
4. Sub‑type → Percentage.
5. Format → 0.0% (one decimal place).
6. Validation Rule → >=0 AND <=1.
7. Validation Text → “Discount must be between 0 % and 100 %”.

Result

• User types 15 → DBMS stores 0.15 and displays 15.0 %.
• In a query the discount can be used directly: FinalPrice: [Price] * (1 - [DiscountRate]).

Example 2 – Setting a Fixed‑point Decimal Field (Unit Price)

1. Create a field called UnitPrice.
2. Data Type → Currency (or Number with sub‑type Decimal if Currency is unavailable).
3. Field Size → Currency (stores four decimal places internally).
4. Sub‑type → Decimal (or leave as default when using Currency).
5. Format → £0.00 (two decimal places for display).
6. Validation Rule → >=0 (price cannot be negative).
7. Validation Text → “Price must be zero or positive”.

Result

• Entering 3.5 is stored as 3.5000 and shown as £3.50.
• Calculations retain the two‑decimal precision, avoiding unexpected rounding errors.

Calculations & Aggregate Functions

• Using a Percentage field in a calculation – because the stored value is a fraction, multiply directly:

  FinalScore: [RawScore] * (1 - [PenaltyRate])

• Summing percentages – if you need the total as a percentage, first multiply by 100:

  TotalPct: Sum([DiscountRate])*100

• AVERAGE, SUM, MIN, MAX work on any numeric sub‑type. The result retains the field’s internal precision; the displayed format is applied only when the value is shown on a form or report.
• When a Decimal or Currency field is used in an aggregate function, the DBMS keeps the defined number of decimal places (e.g., two for money).

Round‑off Behaviour

• The Format property controls only the *display*. If a field stores 3.567 and the format is 0.00, the screen shows 3.57 but the underlying value remains 3.567.
• To force permanent rounding, use a validation rule such as Round([FieldName],2)=[FieldName] (Access) or round the data before it is saved.

Impact on Sorting, Searching and Importing

• Sorting – percentages stored as fractions sort numerically (0.10, 0.20, …) rather than alphabetically (“10 %”, “2 %”).
• Searching – queries must use the stored fraction. Example: WHERE DiscountRate > 0.15 finds discounts greater than 15 %.
• Importing CSV / Excel data

  1. Run the import wizard and map the source column to a Number field.
  2. Choose the same Sub‑type you will use in the destination table (e.g., Percentage).
  3. If the source values contain the “%” symbol (e.g., “85%”), the wizard should strip the symbol and store the fraction 0.85. If the wizard treats the column as plain text, the values will be stored as 85, which is incorrect for calculations.

Presenting Numeric Fields in Reports

• Set the Format property of the report control (e.g., a textbox) to match the table format (0.0% for percentages, £0.00 for currency).
• Because the stored value for a percentage is a fraction, the report will automatically display “85 %” while calculations in the report (e.g., [Score]*[Weight]) use the underlying 0.85 value.
• Consistent formatting ensures that printed mark‑schemes, invoices or statistical summaries are easy to read and free from rounding surprises.

Practice Questions

1. In a table you need a field called ExamScore that stores marks out of 100 and displays them as percentages with one decimal place. Write the complete set of steps (including field size, sub‑type, format and validation rule).
2. A field for “TaxRate” must allow values such as 5.5 % and 7.25 %. Which sub‑type, field size and format should you choose? Include an appropriate validation rule.
3. Explain how the value 123.456 is stored and displayed when the field’s sub‑type is Decimal (format 0.00) versus when the sub‑type is Floating‑point (no specific format). What are the practical consequences for calculations?
4. When a field is set to the Percentage sub‑type, what exact value is stored in the database if a user enters 12.5% on a form? Show the stored value and the displayed value.
5. Describe how you would import a CSV file that contains a column “Discount” with values like “10%”, “15%”, “20%”. What steps ensure the values are stored correctly as percentages?