Microsoft Access from Beginning to End
Microsoft Access Complete Learning Resource | Devine Vision Tech Devine Vision Tech Learning Centre Microsoft Access from Beginning to End A complete, simple-English online book for planning, building, using, securing and maintaining relational databases with Microsoft Access. 39 complete lessonsBeginner to advancedFull college database projectWorks offline Searchable, practical and mobile-friendly Learn database concepts, tables, relationships, queries, forms, reports, macros, SQL, VBA, security, maintenance and real projects. 4Learning tabs 39Full lessons 1Complete project × Expand allCollapse allPrint / Save PDF FoundationsDatabase ideas, tables and relationships Build & WorkQueries, forms, reports and SQL Advanced & ManageVBA, security, multi-user and repair Practice & ReviewFull project, exercises and glossary Microsoft Access and Database BasicsStarting Access and Understanding the WindowPlanning a Database Before Building ItCore Relational Database ConceptsCreating, Opening, Saving and Backing Up DatabasesFields, Data Types and Field PropertiesCreating and Managing TablesPrimary Keys, Foreign Keys and IndexesCreating Relationships and Referential IntegrityNormalization in Very Simple English Microsoft Access and Database Basics Meaning of data, information, database, DBMS, Microsoft Access and common uses Hide lesson What is data? Data means raw facts. Examples are a student name, a phone number, a product price, a date of birth or an examination mark. What is information? Information is data that has been arranged and explained so that it has meaning. A list of marks is data. A report showing the highest mark, average mark and pass rate is information. What is a database? A database is an organised collection of related data. It allows people to store, find, update, calculate, summarise and report information. What is a DBMS? A Database Management System, or DBMS, is software used to create and manage databases. Examples include Microsoft Access, Microsoft SQL Server, MySQL, PostgreSQL, Oracle Database and SQLite. What is Microsoft Access? Microsoft Access is a relational desktop database program. It is part of some Microsoft 365 and Microsoft Office editions. It can store data in tables and use queries, forms, reports, macros and Visual Basic for Applications to work with that data. 1 Table Stores data in rows and columns. 2 Query Finds, filters, calculates, joins or changes data. 3 Form Provides a friendly screen for entering and viewing data. 4 Report Presents data for reading, printing or sharing. 5 Macro Automates common actions without writing much code. 6 Module Stores VBA procedures for advanced automation. Common uses of Access Student registration and examination records. Patient, appointment and clinic records for small organisations. Stock, sales, suppliers and purchase records. Employee, attendance and payroll-support records. Library books and borrowing records. Projects, assets, vehicles, members and customers. Important: Microsoft Access is useful for small and medium database solutions. Large public systems normally need a server database and a web or desktop application. Starting Access and Understanding the Window Backstage view, Ribbon, Navigation Pane, object tabs, status bar and database views Open lesson Start Microsoft Access Open the Windows Start menu or Search. Type Access. Select Microsoft Access. Choose Blank database, a template or an existing database. When creating a blank database, type a clear file name and choose a safe folder. Select Create. Main parts of the Access window Part Simple meaning Title Bar Shows the name of the current database and Access. Quick Access Toolbar Contains common commands such as Save, Undo and Redo. File tab Opens Backstage view for New, Open, Save As, Print, Options and account settings. Ribbon Contains tabs, groups and commands. Navigation Pane Lists tables, queries, forms, reports, macros and modules. Object tab Shows each open table, query, form, report or code object. Work area The main place where the selected object is opened and edited. Status Bar Shows view buttons, record information, filters and other status details. Search box in Navigation Pane Finds database objects by name. Important Ribbon tabs Tab Main use File Create, open, save, print, compact, repair and change options. Home View, clipboard, sort, filter, records, find and text formatting. Create Create tables, queries, forms, reports, macros and modules. External Data Import, link and export data. Database Tools Relationships, dependencies, analysis, macros, VBA, compact and repair. Design or Format Appears when an object is open in a design or layout view. Views: Datasheet View shows data. Design View changes the structure. Layout View changes form or report layout while showing data. Form View and Report View show the finished object. Planning a Database Before Building It Purpose, users, entities, fields, rules, outputs and a simple planning method Open lesson Why planning is important A database can become confusing when tables are created before the real work is understood. Good planning reduces repeated data, wrong relationships and difficult reports. Questions to ask What problem will the database solve? Who will enter, check and use the data? What facts must be stored? What reports, forms and searches are needed? Which fields must be unique? Which rules must prevent wrong data? Will several people use the database at the same time? How will backups and privacy be managed? Simple planning example: student registration Need Database decision Store each student once Create a Students table. Store programmes once Create a Programmes table. Store subjects once Create a Subjects table. A student can take many subjects Create an Enrolments table between Students and Subjects. Print a student list Create a query and report. Enter student details easily Create a Student form. Prevent duplicate student numbers Use a unique indexed field. Basic design steps Write the purpose in one sentence. List the main things you need to store. These are possible entities. List the facts about each entity. These are possible fields. Choose a primary key for every table. Decide how tables are related. Add validation rules and required fields. Draw the relationship diagram. Create a small test database before entering real data. Test all forms, queries and reports. Document the finished design. Core Relational Database Concepts Entity, table, field, record, value, key, relationship, index and integrity Open lesson Concept Meaning Example Entity A real thing or subject about which data is stored. Student, employee, product or course. Table