Mgis 301 Lab 3 Handout (Advanced Queries and Relationships)
1)Download the file bosb 2006.
2) Query1. Show the balances of people whose due dates are in 2006.
-You should open the query window and select the table bosb 2006.
-In order to proceed to your query select the related fields except datedue for the query above.
- Rightclick on an empty field and select Build option. Build option helps you to use functions in Access.
-Now, under functions you should click on build-in functions.
- From the middle column you should select the date/time.
- From the leftmost column, click twice on Year function.
Year(<number>) expression should show up on your screen. Using this formula you can filter your records according to year. Inside the parenthesis instead of number write field name you want to filter.
Note: Access has a similar month, day and hour functions.
Expr1: Year([datedue])
After you close the build menu, you should see the above expression on a field. Expr1 is the name of the new field. You can rename it if you want to have a more meaningful view of the query.
3) Query 2. Show the inflows, outflows and balances according to cities.
In order to see this query, you should use the iif function. Iif function is similar to the if function used in Excel.
Iif functions’ structure is as such,
Iif(Logical test, Value if true, Value if false)
Ex: if x<3 y= 1 else y= 2
Iif( x<3, 1,2)
Ex: If the month is February x= 1 else x= 0
Iif( month=February,1,0)
Now that you learned iif function, you may start to design your query. Select the city and the balances as fields required.
On two empty fields write your iif function for inflows and outflows
Hint: Outflow should be a negative balance.
Open the totals row by clicking on Totals button on the above toolbar and use group by and sum options.
-You should group by city and sum the balances.
Query 3:
Balances of each month of every year
Query 4:
Max balance of each city
Query5:
Number of vendors in each city
Relationships and Multiple Tables:
-Open the Monroes file.
As you can see in Monroes database there are multiple tables. You can link these tables to each other by establishing relationships between them. In order to start building relationships you should first select the primary keys.
To establish relationships, close all tables, and choose relationships from the tools menu. You can establish relationships between two fields by simply dragging the “from” field onto the “to” field. For example, to establish a relationship between the Guest field in the Guest table and Guest field in the Reservations table, drag the Guest field in the Guest table towards the Guest field in the Reservations table and drop it. The relationship is a one-to-many one, and it is safer if we enforce referential integrity (Referential integrity is a system of rules that Microsoft Access uses to ensure that relationships between records in related tables are valid, and that you don't accidentally delete or change related data.)
0)Queries that combine data from multiple tables. Suppose that a customer walks into the MMI and asks if we have room available with a daily room rate lower than 200$. To find out, we have to build a query that uses multiple tables. In the database view, click on queries, then new, and design view. Then in the show table dialog box, add Room, Reservations tables and close the show tables dialog box. In query design, include Room and Rate fields from Room table, ArrivalDate and DepartureDate from the Reservations table. Type in the criteria “<200” under the Rate (named qry_Available). Can you answer the question based on the output you see?
Note: This output is not appropriate in “real-life” since it lists all the information about the room reservations regardless of the occupancy dates. There are ways of filtering this information further, but we will not cover them as it is beyond the scope of our lecture.
Now let us construct some more queries (questions we ask to our database)
- The list of customers who left MMI on the 11-Mar-2005 (Include room rates in the query output) (name it qry_DepartedOn)
- Modify qry_DepartedOn so it becomes rather a general one and prompts for the departure date. (name it qry_DepartedOn_Prompt)
- Average Length of stay (of all customers)? (qry_Avg_Stay)
- Number of reservations for each room so far? (qry_Reservation_Count)
Guest
Battenburg, Randall
Collins, Thomas
Gonzalez, Homer
Room Table Sample Records
Room / Rate
Coolidge / $175.00
Lincoln / $300.00
Washington / $325.00
Reservations
Guest / Room / ArrivalDate / DepartureDate / NoOfGuests
Battenburg, Randall / Washington / 12-Apr-05 / 18-Apr-05 / 2
Battenburg, Randall / Washington / 18-May-05 / 22-May-05 / 1
Collins, Thomas / Lincoln / 09-Sep-05 / 19-Sep-05 / 2
Gonzalez, Homer / Lincoln / 17-May-05 / 19-May-05 / 3