Rational Databases with Microsoft Access

MS Access Database for beginners

Rationale	Scheme	Information	Links	About

Scheme of Work
Lesson	Access skills and learning outcomes	Right click the link and select Save Link As...
Lesson 1	Exercise 1 Simple and complex queries Students will understand basic database vocabulary and that the function of a database is to store data and produce information. Students will be familiar with opening an Access database and working in MS Access Students will be able to create a simple query selecting specified records and fields and sorting data from existing data stored in a database.		Exercise1 Queries
Lesson 1			Exercise1 Queries
Lesson 2	Students will go on to create a range of more complex queries in order to find specific information from existing data stored in a database using a combination of search criteria including: Find records that match one criterion AND another Find records that match one criterion OR another Find records that match one criterion but NOT another Find records that match BETWEEN one criterion and another		Video1.mdb
Lesson 3	Find records that match similar to, or LIKE, a particular criterion Use parameter value queries to perform a "fuzzy" search. Students will be able to create their own queries using a combination of various criteria and appreciate that queries based on fields with different data types require criteria formed with different syntax		Video1 with queries
Homework	Database vocabulary		Homework 1
Lessons 4 and 5	Exercise 2 - Creating reports Students will appreciate that, whilst it is possible to print the contents of a query or table directly, there is little control over the format and layout of the presentation. It is better to use Access reports in order to present information in an attractive and controlled format. Students will be able to produce reports based on tables or queries and group the information generated in useful ways using a report.		Exercise2 reports
			Exercise2 reports
			Video1 with reports
Homework	Data Protection Act		Homework 2
Lessons 6, 7 and 8	Exercise 4 - Creating data storage tables and setting data validation Students will understand that all data is stored in database in tables and that each different set of data subjects will have its own table. Data is stored in records with fields each holding a category of data. Data should be atomised in the table (broken down into its smallest parts - Mr John Smith should exist in 3 fields). Students will create a new data storage table and will add use various validation techniques for each field - including setting data type, setting field length, creating drop down lists, setting default values, establishing input masks, setting validation rules and feedback messages. Students will test the database validation at each step of the implementation process and keep records of the testing completed.		Exercise3 Tables and validation
			Exercise3 Tables and validation
			Sample member data
			Video1 with new member table
Lesson 9	Designing data entry forms Students will consider that forms are a useful and convenient way of entering data into an underlying table. They will be able to identify the difference between columnar and tabular forms and know the advantages of entering data through a columnar form		Exercise4 forms
			Exercise4 forms
			DVD icon
Homework	Database design		Homework3
Lesson 10	Testing the forms Students will consider the layout of a data entry form in terms of how easy it is to use by another person. They will seek feedback from other users and amend their work accordingly		Video1 with forms
Extension activity (forms)	Introduce a picture field into the table (data type OLE object). Working in Design View, introduce this field into the Video form. Copy and paste appropriate pictures from the internet for each film
Extension activity (database design)	From this position it will be possible for students to create a relational database by introducing a third table, tblLoans. Fields will be LoanID (autonumber), CustomerID (number), VideoID (number), LoanDate (Date/Time) set default value to =Now()