R04.03.02.01 · TASK CLASS

Desktop database apps

Build tables, queries, forms and reports in Access, Base or FileMaker.

20 project-authored proposals · taxonomy v1.1 · 0 new benchmark runs. Verifiers and partial-credit rules below are designs, not executed results.

Typical applications: Microsoft Access · LibreOffice Base · FileMaker
Class GUI-need label: G2 · GUI-essential

Task proposals · 20

Expand to inspect the complete original wording and proposed checks. Original proposal text remains English in both interfaces.

R04.03.02.01-T01 Create a validated Members table in Access and import a CSV

Original instruction

In fixtures/club.accdb create table 'Members' per input/members_schema.md (MemberID AutoNumber PK, Email required and indexed unique, JoinDate Date/Time with validation rule ≤ Date(), Tier lookup list Gold/Silver/Bronze), then import fixtures/members.csv into it. Rows that violate rules go to the import errors table only.

Platforms: win · Windows
Applications: Microsoft Access
Interfaces: desk · Desktop GUI
GUI need · task setting: G2 · GUI-essential
GUI need · ordinary access: CL · CLI-sufficient
Interaction primitives: click · Point & click type · Text & forms
Work product: WP08 · App/system state
Conditions: None declared
Proposed difficulty: 2 / 5

Proposed verifier

Grader reads schema via DAO: field types, PK, unique index on Email, Required, ValidationRule, lookup RowSource; row count equals 487 valid rows and values equal key. VBA disabled by Trust Center policy; no PowerShell/ACE scripting in the agent VM.

Proposed partial credit

40% schema matches spec; 40% valid rows imported; 20% invalid rows excluded.

R04.03.02.01-T02 Write a parameter query joining two tables in LibreOffice Base

Original instruction

Open fixtures/library.odb in LibreOffice Base. Create saved query 'qLoansByBranch' joining Loans and Books, returning Title, Borrower, DueDate for loans of one branch entered as a parameter (:Branch), only overdue as of today, sorted by DueDate. Save the database.

Platforms: mac · macOS win · Windows lin · Linux
Applications: LibreOffice Base
Interfaces: desk · Desktop GUI
GUI need · task setting: G2 · GUI-essential
GUI need · ordinary access: CL · CLI-sufficient
Interaction primitives: click · Point & click type · Text & forms drag · Drag & drop
Work product: WP08 · App/system state
Conditions: None declared
Proposed difficulty: 2 / 5

Proposed verifier

Grader opens the .odb with its own UNO script: query 'qLoansByBranch' exists, contains a :Branch parameter; executing it with 3 branch values (and a fixed 'today') returns rows equal key in order. Agent session has macros disabled and no UNO/Python access or soffice CLI.

Proposed partial credit

25% query saved with right name; 25% join and columns; 25% parameter and overdue filter; 25% ordering/results.

R04.03.02.01-T03 Enforce referential integrity after cleaning orphan order lines

Original instruction

fixtures/orders.accdb has Customers, Orders and OrderLines with no relationships and 26 orphan OrderLines. Move orphans to a new table 'Orphans_Archive', then create relationships Customers→Orders and Orders→OrderLines with referential integrity and cascade update (no cascade delete). Save.

Platforms: win · Windows
Applications: Microsoft Access
Interfaces: desk · Desktop GUI
GUI need · task setting: G2 · GUI-essential
GUI need · ordinary access: CL · CLI-sufficient
Interaction primitives: drag · Drag & drop click · Point & click
Work product: WP08 · App/system state
Conditions: None declared
Proposed difficulty: 3 / 5

Proposed verifier

DAO Relations: 2 relations with correct fields, attributes enforce + cascade update, no cascade delete; OrderLines has no orphans; Orphans_Archive holds exactly the 26 seeded rows. VBA is disabled by Trust Center policy and the agent VM has no PowerShell/ACE/DAO scripting, so relations must be made in the Relationships window.

Proposed partial credit

30% orphans archived exactly; 50% two relationships with correct flags (half each); 20% no cascade delete.

R04.03.02.01-T04 Build an order-entry form with a line-item subform and lookups

Original instruction

In fixtures/orders.accdb build form 'frmOrders' (single form on Orders) with a combo box for CustomerID showing customer name, a datasheet subform of OrderLines linked on OrderID, a calculated order total in the footer, and tab order Customer → Date → lines. Save.

Platforms: win · Windows
Applications: Microsoft Access
Interfaces: desk · Desktop GUI
GUI need · task setting: G2 · GUI-essential
GUI need · ordinary access: CL · CLI-sufficient
Interaction primitives: drag · Drag & drop click · Point & click geom · Precise geometry
Work product: WP08 · App/system state
Conditions: None declared
Proposed difficulty: 3 / 5

Proposed verifier

Grader exports the form via SaveAsText: RecordSource Orders; combo with bound column CustomerID and visible name column; subform LinkMaster/ChildFields OrderID; footer control with Sum over lines; TabIndex order. Behavioral probe: grader opens the form via COM on 3 orders and reads the total. VBA is disabled by Trust Center policy and the agent VM has no PowerShell/ACE/DAO scripting.

Proposed partial credit

20% form bound to Orders; 25% combo lookup; 30% linked subform; 25% footer total and tab order.

R04.03.02.01-T05 Design a grouped sales report with subtotals and export PDF

Original instruction

In fixtures/sales.accdb create report 'rptRegionSales' grouped by Region then Rep, with Sum of Amount per rep, per region and grand total, region header repeating on new pages, page footer 'Page N of M', landscape. Export output/region_sales.pdf.

Platforms: win · Windows
Applications: Microsoft Access
Interfaces: desk · Desktop GUI
GUI need · task setting: G2 · GUI-essential
GUI need · ordinary access: CL · CLI-sufficient
Interaction primitives: click · Point & click drag · Drag & drop
Work product: WP03 · Visual layout
Conditions: None declared
Proposed difficulty: 3 / 5

Proposed verifier

SaveAsText: GroupLevel Region and Rep, RepeatSection on region header, Sum controls, page footer expression; PDF text extraction: subtotal and grand total values equal key; orientation landscape. VBA disabled by Trust Center policy; no PowerShell/ACE/DAO scripting in the agent VM.

Proposed partial credit

30% grouping levels; 30% subtotals/grand total correct in PDF; 20% repeat header + page footer; 20% landscape and export.

R04.03.02.01-T06 Add a sorted sub-summary list layout in FileMaker

Original instruction

In fixtures/inventory.fmp12 create layout 'Stock by Category' (List view) on table Items with a sub-summary part 'when sorted by Category' showing category name and summary field 'Total Value' (sum of Qty×Cost), body rows with SKU, Name, Qty, and a trailing grand summary. Save a sorted Find of all records on it.

Platforms: mac · macOS win · Windows
Applications: FileMaker
Interfaces: desk · Desktop GUI
GUI need · task setting: G3 · GUI-only
GUI need · ordinary access: G2 · GUI-essential
Interaction primitives: drag · Drag & drop click · Point & click geom · Precise geometry
Work product: WP08 · App/system state
Conditions: licensed · Licensed pro app
Proposed difficulty: 3 / 5

Proposed verifier

Grader opens the file with FileMaker Pro and runs 'Save a Copy as XML' via its own script: layout exists with parts body, sub-summary (break field Category), trailing grand summary; summary field definition correct; grader PDF of the layout sorted by Category shows subtotals equal key. FileMaker has no API for layout authoring; the agent has no Data API access.

Proposed partial credit

20% layout on Items; 30% sub-summary part with right break field; 25% summary field correct; 25% PDF subtotals.

R04.03.02.01-T07 Script a FileMaker button that finds and sorts overdue invoices

Original instruction

In fixtures/billing.fmp12 write script 'Show Overdue' that enters Browse mode, finds invoices with Status ≠ 'Paid' and DueDate < today, sorts by DueDate ascending without dialog, and shows a message with the found count. Put a button 'Overdue' on layout 'Invoices' that runs it.

Platforms: mac · macOS win · Windows
Applications: FileMaker
Interfaces: desk · Desktop GUI
GUI need · task setting: G3 · GUI-only
GUI need · ordinary access: G2 · GUI-essential
Interaction primitives: click · Point & click drag · Drag & drop type · Text & forms
Work product: WP08 · App/system state
Conditions: licensed · Licensed pro app
Proposed difficulty: 3 / 5

Proposed verifier

XML copy (grader-run): script steps match expected semantics (Enter Find Mode/Set Field/Perform Find or Perform Find[restore], Sort Records[no dialog]); button object on 'Invoices' bound to the script; grader runs the script with a fixed date and checks found set and sort order. FileMaker offers no API for layout or script authoring; the agent has no Data API access.

Proposed partial credit

40% script finds the right set; 20% sort without dialog; 15% count message; 25% button wired on layout.

R04.03.02.01-T08 Build crosstab, parameter and make-table queries in Access

Original instruction

In fixtures/hr.accdb create: 'qxtHeadcount' crosstab (Department rows, JobGrade columns, count of EmployeeID); 'qpHiredBetween' parameter query with [Start date] and [End date]; 'qmtLeavers2025' make-table query writing 2025 leavers to table 'Leavers2025'. Run the make-table query once.

Platforms: win · Windows
Applications: Microsoft Access
Interfaces: desk · Desktop GUI
GUI need · task setting: G2 · GUI-essential
GUI need · ordinary access: CL · CLI-sufficient
Interaction primitives: click · Point & click drag · Drag & drop type · Text & forms
Work product: WP08 · App/system state
Conditions: None declared
Proposed difficulty: 3 / 5

Proposed verifier

DAO QueryDefs: types crosstab/select-with-parameters/make-table; executing each with fixed parameters yields key results; table Leavers2025 exists with exactly the 2025 leavers. VBA disabled; no external scripting in VM.

Proposed partial credit

35% crosstab correct; 30% parameter query; 35% make-table query and resulting table.

R04.03.02.01-T09 Create a Base form with a subform and a list box lookup

Original instruction

In fixtures/clinic_visits.odb create form 'Patients' showing Patients fields with a subform grid of Visits (linked on PatientID) and a list box for Visits.DoctorID that displays doctor names. Order subform rows by VisitDate descending. Save.

Platforms: mac · macOS win · Windows lin · Linux
Applications: LibreOffice Base
Interfaces: desk · Desktop GUI
GUI need · task setting: G2 · GUI-essential
GUI need · ordinary access: CL · CLI-sufficient
Interaction primitives: drag · Drag & drop click · Point & click
Work product: WP08 · App/system state
Conditions: None declared
Proposed difficulty: 3 / 5

Proposed verifier

Form document in .odb: form bound to Patients, subform with master/detail PatientID and order 'VisitDate DESC', list box with bound DoctorID and display Name via SQL list source; grader opens via UNO and checks displayed rows for 3 patients. Macros, UNO and Python are disabled for the agent session.

Proposed partial credit

25% main form bound; 35% linked subform with order; 30% list box lookup; 10% opens without errors.

R04.03.02.01-T10 Merge duplicate customers and re-home their orders

Original instruction

fixtures/crm_lite.accdb has 41 duplicate customer pairs listed in table 'DupPairs' (KeepID, DropID). Using update and delete queries, re-point every Orders and Contacts row from DropID to KeepID, then delete the dropped customers. Work on the provided copy only.

Platforms: win · Windows
Applications: Microsoft Access
Interfaces: desk · Desktop GUI
GUI need · task setting: G2 · GUI-essential
GUI need · ordinary access: CL · CLI-sufficient
Interaction primitives: click · Point & click type · Text & forms
Work product: WP08 · App/system state
Conditions: None declared
Proposed difficulty: 4 / 5

Proposed verifier

No Orders/Contacts reference any DropID; all 41 DropIDs deleted; order count per KeepID equals key; total Orders count unchanged; saved queries 'quRehome*' and 'qdDropDupes' exist. VBA is disabled by Trust Center policy and the agent VM has no PowerShell/ACE/DAO scripting; grader inspects objects via SaveAsText.

Proposed partial credit

40% orders and contacts re-pointed; 30% dropped customers deleted; 20% totals preserved; 10% saved queries.

R04.03.02.01-T11 Produce filtered mailing labels sorted by ZIP from contacts

Original instruction

In fixtures/donors.accdb use the Label Wizard to create report 'rptLabelsCA' on Avery 5160 for donors in State = 'CA' with Opted-In = Yes, sorted by ZIP then LastName, with name, street, city/state/ZIP lines. Export output/labels_ca.pdf.

Platforms: win · Windows
Applications: Microsoft Access
Interfaces: desk · Desktop GUI
GUI need · task setting: G2 · GUI-essential
GUI need · ordinary access: CL · CLI-sufficient
Interaction primitives: click · Point & click type · Text & forms
Work product: WP03 · Visual layout
Conditions: None declared
Proposed difficulty: 3 / 5

Proposed verifier

Report record source filters CA + opted-in; PDF has 3×10 label grid geometry matching 5160 within 1 mm; label text order equals key (ZIP, LastName); count equals 214. VBA is disabled by Trust Center policy and the agent VM has no PowerShell/ACE/DAO scripting; grader inspects objects via SaveAsText.

Proposed partial credit

30% correct filter; 30% sort order; 25% label geometry; 15% PDF exported.

R04.03.02.01-T12 Add a related line-item portal with totals in FileMaker

Original instruction

In fixtures/quotes.fmp12, add table occurrences and relationships in the Relationships Graph so layout 'Quote Detail' shows a portal of QuoteLines (ProductName via a second relationship to Products, Qty, LineTotal), sorted by line number, with a calculation field 'QuoteTotal' summing LineTotal.

Platforms: mac · macOS win · Windows
Applications: FileMaker
Interfaces: desk · Desktop GUI
GUI need · task setting: G3 · GUI-only
GUI need · ordinary access: G2 · GUI-essential
Interaction primitives: drag · Drag & drop click · Point & click geom · Precise geometry
Work product: WP08 · App/system state
Conditions: licensed · Licensed pro app
Proposed difficulty: 4 / 5

Proposed verifier

Grader XML copy: relationships Quotes→QuoteLines (QuoteID) and QuoteLines→Products (ProductID); portal on 'Quote Detail' bound to the QuoteLines TO with sort by LineNo; QuoteTotal = Sum(QuoteLines::LineTotal); grader browses 3 quotes and reads totals. FileMaker offers no API for layout or script authoring; the agent has no Data API access.

Proposed partial credit

30% relationships; 30% portal with related product name; 15% sort; 25% QuoteTotal correct.

R04.03.02.01-T13 Repair broken linked tables and a query that joins them

Original instruction

fixtures/reporting_fe.accdb links to an Excel workbook and two tables in a back-end .accdb at old paths. Relink all links to fixtures/data/ with the Linked Table Manager, fix query 'qMonthlyMargin' that broke when an Excel column was renamed, and confirm it returns the key results.

Platforms: win · Windows
Applications: Microsoft Access · Excel
Interfaces: desk · Desktop GUI
GUI need · task setting: G2 · GUI-essential
GUI need · ordinary access: CL · CLI-sufficient
Interaction primitives: click · Point & click type · Text & forms
Work product: WP08 · App/system state
Conditions: hidden · Hidden/stale state multiapp · Multi-app
Proposed difficulty: 4 / 5

Proposed verifier

TableDefs Connect strings point to fixtures/data/ paths; all links open; qMonthlyMargin SQL references the renamed column and returns rows equal key; no local copies of linked tables created. VBA is disabled by Trust Center policy and the agent VM has no PowerShell/ACE/DAO scripting; grader inspects objects via SaveAsText.

Proposed partial credit

45% three links repaired (equal thirds); 35% query fixed and correct; 20% no local table copies.

R04.03.02.01-T14 Build a small inventory app with forms, navigation and reports

Original instruction

From input/inventory_spec.md build output/inventory.accdb in Access: 4 tables with keys and relationships, a navigation form as startup with tabs for Items, Suppliers, Stock Moves, a stock-move entry form with validation (qty ≠ 0, item required) and 2 reports (reorder list, stock valuation). Load fixtures/seed_data.xlsx.

Platforms: win · Windows
Applications: Microsoft Access
Interfaces: desk · Desktop GUI
GUI need · task setting: G2 · GUI-essential
GUI need · ordinary access: CL · CLI-sufficient
Interaction primitives: drag · Drag & drop click · Point & click type · Text & forms
Work product: WP08 · App/system state
Conditions: long · Long-horizon
Proposed difficulty: 5 / 5

Proposed verifier

Spec checklist via DAO + SaveAsText: tables/keys/relations, startup form is a navigation form with 3 tabs, entry form validation rules fire on probes (grader COM input), report PDFs contain key totals; seed rows loaded. VBA is disabled by Trust Center policy and the agent VM has no PowerShell/ACE/DAO scripting; grader inspects objects via SaveAsText.

Proposed partial credit

25% schema + relations; 20% navigation startup; 25% entry form + validation; 30% reports correct and seed data loaded.

R04.03.02.01-T15 Migrate a spreadsheet tracker into a normalized FileMaker solution

Original instruction

Turn fixtures/equipment_tracker.xlsx (one flat sheet) into output/equipment.fmp12: tables Equipment, Locations, Checkouts with relationships, value lists for Status and Location, an entry layout and a list layout, and a script 'Monthly PDF' that saves output/checkouts_month.pdf for the current month.

Platforms: mac · macOS win · Windows
Applications: FileMaker · Excel
Interfaces: desk · Desktop GUI
GUI need · task setting: G3 · GUI-only
GUI need · ordinary access: G2 · GUI-essential
Interaction primitives: drag · Drag & drop click · Point & click type · Text & forms
Work product: WP08 · App/system state
Conditions: long · Long-horizon multiapp · Multi-app licensed · Licensed pro app
Proposed difficulty: 5 / 5

Proposed verifier

Grader XML copy and record export: 3 tables with fields per spec; no duplicated location text in Equipment (normalized FK); relationships; 2 value lists; 2 layouts; script runs and writes a PDF whose rows equal key for the fixed month. FileMaker offers no API for layout or script authoring; the agent has no Data API access.

Proposed partial credit

30% normalized schema and data; 30% relationships and value lists; 20% layouts; 20% working PDF script.

R04.03.02.01-T16 Design a grouped Base report with totals in Report Builder

Original instruction

In fixtures/fundraiser.odb create report 'Pledges by Campaign' in Report Builder: grouped by Campaign with header and footer, detail rows Donor, Date, Amount, a group sum and a report grand total, Amount formatted currency. Run it and export output/pledges.pdf.

Platforms: mac · macOS win · Windows lin · Linux
Applications: LibreOffice Base
Interfaces: desk · Desktop GUI
GUI need · task setting: G2 · GUI-essential
GUI need · ordinary access: CL · CLI-sufficient
Interaction primitives: drag · Drag & drop click · Point & click geom · Precise geometry
Work product: WP03 · Visual layout
Conditions: None declared
Proposed difficulty: 3 / 5

Proposed verifier

Report definition in .odb has group on Campaign with header/footer and sum functions; PDF text: group totals and grand total equal key; currency formatting present; Report Builder (not a Writer mail-merge) required. Macros, UNO and Python are disabled for the agent session.

Proposed partial credit

30% grouping; 35% group and grand totals correct; 15% currency format; 20% PDF exported.

R04.03.02.01-T17 Fix a broken Access form after table field renames

Original instruction

In fixtures/assets.accdb, form 'frmAssetEdit' shows #Name? in 5 controls after fields were renamed, its Department combo is empty because RowSource points to a dropped query, and an embedded Save button macro opens a missing report. Repair all without recreating the form.

Platforms: win · Windows
Applications: Microsoft Access
Interfaces: desk · Desktop GUI
GUI need · task setting: G2 · GUI-essential
GUI need · ordinary access: CL · CLI-sufficient
Interaction primitives: click · Point & click hover · Hover & context type · Text & forms
Work product: WP08 · App/system state
Conditions: hidden · Hidden/stale state
Proposed difficulty: 4 / 5

Proposed verifier

SaveAsText: same form object (GUID unchanged); 5 ControlSources fixed to new field names; combo RowSource valid SQL returning departments; embedded macro references existing report; grader opens form via COM on 3 records: no #Name?, combo populated, button runs. VBA is disabled by Trust Center policy and the agent VM has no PowerShell/ACE/DAO scripting; grader inspects objects via SaveAsText.

Proposed partial credit

40% control sources (pro-rata over 5); 25% combo; 20% macro fixed; 15% form not recreated.

R04.03.02.01-T18 Control pair of T08: build the same queries with SQL from a script

Original instruction

Control pair of T08. Using PowerShell with ACE OLEDB/DAO (or Python pyodbc) from a terminal, create the same three saved queries in a copy of fixtures/hr.accdb (TRANSFORM…PIVOT crosstab, PARAMETERS query, SELECT…INTO make-table) and run the make-table query.

Platforms: win · Windows
Applications: Microsoft Access · PowerShell
Interfaces: term · Terminal
GUI need · task setting: CL · CLI-sufficient
GUI need · ordinary access: CL · CLI-sufficient
Interaction primitives: code · Code & commands
Work product: WP08 · App/system state
Conditions: None declared
Proposed difficulty: 2 / 5

Proposed verifier

Same checker as T08 (QueryDef types and results); used to compare scripted success against the GUI path.

Proposed partial credit

35% crosstab; 30% parameter query; 35% make-table query and table.

R04.03.02.01-T19 Split an Access database and lock down the front end

Original instruction

Split fixtures/timesheets.accdb with the Database Splitter so tables live in output/timesheets_be.accdb, relink the front end to that path, set startup form 'frmMain', hide the navigation pane, disable special keys, and save a compiled output/timesheets_fe.accde.

Platforms: win · Windows
Applications: Microsoft Access
Interfaces: desk · Desktop GUI
GUI need · task setting: G2 · GUI-essential
GUI need · ordinary access: CL · CLI-sufficient
Interaction primitives: click · Point & click type · Text & forms
Work product: WP08 · App/system state
Conditions: None declared
Proposed difficulty: 4 / 5

Proposed verifier

BE holds all tables; FE has only linked tables pointing to output/timesheets_be.accdb; FE properties StartupForm, StartupShowDBWindow=False, AllowSpecialKeys=False; .accde exists and opens to frmMain via COM. VBA is disabled by Trust Center policy and the agent VM has no PowerShell/ACE/DAO scripting; grader inspects objects via SaveAsText.

Proposed partial credit

30% split correct; 20% relinked path; 30% startup/lockdown properties; 20% .accde compiled.

R04.03.02.01-T20 Set up region-restricted privilege sets and test accounts in FileMaker

Original instruction

In fixtures/field_ops.fmp12 create privilege sets 'Regional Editor' (records in Visits editable and viewable only when Visits::Region = the account's region via Get(AccountName) lookup), 'Viewer' (view-only, no export), and test accounts north_ed, south_ed, viewer1 from input/accounts.csv. Verify as each.

Platforms: mac · macOS win · Windows
Applications: FileMaker
Interfaces: desk · Desktop GUI
GUI need · task setting: G3 · GUI-only
GUI need · ordinary access: G2 · GUI-essential
Interaction primitives: click · Point & click type · Text & forms
Work product: WP08 · App/system state
Conditions: risk · Irreversible/risky licensed · Licensed pro app
Proposed difficulty: 5 / 5

Proposed verifier

Grader logs in as each test account via its own FileMaker automation and counts visible/editable Visits (equal key), checks export is blocked for viewer1; XML copy shows record-level calc and privilege flags. Full Access account must remain unchanged (lockout risk). FileMaker Admin/Data API access and AppleScript/ActiveX automation are withheld from the agent.

Proposed partial credit

35% Regional Editor record access correct; 20% Viewer set; 25% three accounts correct; 20% admin access intact.

Mapped research references

No benchmark reference is assigned to this class in the current mapping.