MS ACCESS 101

1. MS ACCESS 101 ACCESS vs. EXCEL - Concepts ACCESS Components

2. Access vs. Excel • 1-to-many relationship • A customer can have a basic information table, orders table, shipping address table.

3. Access vs. Excel Customer ID Billing Address Telephone # Shipping Address Orders

4. Access vs. Excel • Concurrent users • More than one user can access the customer database file at the same time. • A sales representative and a customer care specialist can look at the history their customers at the same time. DATABASE

5. Access vs. Excel • Data Size & Data Entry • Access is better at managing large amounts of data. • A well designed database reduces repetition of data. • Forms can be designed to facilitate accurate data entry.

6. Access vs. Excel • Less about numbers and calculations, more about relationships between different records (relational database). • Ideal for creating forms and reports such as invoices and statements, i.e. utility bills and bank statements.

7. ACCESS Components • We will focus on the first four components of ACCESS.

8. Tables • Think of each table like an object: Customers, Invoices, Products, etc. • Separates useful units of information into columns. • Columns should be made of the smallest useful bit of data, i.e. there should be at least two columns for a name, FIRST and LAST. A column that takes the entire name as the input is not useful—you will not be able to sort by last name.

9. Tables • Each table should also have a PRIMARY KEY (PK) column. It is a number that represents one distinct record in that table. Social Security numbers are a type of PRIMARY KEY because they are distinct numbers that represent ONE distinct person. • Relationships between tables can be defined by the use of the PK. • PK becomes the FOREIGN KEY (FK) when used in other tables--A customer’s PK (his account #) in the CUSTOMER table will be used in the ORDER table as a FK. • You can explicitly link or define the ‘relationship’ between two or more tables via PK & FK.

10. Queries • Queries are what filters are to Excel spreadsheets. It manipulates the data in the Tables. • You can pick the columns you want to see and filter by the criteria of your choice. • Combines all the data from different tables.

11. Form • Forms are more user-friendly ways to input and call out records from different tables, i.e. data entry. • You can design the forms with different logos, pictures, etc.

12. Reports • Similar to Queries, in that it can filter and display data from more than one table. • You can visually design a report with different logos, font sizes, colors, etc.