Be able to set display format of Boolean/logical field (yes/no, true/false, checkbox)

Cambridge IGCSE ICT 0417 – Databases

Section 18 – Learning Objectives

• AO1 – Knowledge & Understanding: Recall the purpose of tables, fields, data types, primary/foreign keys, relationships, validation rules and the role of Boolean (logical) fields.
• AO2 – Application: Create and modify tables, forms, queries and reports; set field properties (including display format); import data; use calculated fields; and manipulate records.
• AO3 – Analysis & Evaluation: Evaluate design choices (e.g., display format, control layout, query criteria) and suggest improvements for different user groups.

---

1. Core Database Concepts (AO1)

• Table: A collection of related records (rows) and fields (columns).
• Field: A single attribute of a record. Each field has a data type which determines what kind of data can be stored.
• Data‑type details (Access example)
  • Short Text – up to 255 characters.
  • Long Text – large blocks of text.
  • Number – Integer, Long Integer, Decimal, Single, Double.
  • Currency – fixed‑point monetary values.
  • Date/Time – stores dates, times, or both; can be formatted (dd/mm/yyyy, mm/dd/yyyy, etc.).
  • Yes/No – Boolean/logical field (stores only two values).
  • AutoNumber – system‑generated unique numbers (often used for primary keys).
• Primary key: A field (or combination of fields) that uniquely identifies each record. Must be unique, not null, and indexed.
• Foreign key: A field that references the primary key of another table, establishing a relationship between the two tables.
• Relationship types
  • One‑to‑One
  • One‑to‑Many (most common)
  • Many‑to‑Many (implemented via a junction table).
• Validation rules & field properties
  • Required – forces a value to be entered.
  • Indexed (No Duplicates) – enforces uniqueness (often used for primary keys).
  • Default Value – automatically inserts a value when a new record is created.
  • Input Mask – controls the format of data entry (e.g., phone numbers).
• Boolean / logical field: Stores only two possible values (Yes/No, True/False, 0/1). The underlying stored values are numeric (‑1 for Yes/True, 0 for No/False) but can be displayed in several ways.

---

2. Creating Tables (AO2)

Example: a task‑tracking system with two related tables – Projects and Tasks

1. Open Microsoft Access (or another desktop DBMS).
2. File → New → Blank database. Name it TaskManager.accdb.
3. In the Navigation Pane, click Table Design.
4. Create the Projects table:

   Field name	Data type	Key / Validation	Purpose
   ProjectID	AutoNumber	Primary Key	Unique project identifier
   ProjectName	Short Text	Required	Name of the project
   StartDate	Date/Time		Project start date
   EndDate	Date/Time		Projected end date

   
   Save as Projects.
5. Create the Tasks table (includes a foreign key):

   Field name	Data type	Key / Validation	Purpose
   TaskID	AutoNumber	Primary Key	Unique task identifier
   ProjectID	Number (Long Integer)	Foreign Key → Projects.ProjectID	Links task to a project
   Description	Short Text	Required	Brief description of the task
   DueDate	Date/Time		When the task should be finished
   Completed	Yes/No	Default Value: No	Boolean field indicating status
   HoursSpent	Number (Decimal)		Time logged for the task

   
   Save as Tasks.
6. Define the relationship:
   1. Database Tools → Relationships.
   2. Add Projects and Tasks.
   3. Drag Projects.ProjectID onto Tasks.ProjectID.
   4. Enforce Referential Integrity and choose Cascade Update (optional).

---

3. Setting the Display Format of a Boolean Field (AO2)

The stored values remain –1 (Yes/True) or 0 (No/False). Only the visual representation changes.

3.1 General Procedure (Applies to Access, LibreOffice Base, FileMaker, etc.)

1. Open the database and locate the table containing the Boolean field.
2. Enter Design view (or Structure view).
3. Select the Boolean field (e.g., Completed).
4. In the property sheet locate Format (or Display Format / Display Control).
5. Choose one of the options:
   • Yes / No
   • True / False
   • Checkbox
6. Save the design and reopen the table in Datasheet (or Browse) view to see the effect.

3.2 Example – Checkbox in Microsoft Access

1. Open TaskManager.accdb.
2. Navigation Pane → right‑click Tasks → Design View.
3. Click the Completed field.
4. In the Field Properties pane, find Display Control and select Check Box.
5. Save (Ctrl + S) and close Design view.
6. Open the table in Datasheet view – a tick‑box now appears in the Completed column.

3.3 Comparison of Display Formats

Display Format	Visual Representation	Typical Use‑Case
Yes / No	Text strings “Yes” or “No”	Surveys or questionnaires where a verbal answer is required.
True / False	Text strings “True” or “False”	Programming‑oriented databases or logical condition logs.
Checkbox	Square box that can be ticked (✔) or left empty	Forms where rapid toggling of a status is needed (e.g., task completed, fee paid).

---

4. Form Design (AO2)

Forms provide a user‑friendly interface for data entry, editing and navigation.

4.1 Creating a Simple Data‑Entry Form

1. With the Tasks table selected, click Create → Form Design.
2. In the Form Design Tools ribbon, click Add Existing Fields and drag each field onto the form.
3. For Completed, the control type will automatically be a Check Box if the field’s display format is set to Checkbox.
4. Set useful control properties (Property Sheet → Format tab):
   • Default Value: No (unchecked).
   • Tab Order: Design → Tab Order – arrange logical sequence.
   • Caption: Provide clear labels (e.g., “Task description”).
   • Control Width / Height: Align controls for a tidy layout.
5. Apply layout best‑practice:
   • Group related fields (e.g., task details together, status fields together).
   • Use section headers (Form Header, Detail, Footer) to separate groups.
   • Align controls vertically and use consistent spacing.
6. Save the form as TaskEntryForm and switch to Form View to test data entry.

4.2 Adding a Calculated Control (Optional)

Display the number of days remaining until the due date:

1. Add an unbound text box to the form.
2. Set its Control Source to:
   =DateDiff("d", Date(), [DueDate])
3. Give it a caption “Days left”.

---

5. Importing External Data (AO2)

Real‑world work often requires importing CSV, TXT or Excel files.

5.1 Preparing the CSV File

Description,DueDate,Completed,ProjectID,HoursSpent
Write report,2025-01-15,Yes,1,2.5
Update website,2025-01-20,No,2,0

5.2 Import Procedure (Access)

1. External Data → New Data Source → From File → Text File.
2. Browse to tasks.csv, choose Import the source data into a new table in the current database, click OK.
3. Import Wizard – step by step:
   • First row contains field names – check the box.
   • Set data types:
     • Description – Short Text
     • DueDate – Date/Time (choose appropriate format)
     • Completed – Yes/No (Access recognises “Yes/No”, “True/False”, “1/0”)
     • ProjectID – Number (Long Integer)
     • HoursSpent – Number (Decimal)
   • Primary key: let Access add an AutoNumber field (e.g., ImportID) or choose an existing unique column.
4. Finish – the new table (e.g., ImportedTasks) appears.

5.3 Append vs. Create New Table

• Create new table – use when the source data is a completely separate set.
• Append query – use when you need to add the imported records to an existing table (Tasks). Steps:
  1. Create a new query → Append → select Tasks as the destination.
  2. Add fields from ImportedTasks and map them to the corresponding fields in Tasks.
  3. Run the query; Access will warn about duplicate primary‑key values. Resolve duplicates by:
     • Removing or renumbering the conflicting records in the source file.
     • Using ON CONFLICT REPLACE (in SQL) if the DBMS supports it.

5.4 Mapping Tips for Boolean Values

• Consistently use one of the following in the CSV: Yes/No, True/False, or 1/0.
• If the source uses 0/1, Access will automatically map 1 → Yes, 0 → No.
• When the source uses other words (e.g., “Completed”, “Pending”), use the Import Wizard → Advanced → Field Options → Yes/No to define custom mappings.

---

6. Queries – Selecting, Sorting, Criteria, Wild‑cards, Parameters & Aggregates (AO2)

6.1 Simple Select Query

1. Create → Query Design → add Tasks.
2. Double‑click the fields to display: TaskID, Description, Completed.
3. In the Criteria row under Completed type Yes (or -1).
4. Run (red !); the result shows only completed tasks.

6.2 Multiple‑Criteria Query (AND / OR / NOT)

Show pending tasks due in the next 7 days:

1. In a new query, add Tasks.
2. Fields: TaskID, Description, DueDate, Completed.
3. Criteria rows:
   • Under Completed: No
   • Under DueDate (first criteria row): <=Date()+7
4. Run – records meet **both** conditions (AND).

6.3 Using Wild‑cards (LIKE)

Find tasks whose description contains the word “report” (case‑insensitive):

Criteria under Description:  Like "*report*"

6.4 Parameter Query (Prompted Input)

Prompt the user for a project number and list its tasks:

Criteria under ProjectID:  [Enter Project ID]

When the query runs, Access displays a dialog box asking for the value.

6.5 Aggregate (Summary) Queries

Show totals for each project:

1. Create a new query → add Tasks.
2. Add ProjectID and HoursSpent.
3. Click the Totals (Σ) button.
   • ProjectID – Group By
   • HoursSpent – change Total to Sum
4. Run – you get total hours logged per project.

6.6 Other Aggregate Functions

Function	Purpose
Count	Number of records (e.g., total tasks).
Sum	Total of a numeric field (e.g., total hours).
Avg	Average value (e.g., average hours per task).
Min	Smallest value (earliest DueDate).
Max	Largest value (latest DueDate).

---

7. Calculated Fields & Functions (AO2)

Calculated fields can be created in tables, queries or forms.

7.1 Table‑Level Calculated Field (Access 2010+)

1. Open Tasks in Design view.
2. Add a new field called DaysLate.
3. Set its Data Type to Number and in the Expression Builder enter:
   =DateDiff("d", [DueDate], Date())
4. Save – the field now shows how many days a task is overdue (negative values mean it is not yet due).

7.2 Query‑Level Calculated Field

Calculate the cost of a task (HoursSpent × Rate = £). Assume a fixed rate of £30 per hour.

Field: TaskCost: [HoursSpent]*30

7.3 Common Functions

• Round(expression, n) – rounds to *n* decimal places.
• Int(expression) – returns the integer part.
• Nz(expression, value) – replaces Null with *value* (useful for calculations).
• Date() – current system date.
• Now() – current date and time.

---

8. Reports – Design, Grouping, Formatting & Boolean Display (AO2)

8.1 Creating a Report with the Report Wizard

1. Select the Tasks table → Create → Report Wizard.
2. Choose fields: TaskID, Description, DueDate, Completed, HoursSpent.
3. Group by Completed (so completed and pending tasks appear in separate sections).
4. Select a layout (Columnar) and a style, then click Finish.

8.2 Formatting the Boolean Field in a Report

1. Open the report in Design view.
2. Select the Completed control.
3. In the Property Sheet → Format tab, set Format to:
   "Completed";"Pending" (or use the built‑in “Yes/No” format and change the caption).
4. Alternatively, change the Display Control to a **Check Box** for a visual tick‑box.

8.3 Adding Summary Totals

1. With the report in Design view, click the Group & Sort pane.
2. For the Completed group, click Add a Total → Count (shows number of tasks in each group).
3. To show total hours per group, add the HoursSpent field again and set its total to Sum.

8.4 Layout Best Practices for Reports

• Use a clear report header (title, date, author).
• Align column headings and data columns.
• Apply consistent fonts and shading for readability.
• Include page numbers and a footer with the report generation date.

---

9. Tips & Common Mistakes (AO2)

• Underlying values: In Access, Yes = –1, No = 0. Changing the display format never alters these numbers.
• Consistent control types: The form control (e.g., checkbox) must match the field’s display format; otherwise users may be unable to edit the data.
• Importing Booleans: Keep the source consistent (Yes/No, True/False, 1/0). Access will map them automatically.
• Duplicate primary keys: When appending data, resolve duplicate keys before running the append query, or let Access auto‑number.
• Validation rules: Use “Required” for essential fields to prevent missing data.
• Referential integrity: Enforce it on relationships to avoid orphan records.
• Saving changes: Always click Save after modifying tables, forms, queries or reports.
• Calculated fields: Remember that they are read‑only; if you need to edit the underlying data, change the source fields.
• Report formatting: Use the Format property to replace “Yes/No” with more meaningful words (e.g., “Completed”/“Pending”).

---

10. Integrated Practice Exercise (All AO’s)

1. Create tables Projects and Tasks as described in Section 2, ensuring ProjectID in Tasks is a foreign key.
2. Set the display format of Completed to Checkbox (Section 3).
3. Design a data‑entry form called TaskEntryForm that includes a checkbox linked to Completed and follows the layout best‑practice guidelines (Section 4).
4. Import the supplied tasks.csv file, mapping fields correctly and using an Append Query to add the records to Tasks (Section 5).
5. Run a query that lists pending tasks due within the next 14 days, sorted by DueDate, using multiple criteria and the Date() function (Section 6).
6. Create a calculated field in a query that shows TaskCost = HoursSpent × 30 and display it in the result set.
7. Produce a report named TaskStatusReport that:
   • Groups tasks by the Completed status.
   • Shows the count of tasks and the sum of HoursSpent for each group.
   • Formats the Boolean field to display “Completed” / “Pending”.
8. Answer the evaluation question in Section 11.

---

11. Evaluation (AO3)

Reflect on the choice of display format for the Completed field.

1. Which format (Yes/No, True/False, Checkbox) would be most appropriate for:
   • Primary‑school pupils entering data on a classroom project?
   • Company accountants reviewing a financial audit checklist?
2. Justify your choice by considering:
   • Ease of use (visual clarity, number of clicks).
   • Potential for data‑entry errors.
   • Readability of printed reports.

---

12. Summary

Setting the display format of a Boolean/logical field is a small but vital part of database design. Mastery of tables, primary/foreign keys, relationships, validation, forms, imports, queries (including multiple criteria, wild‑cards, parameters, aggregates, and calculated fields), and reports equips students to meet the full range of Cambridge IGCSE ICT (0417) requirements. The choice of display format—Yes/No, True/False, or Checkbox—should always be driven by the needs of the intended users, evaluated against criteria such as readability, speed of entry, and error‑prevention.