access (chapter 2-3 continued) naman kohli [email protected] [email protected] october...
TRANSCRIPT
![Page 2: ACCESS (CHAPTER 2-3 continued) Naman Kohli naman.kohli@mail.wvu.edu naman.kohli@mail.wvu.edu October 16, 2013 1](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f1e5503460f94c35eed/html5/thumbnails/2.jpg)
UPCOMING DEADLINES
• 21st October – MyITLab Lesson C
• 1st November – Homework 4 (published)
• 4th November – MyITLab Lesson D
• 8th November – Homework 5
2
![Page 3: ACCESS (CHAPTER 2-3 continued) Naman Kohli naman.kohli@mail.wvu.edu naman.kohli@mail.wvu.edu October 16, 2013 1](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f1e5503460f94c35eed/html5/thumbnails/3.jpg)
MID SEMESTER GRADES
• Those not doing well. Can be seen on STAR.
• Out of 280 – Exam, Homework # 1,2,3
• Still 720 + 60 Bonus points left.
• Focus on MyITLab and Exam 2.
3
![Page 4: ACCESS (CHAPTER 2-3 continued) Naman Kohli naman.kohli@mail.wvu.edu naman.kohli@mail.wvu.edu October 16, 2013 1](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f1e5503460f94c35eed/html5/thumbnails/4.jpg)
PREVIOUS CLASS
4
![Page 5: ACCESS (CHAPTER 2-3 continued) Naman Kohli naman.kohli@mail.wvu.edu naman.kohli@mail.wvu.edu October 16, 2013 1](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f1e5503460f94c35eed/html5/thumbnails/5.jpg)
TABLE RELATIONAL STRUCTURE
5
Members Bands
Countries
∞
∞
1
1
![Page 6: ACCESS (CHAPTER 2-3 continued) Naman Kohli naman.kohli@mail.wvu.edu naman.kohli@mail.wvu.edu October 16, 2013 1](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f1e5503460f94c35eed/html5/thumbnails/6.jpg)
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.
• Eg:‘Year’ is a Lookup Type Field
• Two types of lookup fields:1. Lookup references uses field values in another table2. Lookup references items you manually enter in
advance in the same table
We will now do both types…
6
![Page 7: ACCESS (CHAPTER 2-3 continued) Naman Kohli naman.kohli@mail.wvu.edu naman.kohli@mail.wvu.edu October 16, 2013 1](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f1e5503460f94c35eed/html5/thumbnails/7.jpg)
LOOKUP FROM OTHER TABLE EXAMPLE
Create the third table for our database called Countries
Make these fields:
ID AutoNumber (Primary Key)
Country TextFlip to Datasheet view and save the table
…
7
![Page 8: ACCESS (CHAPTER 2-3 continued) Naman Kohli naman.kohli@mail.wvu.edu naman.kohli@mail.wvu.edu October 16, 2013 1](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f1e5503460f94c35eed/html5/thumbnails/8.jpg)
LOOKUP FROM OTHER TABLE EXAMPLE
Create the following Records:
1, England
2, America
Close the table
…
8
![Page 9: ACCESS (CHAPTER 2-3 continued) Naman Kohli naman.kohli@mail.wvu.edu naman.kohli@mail.wvu.edu October 16, 2013 1](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f1e5503460f94c35eed/html5/thumbnails/9.jpg)
LOOKUP FROM OTHER TABLE EXAMPLE
Return to the “Members” table in Design View
Add a 5th 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”
…
9
![Page 10: ACCESS (CHAPTER 2-3 continued) Naman Kohli naman.kohli@mail.wvu.edu naman.kohli@mail.wvu.edu October 16, 2013 1](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f1e5503460f94c35eed/html5/thumbnails/10.jpg)
LOOKUP FROM OTHER TABLE EXAMPLE
Add the Country field to Selected fieldsNext… Next… FinishSave if promptedReturn to Datasheet View in “Members”
Click in the Origin file to assign these:Assign “England” to The Who membersAssign “America” to Van Halen members.
10
![Page 11: ACCESS (CHAPTER 2-3 continued) Naman Kohli naman.kohli@mail.wvu.edu naman.kohli@mail.wvu.edu October 16, 2013 1](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f1e5503460f94c35eed/html5/thumbnails/11.jpg)
SAME TABLE LOOKUP EXAMPLE(LIKE STEP 5H) In the Members table of our example, return
to Design ViewCreate a filed called Instrument of type
Look-up Wizard…Select I will type the values I want.Enter the following:
Vocals, Drums, Guitar, BassSave, Return to Datasheet view to enter
them!
11
![Page 12: ACCESS (CHAPTER 2-3 continued) Naman Kohli naman.kohli@mail.wvu.edu naman.kohli@mail.wvu.edu October 16, 2013 1](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f1e5503460f94c35eed/html5/thumbnails/12.jpg)
SAME TABLE LOOKUP EXAMPLE(LIKE STEP 5H)
12
Members
BandNum FirstName LastName Origin Instrument
1 John Entwhistle England Bass
1 Pete Townsend England Guitar
1 Roger Daltry England Vocals
1 Kieth Moon England Drums
2 David Lee Roth America Vocals
2 Edward Van Halen America Guitar
2 Alex Van Halen America Drums
2 Michael Anthony America Bass
![Page 13: ACCESS (CHAPTER 2-3 continued) Naman Kohli naman.kohli@mail.wvu.edu naman.kohli@mail.wvu.edu October 16, 2013 1](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f1e5503460f94c35eed/html5/thumbnails/13.jpg)
“YES/NO” FIELD TYPE EXAMPLE
Add a field called “Living”Make it of Type Yes/NoSave the table and mark all records except
Keith Moon and John Entwhistle as Living members
* We will come back to this
13
![Page 14: ACCESS (CHAPTER 2-3 continued) Naman Kohli naman.kohli@mail.wvu.edu naman.kohli@mail.wvu.edu October 16, 2013 1](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f1e5503460f94c35eed/html5/thumbnails/14.jpg)
DATE FIELD EXAMPLE
• Add a field called Born of Date/Time datatype
14
Members
Born
10/9/1944
8/23/1946
3/1/1944
5/19/1945
10/10/1954
1/26/1955
5/8/1953
6/20/1954
![Page 15: ACCESS (CHAPTER 2-3 continued) Naman Kohli naman.kohli@mail.wvu.edu naman.kohli@mail.wvu.edu October 16, 2013 1](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f1e5503460f94c35eed/html5/thumbnails/15.jpg)
CREATE A RELATIONSHIP EXAMPLE
Close the tablesOpen RelationshipsNote that lookup wizard created one already!Right click, Show Table, add Bands.Drag [Bands/BandNum] to
[Members/BandNum]Enforce referential, Create, and save.…
15
![Page 16: ACCESS (CHAPTER 2-3 continued) Naman Kohli naman.kohli@mail.wvu.edu naman.kohli@mail.wvu.edu October 16, 2013 1](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f1e5503460f94c35eed/html5/thumbnails/16.jpg)
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…
16
![Page 17: ACCESS (CHAPTER 2-3 continued) Naman Kohli naman.kohli@mail.wvu.edu naman.kohli@mail.wvu.edu October 16, 2013 1](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f1e5503460f94c35eed/html5/thumbnails/17.jpg)
QUERY EXAMPLECreate query in design view
Create Ribbon > Query Design buttonAdd all tables (note the relationships showing)From Bands table double click to add: BandNameFrom Members table add: FirstName, LastName, Instrument, LivingFrom Countries table add: CountryRun the Query !Close and Save Query as RockTrivia
17
![Page 18: ACCESS (CHAPTER 2-3 continued) Naman Kohli naman.kohli@mail.wvu.edu naman.kohli@mail.wvu.edu October 16, 2013 1](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f1e5503460f94c35eed/html5/thumbnails/18.jpg)
18
CREATING “AND” CONDITIONIN THE DESIGN GRID…
![Page 19: ACCESS (CHAPTER 2-3 continued) Naman Kohli naman.kohli@mail.wvu.edu naman.kohli@mail.wvu.edu October 16, 2013 1](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f1e5503460f94c35eed/html5/thumbnails/19.jpg)
19
LET’S TRY AN “AND”
Clear any existing criteriaType England in the Country row and
create the AND condition by placing Yes in the Living row to return all living band members from England
Run itGo back and change Yes to No and
rerun…
![Page 20: ACCESS (CHAPTER 2-3 continued) Naman Kohli naman.kohli@mail.wvu.edu naman.kohli@mail.wvu.edu October 16, 2013 1](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f1e5503460f94c35eed/html5/thumbnails/20.jpg)
20
LET’S DO AN “OR”
Remove the old criteria
We will set the criteria to show all members from The Who OR anyone from any band that is living at all from the list of members…
First lets put “The Who” in the [Bands] Name field and run to see the results
…
![Page 21: ACCESS (CHAPTER 2-3 continued) Naman Kohli naman.kohli@mail.wvu.edu naman.kohli@mail.wvu.edu October 16, 2013 1](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f1e5503460f94c35eed/html5/thumbnails/21.jpg)
21
CREATE THE “OR”
• We wish to not only see the members of The Who, but also in addition to them, anyone that might be alive in the members table.
In Design view, type “Yes” in the OR row for [Members] Living
You should now see a list of 4 additional records (8 total)
![Page 22: ACCESS (CHAPTER 2-3 continued) Naman Kohli naman.kohli@mail.wvu.edu naman.kohli@mail.wvu.edu October 16, 2013 1](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f1e5503460f94c35eed/html5/thumbnails/22.jpg)
22
SORT EXERCISE
Open the RockTrivia Query in Design ViewIn the Sort row for the BandName field,
have it sort the records in Ascending OrderRun the Query
…
![Page 23: ACCESS (CHAPTER 2-3 continued) Naman Kohli naman.kohli@mail.wvu.edu naman.kohli@mail.wvu.edu October 16, 2013 1](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f1e5503460f94c35eed/html5/thumbnails/23.jpg)
23
TWO-LEVEL SORT
Leave the existing sort in place and move the last name field so that it is before the first name field in the Design view…
Click once to select it… let go and then click and drag to move it over
Add a secondary sort by having it do Last Name in Ascending order
Run it. Last names are now show alphabetically inside the band names
![Page 24: ACCESS (CHAPTER 2-3 continued) Naman Kohli naman.kohli@mail.wvu.edu naman.kohli@mail.wvu.edu October 16, 2013 1](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f1e5503460f94c35eed/html5/thumbnails/24.jpg)
24
WE CAN ALSO USE “WILDCARDS”
![Page 25: ACCESS (CHAPTER 2-3 continued) Naman Kohli naman.kohli@mail.wvu.edu naman.kohli@mail.wvu.edu October 16, 2013 1](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f1e5503460f94c35eed/html5/thumbnails/25.jpg)
25
WILDCARD EXAMPLE
We want to see all members whose last names begin with “E”…
Remove all existing sort optionsIn the last name field, type the letter E in
Criteria and run the query.(No results show as no one has just the letter E for a last name)
Make it read E* and rerun to get any other characters…
![Page 26: ACCESS (CHAPTER 2-3 continued) Naman Kohli naman.kohli@mail.wvu.edu naman.kohli@mail.wvu.edu October 16, 2013 1](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f1e5503460f94c35eed/html5/thumbnails/26.jpg)
26
WILDCARDS CONTINUED
Change the criteria to show people whose last names end in “N”*N
Run it and we should see 3 records.Remove all criteria.
![Page 27: ACCESS (CHAPTER 2-3 continued) Naman Kohli naman.kohli@mail.wvu.edu naman.kohli@mail.wvu.edu October 16, 2013 1](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f1e5503460f94c35eed/html5/thumbnails/27.jpg)
USING FIELDS WITHOUT SHOWING
• It is possible to use a field in searching but not show that field in the query results
• Let’s say we want to see all band members whose bands begin and end in “d”, but not see the band name
Remove the “Show” row checkmark for BandName
Create the criteria and run !
27
![Page 28: ACCESS (CHAPTER 2-3 continued) Naman Kohli naman.kohli@mail.wvu.edu naman.kohli@mail.wvu.edu October 16, 2013 1](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f1e5503460f94c35eed/html5/thumbnails/28.jpg)
28
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 Numeric data type fields
![Page 29: ACCESS (CHAPTER 2-3 continued) Naman Kohli naman.kohli@mail.wvu.edu naman.kohli@mail.wvu.edu October 16, 2013 1](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f1e5503460f94c35eed/html5/thumbnails/29.jpg)
29
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
![Page 30: ACCESS (CHAPTER 2-3 continued) Naman Kohli naman.kohli@mail.wvu.edu naman.kohli@mail.wvu.edu October 16, 2013 1](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f1e5503460f94c35eed/html5/thumbnails/30.jpg)
30
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…
![Page 31: ACCESS (CHAPTER 2-3 continued) Naman Kohli naman.kohli@mail.wvu.edu naman.kohli@mail.wvu.edu October 16, 2013 1](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f1e5503460f94c35eed/html5/thumbnails/31.jpg)
31
THE EXPRESSION BUILDER…
![Page 32: ACCESS (CHAPTER 2-3 continued) Naman Kohli naman.kohli@mail.wvu.edu naman.kohli@mail.wvu.edu October 16, 2013 1](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f1e5503460f94c35eed/html5/thumbnails/32.jpg)
32
LET’S MAKE A QUERY TO TRY THIS…
We want to figure out how many years they have/would been 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 ####
…
![Page 33: ACCESS (CHAPTER 2-3 continued) Naman Kohli naman.kohli@mail.wvu.edu naman.kohli@mail.wvu.edu October 16, 2013 1](https://reader036.vdocuments.us/reader036/viewer/2022062721/56649f1e5503460f94c35eed/html5/thumbnails/33.jpg)
33
CHANGING DECIMALS PRECISION
In design view, right click Age fieldClick PropertiesSelect Fixed for Format and 0 for Decimal
PlacesRe-run it to see it without the decimal
places