Week 1

Build the Database Foundation

Use the Week 1 workbook to plan, create and test the Microsoft List that acts as the single-table database.

Starting Workbook

Use this workbook as the planning model, sample data source and checking tool before building the list in Microsoft Lists.

Start HereData DictionaryLoan Records ExampleList Setup Checklist
Download workbook

Learning Goals

  • Map database terms to Microsoft Lists
  • Use an Excel workbook to plan the table, fields and records before building
  • Create or manually recreate a Microsoft List from structured Excel data
  • Choose suitable column types for each field
  • Create a LoanID key field and enter varied sample records

Lesson Sequence

1

Read the workbook scenario and identify the fields, data types and example records.

2

Create the Microsoft List from Excel where available, or recreate it manually from the workbook.

3

Configure LoanID, required fields and choice columns so the list behaves like a database table.

4

Enter and check realistic returned, unreturned, overdue and damaged records.

5

Complete a timed mini-build and compare it with the workbook checklist.

Weekly Checkpoint

Show your teacher your columns, LoanID field and at least 12 varied records.

Detailed Lesson Build Pathway

Lesson 1

Read the workbook and plan the table

Understand the equipment loans scenario and turn the workbook into a database plan.

Start HereData DictionaryLoan Records Example
  1. Download and open the Week 1 workbook.
  2. Read the Start Here sheet and write the database purpose in one sentence: this list tracks equipment loans, returns, overdue items and follow-up notes.
  3. Open the Data Dictionary sheet and identify the field names, data types and required fields.
  4. Open the Loan Records Example sheet and find one returned loan, one unreturned loan, one overdue loan and one damaged or follow-up record.
  5. List the database terms and Microsoft Lists equivalents: table is list, field is column, record is item, data type is column type, query is saved view.
  6. Check that every field has a reason. If you cannot explain why a field exists, ask before you build.

Evidence: A completed planning note showing the purpose, field list, Microsoft Lists equivalents and four example record types.

Lesson 2

Create the list from the Excel workbook

Build the first version of the equipment loans list using the workbook as the source.

Loan Records ExampleList Setup Checklist
  1. Open Microsoft Lists in the browser at https://office.com/launch/Lists.
  2. Choose New list. If From Excel is available, choose From Excel and upload the Week 1 workbook.
  3. Select the table or range from the Loan Records Example sheet. Preview the fields before creating the list.
  4. Name the list Task 3 Equipment Loans - Your Name.
  5. If the import screen asks for column types, choose text for LoanID, item and borrower; choice for category, year level and condition; date for borrowed, due and returned dates; yes/no for returned; currency for replacement cost; multiple lines for notes.
  6. If From Excel is not available, choose Blank list and create the columns manually using the Data Dictionary sheet.
  7. After the list opens, check whether Microsoft Lists created a default Title column. Rename it to ItemName if it is being used for the item name, or hide it if you created a separate ItemName column.

Evidence: A Microsoft List with the equipment loan records visible and the list named correctly.

Lesson 3

Set up fields, data types and LoanID

Improve the imported list so it uses reliable database-style structure.

Data DictionaryList Setup Checklist
  1. Open the list settings or column menu for each field and check the column type.
  2. Set LoanID as a single line of text column with values such as LOAN-001, LOAN-002 and LOAN-003.
  3. Make LoanID required. If the unique values setting is available, turn it on so duplicate LoanID values cannot be saved.
  4. Make ItemName, Category, BorrowerName, YearLevel, DateBorrowed, DueDate, Returned and ConditionOut required.
  5. Create consistent choice options. Category can use Camera, Audio, Laptop, Cable, Tripod and Tablet. Condition can use New, Good, Fair, Damaged and Missing.
  6. Check date columns by opening one record and confirming the date is stored as a date, not plain text.
  7. Check ReplacementCost is stored as currency or number so it can be sorted later.

Evidence: A checked list setup where LoanID, required fields and column types match the workbook data dictionary.

Lesson 4

Enter, edit and validate records

Practise using the new/edit item form and check that the data is varied enough for later views.

Loan Records ExampleList Setup Checklist
  1. Open New item and enter one new loan record using the same field order as the workbook.
  2. Enter at least 12 total records if the import did not bring them across.
  3. Make sure the data set includes returned items, unreturned items, overdue items, Year 12 borrowers, different categories and at least one damaged or follow-up item.
  4. Try to add a duplicate LoanID. If the system blocks it, record that as evidence of data integrity. If it does not block it, fix the duplicate and note that you must manually check LoanID values.
  5. Edit one record to mark it as returned and add DateReturned and ConditionIn.
  6. Sort by DueDate and check that dates behave correctly. If they sort alphabetically, the column is the wrong type and must be fixed.

Evidence: At least 12 clean records and a short note explaining how LoanID and date fields were checked.

Lesson 5

Timed mini-build and checkpoint

Check the foundation build process under assessment conditions.

List Setup Checklist
  1. Start a 25 minute timer.
  2. Create a fresh practice list called Task 3 Timed Foundation - Your Name.
  3. Build the required columns from memory first, then use the workbook checklist to catch missing details.
  4. Enter six sample records quickly, including one returned, one unreturned, one overdue and one damaged or follow-up record.
  5. Spend the last five minutes checking column types, required fields, LoanID values and date behaviour.
  6. Write down the two steps that slowed you down most so you can improve them before final submission.

Evidence: A timed checkpoint list plus a two-point reflection on what needs more attention.

Key Build Steps

Build from Excel if your account allows it

  1. Open Microsoft Lists, select New list, then From Excel.
  2. Upload the Week 1 workbook and choose the loan records table or range.
  3. Check every column name before creating the list. Do not accept vague names such as Column1.
  4. Create the list, then immediately check column types because imported Excel data often needs cleaning.
  5. Fix LoanID, dates, yes/no, choice fields and currency fields before adding more records.

Manual build fallback

  1. Create a blank list called Task 3 Equipment Loans - Your Name.
  2. Use the Data Dictionary sheet as your build order.
  3. Create LoanID first, then item, category, borrower, year level, borrowed date, due date, returned status, return date, condition fields, replacement cost and notes.
  4. Use the Loan Records Example sheet to manually enter records.
  5. Use the List Setup Checklist sheet as your final check.

Student Checklist

  • I can explain the difference between a list, column, item, column type, form, view and report.
  • My list has all required equipment loan columns.
  • LoanID values are unique and follow a consistent format.
  • My data set includes enough varied records to test views next week.
  • I have checked my build against the workbook checklist.

Common Mistakes to Avoid

Using plain text for dates.

Change DateBorrowed, DueDate and DateReturned to date columns so sorting and overdue views work.

Using borrower name as the key.

Borrowers can have more than one loan. Use LoanID because each loan record needs its own identifier.

Letting choice fields become free typing.

Use choice columns for category, year level and condition to reduce spelling variations.

Only entering perfect records.

Include messy real-world cases such as overdue, unreturned, damaged and follow-up loans so views can be tested.

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.