MICROSOFT ACCESS Navya Thum navya thummail wvu edu

MICROSOFT ACCESS Navya Thum navya. thum@mail. wvu. edu March 04, 2013

ATTENDANCE • Go to https: //cs 101. wvu. edu/tools/recordattendance/ to record attendance. • Enter your My. ID username and password. 12/26/2021 2

DATABASES & RELATIONSHIPS Now that we know what a table is, let’s talk again about databases … • A Database is a collection of tables – If Tables are related, it is called a “Relational Database” • A Common Field between tables is what allows a relationship to exist. This works by way of “Primary” and “Foreign” keys… 12/26/2021 3

PRIMARY KEYS - REVISITED • Primary Key – Field in every table whose field values are unique for every record • Examples might be things like: – Student ID numbers – Cell Phone numbers – Anything that will not have duplicates as additional records are entered 12/26/2021 4

RELATIONSHIPS • Foreign Key – Similar field in a related table – Primary key field in relationship will have similar data type and content to foreign key field – The two tables are joined together on the primary and foreign keys and form a Common Field. 12/26/2021 5

RELATIONSHIPS • The link formed through this Common Field which is dubbed in one case as a “primary key”, and in another as a “foreign key”, allows tables to share data • Note that the “Look Ahead” database has 3 tables, but we will only look at 2 today… 12/26/2021 6

1: EMPLOYEES TABLE… Employee. ID is the Primary Key as all numbers are unique for employee records 12/26/2021 7

2: LOCATIONS TABLE… Location. ID is the primary key and each record has a unique Location. ID designator. This will tie back to the Employees table. 12/26/2021 8

RELATIONSHIPS • Databases are all about efficiency and not having to store the same information more than once if multiple records can use it • In this example, we know that multiple people work at the same office address. One address can have many related Employees (One to Many)… 12/26/2021 9

RELATIONSHIPS • Instead of typing the same address in each record for every Atlanta office employee: “ 450 Peachtree Road…” • We can assign a simple relationship to a “Locations” table and reference this information to a given location with a short common code of “L 01” between the tables… 12/26/2021 10

ONE TO MANY RELATIONSHIP (Many here) (One instance here) Typing “L 01” when entering an employee and having it equate to the entire address saves MANY characters in databases as it is reused over and over. “L 01” is 3 characters and the entire address is over 30 ! 12/26/2021 11

SUMMING UP WHAT WAS JUST MENTIONED • Each table has a primary key that has a unique field value • In one of the tables, there is a foreign key • In Employees table – Has the “Employee ID” as the primary key • Locations table – Has the “Location ID” as the primary key – That “Location ID” ties back to a “Location ID” field in the Employees table (where it is considered the Foreign key) • What we need to do now is to join them 12/26/2021 12

MAKING THE RELATIONSHIP These tables need to be tied together (related) to work this way… Ø Open the Relationships Window through: ØDatabase Tools ribbon > Relationships Ø Right click blank space and select “Show Table” Ø Double click “Employees” & “Locations” tables to be able to establish the relationship between them. 12/26/2021 13

THE RELATIONSHIPS WINDOW Ø Click the “Close” button • The Relationship window allows us to see structures representing each added table • Within the structures, we see the fields that each table has • Primary keys are shown with a key icon … 12/26/2021 14

MAKING THE RELATIONSHIPS Ø Drag “Location. ID” from the “Locations” table to “Location. ID” in the “Employees” table. Ø Notice the “One-To-Many” reference Ø Click to Enforce Referential Integrity Ø Click “Create”… 12/26/2021 15

RELATIONSHIP FORMED • Note the One to Many (1 to infinity symbol) that formed on the join line… Ø Close and save the relationship Ø Run the “Employee Information” query to see data pulled together from both tables… (Relationship Primary Key) (Relationship Foreign Key) Note, the relationship we made was already created in the query we ran and we did this only for increased understanding of how to create relationships. 12/26/2021 16

QUERY RESULTS • Information is pulled from the “Employee” and “Locations” tables through their relationship and shown united within the query results to give us a complete picture of the information ! • The Location. ID field was used behind the scenes to pull it all together. • Everything is stored efficiently and we are able to see everything we need together all at once. 12/26/2021 17

ACCESS CHAPTER 2 DOING IT FROM SCRATCH • • Creating a Database Creating Tables Creating Fields Field Properties 12/26/2021 18

PREMISE • We are going to create a school database to keep information on students and programs at WVU • Table Creation – Fields, Data types, other attributes • Establishing Relationships • Entering Records 12/26/2021 19

BUILDING THE FRAMEWORK Ø Open Access Ø Select “Blank Database” Ø For “File Name”, Call it lastname_firstname_accessexample 4. accdb Ø Save to Desktop (This is important) Ø Click “Create” Ø Set the Objects bar to “Object Type” to see “All Access Objects” * Note that nothing exists in the objects area yet 12/26/2021 20

DESIGNING DATABASES • When designing a database, try to think of all the fields of data that need to be stored • Group the fields into tables – Each table will contain a group of fields • Select a field in each table as a primary key where each field value will be unique … 12/26/2021 21

DESIGNING DATABASES • We want to break out information that may be redundant and include it in separate tables… • When tables will be related to one another, you need to include a common field in each of the tables that will be used to form the relationship – Primary key in one table – Foreign key in the other table 12/26/2021 22

PLANNING • We need to consider what types of information our database will hold • Assuming that we wish to keep records on students, we will want to have the following information: First and Last names, address, phone, major, and student number… 12/26/2021 23

DRAWING IT ALL OUT • To be versatile, we will want to make multiple fields for name and address elements to split the information up: – First. Name – Last. Name – St. Address – City – State – Zip … breaking things up helps us in searching later on 12/26/2021 24

DRAWING IT ALL OUT • We will also have fields for these items: Student. Num, Phone. Num • Major, will be used over and over – One major to Many people • This screams “One to Many relationship” and we can make a separate table for the majors with a common field of “Major. ID” • Second table fields: Major. ID and Major. Name 12/26/2021 25

ASSIGNING FIELD “DATA TYPES” • Data Types specify types of field values you can enter for the field, how they can be used, and what other properties the field will have – They essentially control what kind of information may be entered as field values for a given field • Let’s look at what kinds are available… 12/26/2021 26

ACCESS FIELD TYPES … 12/26/2021 27

ACCESS FIELD TYPES 12/26/2021 28

NAMING FIELDS • Fields and Tables need to be named • It is good to pick meaningful names – Names can be up to 64 characters – Names need to be unique – Can contain letters, numbers, and some special characters – Spaces generally not used • Can put capitol letters at the start of words to show separation. ex: Two. Words 12/26/2021 29

FIELD SIZES • Access has default field sizes that can be overwritten • Setting a Field Size tells Access how much space to allocate for fields • It should be only as large as the maximum -size piece of data needing to be held • Making it too large will waste disk space as it is consumed whether filled or not. 12/26/2021 30

FIELD CAPTIONS • The Caption Property allows us to customize the way a field name will show up in a report or form. – If we do not specify one, the regular field name will be used • Because we do not use spaces in actual field names, they may not be easy to understand in reports. The caption lets us make names appear more like regular English words if we choose 12/26/2021 31

CREATING THE FIRST TABLE Ø With “Table 1” selected in the Objects bar, Click the ruler to enter “Design View” Ø Name the table Student. Info and click OK Ø We now see the following… 12/26/2021 32

NEW TABLE IN DESIGN VIEW • Note that a primary key field is already created • We can change the name of this field and add others… 12/26/2021 33

MAKING THE FIELDS Create the following Fields: Ø Replace “ID” with Student. Num of Data Type Number Ø First. Name of type Text Ø Last. Name of type Text Ø Phone. Num of type Text … 12/26/2021 34

MAKING THE FIELDS Ø Major. ID of type Number Ø St. Address of type Text Ø City of type Text Ø State of type Text Ø Zip of type Text … 12/26/2021 35

MAKING THE FIELDS Ø Change Text Field Sizes to be “ 20” Ø Put descriptions on Student. Num & Major. ID ØStudents ID Number ØForeign Key to related table Ø Put captions in fields that have abbreviated names 12/26/2021 36

MAKING THE MAJORS TABLE • We said that we would like to create a separate table to have the majors listed as many students will have the same major and this will save a lot of space Ø Close the Student. Info table & Save the changes Ø Select the Create Ribbon Ø Choose Table 12/26/2021 37

MAJORS TABLE * The first field will be Major. ID and the data type needs to be Number to match the Major. ID field in the first table that we will relate it to Ø Go to Design View and name it Majors Ø Create the Major. ID field of type Number Ø Description: Primary Key related to Student. Info table Ø Create the Major. Name field of type Text … 12/26/2021 38

MAJORS TABLE COMPLETED Ø Verify Major. ID is the Primary Key Ø Flip to Datasheet view, saving changes, and enter the following records in this table… 12/26/2021 39

ENTERING DATA IN MAJORS TABLE Ø Close the table 12/26/2021 40

ENTER RECORDS IN STUDENTINFO TABLE Ø Open the Student. Info table in datasheet view Ø Create 4 student records and enter all data for them (make up names and such) Ø Make 2 of them Major. ID 2 Ø Make 1 of them Major. ID 1 Ø Make 1 of them Major ID 3… Ø Close The Table! 12/26/2021 41

REASONS TO RELATE • Establishing relationships between tables can: – Eliminate redundancy – Waste Less Space – Reduce data errors / Inconsistencies in duplication… 12/26/2021 42

INCLUDING A COMMON FIELD • When one table needs to be related to another table, we include a common field • The common field will be the primary key in one table and foreign key in the related table • In our example the Major. ID fields are common – Primary in Majors table and Foreign in Student. Info … 12/26/2021 43

LET’S RELATE OUR TABLES! Ø Make sure both tables are closed ØAlways close tables when working on them directly, otherwise you may get errors when things are in use Ø Database Tools ribbon Ø Select Relationships Ø Double-click each table name to add them … 12/26/2021 44

CREATING THE RELATIONSHIP Ø Drag Major. ID from Majors table to Major. ID in the Student. Info Table Ø Enforce Referential Integrity Ø Click Create Ø Close the Relationships window & save Ø Close Access 12/26/2021 45

MAKING A MULTI-TABLE QUERY • Create ribbon > Other group Query Design • Add both tables and close “Show Table” • Expand the Student. Info table to see all fields • * Notice the relationship showing 12/26/2021 46

WHAT WILL HAPPEN? ! • We want the query to show the student number, first and last names, phone number, full address and name of their major (not number) • We will not use the Major. ID field because remember that we don’t want to see the number • The relationship that we established already will use the common field to get the Major. Name for us by it using the Major. ID number! 12/26/2021 47

ADD FIELDS TO THE QUERY • Double click to add the fields in the order we want them to be displayed Student. Info Table Student. Num, First. Name, Last. Name, Phone. Num, St. Address, City, State, Zip Majors Table Major. Name 12/26/2021 48

RUN THE QUERY! • Click the ! Up to run the query • Notice that the information was pulled together out of both tables to be able to get you the words for the Major. Name and all the other data together 12/26/2021 49

KEEP THE QUERY TO USE AGAIN • Close the Query • Save the query as Student. Query • OK “Queries” is added to the Objects Bar • In Student. Info add a 5 th student • Close the Student. Info table • Rerun the Query 12/26/2021 50

IN-CLASS EXTRA CREDIT ! ! ! Ø Close Access & Go to the CS 101 Web site Ø Use Homework Submission Tool Ø Browse and upload for 2 points of credit. Submissions NOT ACCEPTED outside of class! >> Extra credit exercises are to be done in class. Those submitted out of class will be considered violating the academic dishonesty agreement. 12/26/2021 51

• For next class, – Walk through today’s slides to better understand the “Relationships” concept. • 03. 18. 2013 – My. ITLab Lesson C due • 03. 22. 2013 – Homework 4 is due – HW 4 is posted now on the assignments page. 12/26/2021 52

Questions? 53 12/26/2021