Access chapters 35 Nagendra Vemulapalli Nagendra vemulapallimail wvu
- Slides: 41
Access chapters 3&5 Nagendra Vemulapalli Nagendra. vemulapalli@mail. wvu. edu 2/5/2022 1
Reports • Let us put things into a visually professional format suitable for printing • Creation can be somewhat like doing Forms with Subforms… 2/5/2022 2
Reports • • • Open the File Music. Accdb Create ribbon > Reports Group > Report Wizard Add these fields: [Bands] Band Name [Members] First. Name, Last. Name, Instrument, Living [Countries] Country 2/5/2022 3
Reports • Click Next 3 times • Sort Ascending by Last. Name and then First. Name • Click Next 2 times • Select any style you like • Click “Finish” • To Change any of the column widths in Report Right Click and Select Layout View Drag the fields to resize!! 2/5/2022 4
Exercise • Go to the lecture notes page and Download Music_extended. accdb • Note the added records to complete the English band Def Leppard and also the addition of the Canadian band Rush… 2/5/2022 5
Exercise • Create a new query in design view • Add 3 tables: Bands, Countries, & Members… 2/5/2022 6
Specify an Exact Match Condition • An exact match condition only shows records that match your criteria in the “Criteria” row of the query design grid • The field values of the records for the field the criteria is specified in must match the condition exactly to be shown in the query results 2/5/2022 7
Exercise • Add the following fields from these tables: • [Bands] Band. Name • [Member] First. Name, Last. Name • [Countries] Country • Run the Query to see the unrestricted results of all the 16 records 2/5/2022 8
Adding Criteria • Return to Design View • In the criteria row for Country, type England to have it show us a Dynaset with only band members from England • Rerun the query • Change England to America and rerun • Try Portugal to see that nothing returns! 2/5/2022 9
Using Comparison Operators… “And” / “Or” • When using multiple conditions for a query, you can use the logical operators to combine conditions – The “And” logical operator specifies that both conditions must be met – The “Or” logical operator specifies that one or the other of the conditions must be met 2/5/2022 10
Using “And” and “Or” • When you enter two conditions in the same row of the query design grid, an “And” condition is created • If you enter two conditions that are on separate rows, an “Or” condition is created 2/5/2022 11
Illustration of “And” & “Or” Logic 2/5/2022 12
Creating “And” Condition in the Design Grid… 2/5/2022 13
Let’s Try an “And” • Clear any existing criteria • Add the field [Members] Living • Type 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 it • Go back and change Yes to No and rerun 2/5/2022 14
Let’s do an “Or” • Remove the old criteria • We will set the criteria to show all members from Rush OR anyone from any band that is living at all from the list of members… • First lets put “Rush” in the [Bands] Name field and run to see the results 2/5/2022 15
Create the “OR” • We wish to not only see the members of Rush, 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 10 additional records (13 total) 2/5/2022 16
Sorting Results by Data Type… 2/5/2022 17
Sort Exercise • Open the Rock. Trivia Query in Design View • In the Sort row for the Band. Name field, have it sort the records in Ascending Order • Run the Query 2/5/2022 18
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 2/5/2022 19
We can also use “Wildcards” 2/5/2022 20
Wildcard Example • • We want to see all members whose last names begin with “E”… Remove all existing sort options In 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… 2/5/2022 21
Wildcards continued • Change the criteria to show people whose last names end in “N” • *N • Run it and we should see 6 records. • Remove all criteria. 2/5/2022 22
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 Band. Name • Create the criteria and run ! 2/5/2022 23
Operators Note: This technique is similar to what part of a query asks for in HW 4 ! 2/5/2022 24
Date Field Entries for Birthday • Note Born field in Members table is completed • Close Members table 2/5/2022 25
Criteria & Operators Exercise • Go to Design view for Rock. Trivia, add the Born field, and clear criteria, re-show Band. Name • In the criteria row for born, add: >1/1/1960 • Note we get 3 records • Try making it: >=1/1/1950 AND <=1/1/1960 • We get 9 Records • Change it to: <1/1/1960 • We get 13 records 2/5/2022 26
Calculations in a Query… • Mathematical calculation can be done 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 2/5/2022 27
Calculations in a Query… • To perform a calculation in a query, you must created a calculated field in query design. • Make certain that you are following the rules of precedence !(Please Excuse My Dear Aunt Sally) • Two options for entering expressions: – Enter the expression directly into the field text box – Enter the expression in the Expression Builder 2/5/2022 28
Creating a Calculated Field • Remove the existing criteria • Click in the next available blank field where the name would be, and then click the magic wand “Builder” icon above • The expression builder shows up… 2/5/2022 29
The Expression Builder… 2/5/2022 30
Let’s make a Query to try this… • Download and Open Music_extended. accdb • 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 #### 2/5/2022 31
Changing Decimals Precision • Note: Similar to what is needed for to set decimal places on query results in homeworks!! YOU MUST RUN THE QUERY ONCE FIRST ! • 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 2/5/2022 32
Total Queries • “Totals” in a query can group field values and also perform basic computational functions on them • To make the Total row appear, you need to click the Sigma symbol “Totals” button in query design view ! 2/5/2022 33
Premise We are running an investment firm and have some consultants working for us who have clients for which they manage investments. We want a query that will show us how many clients each consultant has and the total amount of investments each consultant is handling… • Save locally and open Total_example. accdb 2/5/2022 34
Structure of Tables & Relationship This Database has not yet had a relationship created. We will do it in the query for this one. Sometimes under special circumstances you will create them here instead of in the relationships window. Generally it is done in the relationships window though… 2/5/2022 35
Building the Total Query • Create ribbon > Other group > Query Design • Add both tables • Create relationship inside query by dragging Consultant. ID fields together 2/5/2022 36
Building the Total Query Add the field [Consultants]Last. Name Add the field [Clients]Assets Run it without Total options > back to design view • Click the button on the Query Tools / Design ribbon to insert a “Total: ” row • Note that the default for each is “Group By” • • 2/5/2022 37
Building the Total Query • We will Group By the last name of the consults to show each of them singularly • We will Count the last names of the clients as they are tied to the consultants to show many clients each has ! (Yes, kind of cool) • We will Sum the assets of the clients to show much all clients of each consultant have together 2/5/2022 38
Setting up the Total Row • Have [Consultants] Last. Name as Group By • Have [Clients] Last. Name as Count • Have Assets as Sum • Run the Query 2/5/2022 ! 39
Aliasing the Field Names • Sometimes for a Field, a Calculated field, or a Total field, we like to make the display name more specific in the query results • To do this, simply change the field name in design view by adding a new name and an “: ” • An example for the clients Last. Name field: # of Clients: Last. Name 2/5/2022 40
Alias the 3 fields in Design View • • Consultant: Last. Name # Clients: Last. Name Total Assets: Assets Run the Query and note the field names in the dynaset ! 2/5/2022 41
- Nagendra modadugu
- Cynthia nagendra
- Terminal access controller access control system
- Terminal access controller access-control system
- Wvu research corp
- Chris randall wvu
- Wvu open house
- Wvu pe classes
- Degree works wvu
- Wvu cafee
- Math department wvu
- Wvu degreeworks login
- Wvu department of surgery
- Cs101wvu
- Wvu gef
- Wvu emba
- Wvu student legal services
- Wvu risk management
- Stat 211
- John quaranta wvu
- Wvu citi training
- Symplicity wvu law
- Accessibility services wvu
- Wvu math placement test
- Wvu oit
- Wvu oit
- The giver vocabulary list
- Hrpa membership
- Touching spirit bear chapters 1-2 answers
- Things fall apart chapter 14-19
- To kill a mockingbird chapters 1-5
- The wave chapter 2
- Fever 1793 chapter 24
- Quotes from catcher in the rye
- Apa chapters
- The scarlet letter chapter 23 summary
- Chapter 11 tkam
- All deadlocks involve conflicting needs for
- Frankenstein chapter summaries 11-16
- Summary of chapters 4-6 lord of the flies
- Night chapter 5 notes
- The no1 ladies' detective agency summary of each chapter