Lecture Notes: Introduction to Microsoft Access

Introduction

• Presenter: Kevin
• Topic: Using Microsoft Access
• Motivation: Requested by viewers, personal connection (wife worked on Access team)

What is Microsoft Access?

• A relational database program.
• Uses: Track customers, orders, assets, etc.

Why Use Access Over Excel?

• Inefficiencies in Excel:

  • Excel is a flat file system with no relationships between data.
  • Repetition of customer data for each entry.
  • Limit of just over 1,000,000 rows of data in Excel.
  • Difficult to extract insights and write queries in Excel.

• Advantages of Access:

  • Efficient data management with relational databases.
  • Allows for the creation of tables for separate data.
  • More powerful for querying and reporting.

Limitations of Microsoft Access

• Suitable for individuals or small businesses.
• Larger companies may require more scalable options (e.g., Oracle, MySQL, Microsoft SQL Server, MongoDB).
• Good for learning the fundamentals of database design.

Tutorial Overview

• Creating a database for the "Kevin Cookie Company."
• Steps:
  • Create tables.
  • Create an order entry form.
  • Write a query for data insights.
  • Create a report.

Getting Started with Microsoft Access

• Launch the application.
• Start with a blank database or use templates.
• Pin frequently used databases for easy access.

Creating Tables

• Table1: Customers

  • Fields: Customer ID, First Name, Last Name, Email Address, Phone, Notes.
  • Data types: Short text for most fields, long text for notes.
  • Editing fields: Modify names, data types, insert/delete fields.

• Table2: Orders

  • Fields: Order ID, Customer ID, Order Date, Cookies Ordered, Revenue, Order Filled.
  • Data types: Number, date/time, currency, yes/no for order filled.
  • Calculated fields: Revenue per cookie.

Entering Data

• Enter customer and order details.
• Use primary keys to ensure unique identification.
• Primary keys relate tables.

Establishing Relationships

• Use the relationships tool to connect tables.
• Connect Customer ID from customers and orders table.

Importing Data

• Import data from external sources via the "External Data" option.

Creating Forms

• Use forms for data entry and to review records.
• Customize with design options: themes, controls, logos.
• Save and access forms easily.

Running Queries

• Use "Query Design" for running queries.
• Example: Show all unfilled orders.
• Design view allows adding criteria and selecting fields.
• View SQL code for advanced query writing.

Creating Reports

• Utilize "Reports" for data summaries.
• Generate reports from queries or tables.
• Customize report look and format.

Saving Your Work

• Save database in Access format via "Save As" option.

Conclusion

• Microsoft Access basics covered: database creation, queries, forms, reports.
• Encouragement to explore more advanced features.

Feedback: If this video was helpful, give a thumbs up and subscribe for more content. Suggestions for future topics are welcomed.