INTRODUCTION TO ACCESS DATABASE Navya Thum navya thummail

INTRODUCTION TO ACCESS DATABASE Navya Thum navya. thum@mail. wvu. edu February 25, 2013

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

OUTLINE • Access 2 Continues – Creating a Three-Table Database – Data Types • Auto. Number • Yes/No • Lookup Wizard • Much of what we do today will likely be useful for your Homework 4 project 12/30/2021 Access Chapter 2_2 3

THE ROCK & ROLL HALL OF FAME DATABASE EXAMPLE • Premise: We want to create a database to keep track of Bands & Members – One Band has Multiple Members • For the members, we also want to track their country of origin. – There could be one country for many members. • 3 Tables, 2 relationships… 12/30/2021 Access Chapter 2_2 4

TABLE RELATIONAL STRUCTURE ∞ Members 1 Bands ∞ 1 Countries 12/30/2021 Access Chapter 2_2 5

AUTONUMBER PRIMARY KEY EXAMPLE Ø Create a New Blank Database called Last. Name_First. Name_Access_Ex 4 Ø Switch to Design View and create a Bands table with a field called Band. Num of type Auto. Number (Primary Key) Ø Create a field called Band. Name of type Text Ø Save the table and enter these records in Datasheet view: 1, The Who 2, Van Halen … 12/30/2021 Access Chapter 2_2 6

AUTONUMBER EXAMPLE CONTINUED Ø Create another table: Create ribbon > Table Ø Call it Members and enter these fields: Member Auto. Number (Primary Key) Band. Number (will relate to “Band” Table later) * NOTE: Autonumber from Band Table is compatible with Number type in this table. DO NOT USE AUTONUMBER on both sides of the common field ! First. Name Text Last. Name Text … 12/30/2021 Access Chapter 2_2 7

SECOND TABLE FROM EXAMPLE Ø Flip to Datasheet view and Save the table Ø Enter these records: 1, 1, John, Entwhistle 2, 1, Pete, Townsend 3, 1, Roger, Daltry 4, 1, Keith, Moon … 12/30/2021 Access Chapter 2_2 8

SECOND TABLE COMPLETION Ø Enter these too… 5, 2, David Lee, Roth 6, 2, Edward, Van Halen 7, 2, Alex, Van Halen 8, 2, Michael, Anthony * Close both open tables and we will come back to this later… 12/30/2021 Access Chapter 2_2 9

USING “LOOKUP” TYPE FIELDS • • They allow for clicking a drop down arrow when entering field values while in datasheet view to select predefined items as opposed to having to type them. Two types of lookup fields: 1. Lookup references uses field values in another table 2. Lookup references items you manually enter in advance in the same table We will now do both types… 12/30/2021 Access Chapter 2_2 10

LOOKUP FROM OTHER TABLE EXAMPLE Ø Create third table for our database called Countries Ø Make these fields: ID Auto. Number(Primary Key) Country Text Ø Flip to Datasheet view and save the table … 12/30/2021 Access Chapter 2_2 11

LOOKUP FROM OTHER TABLE EXAMPLE Ø Create the following Records: 1, England 2, America Ø Close the table … 12/30/2021 Access Chapter 2_2 12

LOOKUP FROM OTHER TABLE EXAMPLE Ø Return to the “Members” table in Design View Ø Add a 5 th field called Origin of type Lookup Wizard Ø Use “I want the lookup column to look up values in a table or query” Ø Select “Table: Countries” … 12/30/2021 Access Chapter 2_2 13

LOOKUP FROM OTHER TABLE EXAMPLE Ø Add the Country field to Selected fields Ø Next… Finish Ø Save if prompted Ø Return to Datasheet View in “Members” Click in the Origin file to assign these: Ø Assign “England” to The Who members Ø Assign “America” to Van Halen members. 12/30/2021 Access Chapter 2_2 14

SAME TABLE LOOKUP EXAMPLE Ø In the Members table of our example, return to Design View Ø Create a field called Instrument of type Lookup Wizard… Ø Select I will type the values I want. Ø Enter the following: Vocals, Drums, Guitar, Bass Ø Save, Return to Datasheet view to enter them! 12/30/2021 Access Chapter 2_2 15

“YES/NO” FIELD TYPE EXAMPLE Ø Add a field called “Living” Ø Make it of Type Yes/No Ø Save the table and mark all records except Keith Moon and John Entwhistle as Living members * We will come back to this 12/30/2021 Access Chapter 2_2 16

CREATE A RELATIONSHIP EXAMPLE Ø Close the tables Ø Open Relationships Ø Note that lookup wizard created one already! Ø Right click, Show Table, add Bands. Ø Drag [Bands/Band. Num] to [Members/Band. Num] Ø Enforce referential, Create, and save. … 12/30/2021 Access Chapter 2_2 17

QUERY EXAMPLE • We want to use the relationships we created between the tables to show the names of the artists, their band names, and their countries of origin despite only having a band number in the Member table and a lookup for country. • We can use the relationship to seamlessly query between fields in the three tables and give the desired results… 12/30/2021 Access Chapter 2_2 18

QUERY EXAMPLE Ø Create query in design view Ø Create Ribbon > Query Design button Ø Add all tables (note the relationships showing) Ø From Bands table double click to add: Band. Name Ø From Members table add: First. Name, Last. Name, Instrument, Living Ø From Countries table add: Country Ø Run the Query ! Ø Close and Save Query as Rock. Trivia 12/30/2021 Access Chapter 2_2 19

WILD CARDS • Special characters that can represent one or more characters in text value. • You enter wild card characters in criteria row in query. • ? stands foe single character. • * stands for any number of characters. 20

ADDED DATE FIELD FOR BIRTHDAY Ø Open the Members table from Access_Ex 6(Data. File 2)and note a new field of type Date/Time called Born that was added Ø Close Members table 12/30/2021 21

RETURNING TO THE QUERY Ø Create Rock. Trivia Query Ø Also Add [Members] Born field in design view Ø Run the query Now let’s look at adding operators for criteria… 12/30/2021 22

OPERATORS 12/30/2021 23

CRITERIA & OPERATORS EXERCISE Ø Remove all the criteria Ø In the criteria row for born, add: >1/1/1960 Ø Note we get 3 records (born after then) Ø Try making it: >=6/5/1950 We get 12 records with VH, Def Lep, and Rush Ø Change it to: <6/5/1950 ØThe Who are our most senior band 12/30/2021 24

CALCULATIONS IN A QUERY… • Queries can be built to perform a calculation as part of the query • We often do this for things that we do not need to store and take up space with • Expressions can be entered into the query design grid as if they were regular fields • Done on data type fields treated as numbers 12/30/2021 25

CALCULATIONS IN A QUERY… • To perform a calculation in a query, you must add a calculated field to the query design. • Make certain that you are following the rules of precedence. • Two options for entering expressions: – Enter the expression directly into the field text box – Enter the expression in the Expression Builder 12/30/2021 26

CREATING A CALCULATED FIELD Ø Remove the existing criteria Ø In design view, click in the next available field, where the name would be, and then click the magic wand “Builder” icon above. Ø The expression builder shows up… 12/30/2021 27

THE EXPRESSION BUILDER… 12/30/2021 28

LET’S MAKE A QUERY TO TRY THIS… We want to figure out how many years they are/would be alive. It would be inefficient to store this and we would have to update it all the time too Ø Build or type the following: Age: (Date()-[Members]![Born])/365 Note: If you see <<expr>> be sure to remove it ! ! ! Ø Run it ! Ø Expand the width of the column if you see ##### … 12/30/2021 29

CHANGING DECIMALS PRECISION Note: Similar to what is needed for to set decimal places on query results in home works!! Ø In design view, right click Age field Ø Click Properties Ø Select Fixed for Format and 0 for Decimal Places Ø Re-run it to see it without the decimal places 12/30/2021 30

NEXT CLASS Ø Try to go over this lecture slides and step by step Ø Read over Access 2 in text 12/30/2021 Access Chapter 2_2 31

Questions? 32 12/30/2021