The Data Files course: sixteen films from an empty file to a shop’s database

Data Files is the thirteenth app in Rutba Office, a desktop database for when a spreadsheet is no longer enough, which an earlier post introduced. It now has a course: films of three to four minutes each, watched in order, that build one shop’s database and use it.

Play the film

Part 1: what Data Files is, shown on the finished shop (3:14).

One shop throughout

The course follows Larkspur Corner Store, the same invented shop as Data Files’ shop sample. Part 1 opens the finished database to show where the course is going: products, customers and sales, the records and fields in each, the tables that point at each other, and the queries, forms, reports and print templates built on them, all in one file kept on your own computer. From part 2 the shop is built up again, and each later part starts from the file the one before left.

The parts so far

1. What Data Files is: tables, records and one file you keep.
2. Start a database: ready-made, a sample business or empty.
3. Make a table and design its fields.
4. Required, unique and default values.
5. Type records in, then sort, filter and search them.
6. Import a CSV file or a spreadsheet as new tables.
7. Relationships: link sales to customers and lines to sales.
8. Your first query: fields, sorting and criteria.
9. Totals in queries: count, sum and average by group.
10. Join tables in a query: best sellers from sales and products.
11. See and write SQL.
12. Make a form and lay it out.

Then part 14, subforms, a sale and its lines on one screen; part 15, reports grouped, totalled, printed or saved as PDF; part 16, print templates; and part 17, a mail merge in Documents from a Data Files table. Part 13 follows.

Asking the shop a question

The middle of the course is about getting answers out. Part 8 asks the products table which products need reordering, comparing the stock with each product’s own reorder level and leaving out what is no longer sold, then saves the query to run again:

Play the film

Part 8: your first query, fields, sorting and criteria (3:07).

Part 11 opens the SQL behind a query and writes one by hand. The SQL view only reads, so nothing typed there can change a record, which makes it a safe place to learn; a guide on office.rutba.io covers the same ground for any database.

Printing from the records

The last parts are where a database earns its keep in a small business. Part 16 keeps the shop’s own details in a table for the letterhead, makes an 80 mm till receipt with its lines and total, an A4 invoice with a place to sign and a letter to every loyalty customer, saves a PDF and prints a sale from its form. Part 17 writes one letter per customer in Documents, with each customer’s first name and loyalty points filled in.

Play the film

Part 16: print templates, a till receipt, an invoice and a letter (3:17).