Week 2

Create Query Views, Reports and Interface

Use the Week 2 workbook to turn the equipment loans list into saved query views, report views and a usable interface.

Starting Workbook

Use this workbook to see what each saved view should answer before recreating the view in Microsoft Lists.

All RecordsCurrent LoansOverdue LoansYear 12 LoansDamaged or Follow UpWeekly Loan SummaryReplacement Cost ReviewDashboardInterface Test
Download workbook

Learning Goals

  • Create saved query views
  • Use filters and sorting
  • Create report-style views
  • Improve the visual interface
  • Complete a peer usability test

Lesson Sequence

1

Compare the workbook view sheets with the full data set and identify what each view is filtering.

2

Create Current Loans, Overdue Loans, Year 12 Loans and Damaged or Follow Up query views.

3

Create Weekly Loan Summary and Replacement Cost Review report views.

4

Improve view names, column order, form order and iPad usability.

5

Peer test the database and fix one interface issue.

Weekly Checkpoint

Show your teacher 4 query views, 2 report views and one interface improvement.

Detailed Lesson Build Pathway

Lesson 1

Read the workbook view examples

Understand how a saved view answers a user question.

All RecordsCurrent LoansOverdue LoansYear 12 LoansDamaged or Follow Up
  1. Download and open the Week 2 workbook.
  2. Start on the All Records sheet and count how many total loans are in the example data.
  3. Open Current Loans and identify the filter being used: Returned equals No.
  4. Open Overdue Loans and identify the two conditions: Returned equals No and DueDate is before the checking date.
  5. Open Year 12 Loans and identify the filter: YearLevel equals 12.
  6. Open Damaged or Follow Up and identify why each record needs attention.
  7. Write one sentence for each view explaining the question it answers for a staff user.

Evidence: Four view purpose statements written in student language.

Lesson 2

Build query views in Microsoft Lists

Create saved filtered and sorted views that behave like database queries.

Current LoansOverdue LoansYear 12 LoansDamaged or Follow Up
  1. Open your Task 3 Equipment Loans list in Microsoft Lists.
  2. Create a new view called Current Loans. Filter Returned to No, show LoanID, ItemName, BorrowerName, YearLevel, DueDate and Notes, then sort DueDate ascending.
  3. Create a new view called Overdue Loans. Filter Returned to No, then filter DueDate to before today or before the checking date your teacher gives you. Sort DueDate ascending.
  4. Create a new view called Year 12 Loans. Filter YearLevel to 12. Show borrower, item, due date and returned status.
  5. Create a new view called Damaged or Follow Up. Filter ConditionIn to Damaged where available. If notes-based filtering is limited, include the Notes column and manually check records with follow-up notes.
  6. For every view, compare the records shown with the workbook example and check whether the result makes sense.

Evidence: Four saved query views that each show only the records matching the view purpose.

Lesson 3

Build report views

Create saved report-style views that are clear enough to print, PDF or screenshot as evidence.

Weekly Loan SummaryReplacement Cost Review
  1. Open the Weekly Loan Summary sheet and notice that it is designed for quick staff reading, not data entry.
  2. In Microsoft Lists, create a view called Weekly Loan Summary.
  3. Show only the fields needed for a summary: LoanID, ItemName, Category, BorrowerName, YearLevel, DueDate and Returned.
  4. Group by Category or Returned, then sort by DueDate.
  5. Create a second view called Replacement Cost Review.
  6. Show ItemName, BorrowerName, ConditionIn, ReplacementCost and Notes. Sort ReplacementCost from highest to lowest.
  7. Hide long or irrelevant columns from report views so the output is readable on an iPad screen.

Evidence: Two saved report views that are readable without scrolling through every field.

Lesson 4

Improve the visual interface

Make the database easier for a user to navigate and enter data into.

DashboardInterface Test
  1. Open the Dashboard sheet and list the main user tasks: add a loan, find current loans, find overdue loans, check damaged items and review costs.
  2. In Microsoft Lists, make sure the saved views have clear names. Avoid names like View 1 or Test.
  3. Reorder columns so important fields are near the start: LoanID, ItemName, BorrowerName, YearLevel, DueDate and Returned.
  4. Open New item and check the form order. A user should not have to search for borrower, item or due date fields.
  5. On an iPad browser, open the list and check whether views can be selected easily.
  6. Hide clutter from report views. Keep data entry views detailed, but keep report views clean.

Evidence: One screenshot or note showing the interface improvement you made and why it helps the user.

Lesson 5

Peer test and fix

Use another student to test whether your database can be used without explanation.

Interface Test
  1. Give your peer three tasks: find an overdue loan, find all Year 12 loans, and add a new loan record.
  2. Do not explain where everything is unless they get stuck. Watch what they click.
  3. Record one success and one problem in the Interface Test sheet or your notes.
  4. Fix one issue, such as unclear view name, hidden column, poor column order or missing required field.
  5. Ask your peer to repeat the task after the fix.
  6. Write a short testing note: what was tested, what problem was found, what was changed, and whether the fix worked.

Evidence: A peer testing note and one visible database improvement.

Key Build Steps

Create a query view

  1. Start from All items or your full records view.
  2. Apply the filter or sort that answers one user question.
  3. Choose only the useful columns for that question.
  4. Save the view with a name that describes the answer, not the technique.
  5. Open the saved view again and check every visible record against the criteria.

Create a report view

  1. Decide who the report is for and what decision it supports.
  2. Hide fields that distract from that purpose.
  3. Use grouping or sorting to make patterns obvious.
  4. Keep the view narrow enough for screen reading or PDF evidence.
  5. Save the view and capture evidence once it is readable.

Student Checklist

  • I have four query views: Current Loans, Overdue Loans, Year 12 Loans and Damaged or Follow Up.
  • I have two report views: Weekly Loan Summary and Replacement Cost Review.
  • My views use filters, sorts, grouping or selected columns for a clear purpose.
  • A peer has tested my list and I improved one interface issue.
  • I can explain the difference between a query view and a report view.

Common Mistakes to Avoid

Creating views that are just copies of All items.

Every query or report view must use a filter, sort, grouping or selected columns for a clear purpose.

Forgetting to save the view after filtering.

Use Save view as or the view menu so the view remains available later.

Showing too many columns in reports.

Reports should answer quickly. Hide fields that do not support the report purpose.

Filtering overdue loans without checking Returned.

Overdue loans should normally be Returned equals No and DueDate before the checking date.

Quick Start Instructions

  1. Download this week's workbook and open the sheets listed at the top of the page.
  2. Open Microsoft Lists in the browser at https://office.com/launch/Lists.
  3. Build from Excel if the option is available. If not, create a blank list and follow the workbook field order manually.
  4. Complete each lesson task in order. Do not skip the evidence checks because they match the final assessment expectations.
  5. Use the Task 3 guides when you need help with fields, forms, query views, report views, interface or documentation.