Guided Exercises

Go to your IU OneDrive and open your username-Haptown database.

The Relationships diagram is at the end of the instructions, but can be opened from the Database Tools tab in your Access file, too. When reading the directions for a query, you need to decide which table(s) to use and looking at the Relationships can be helpful.

Lookup Field Filtering

One thing we can do is change a lookup field to only show a specific subset of options in a drop-down list. For example, we can change our Ticket Data’s assigned officer to only allow us to select Police Department employees, instead of any employee. So let’s do that.

1. Open tblTicketData in Design view.
2. Switch to the Lookup tab at the bottom.
3. Notice there is a SQL query for Row Source, which selects the ID, last name, and job title from tblEmployee. We can alter that, so click on the three dots … at the end of that Row Source line.
4. In this tblTicketData: Query Builder window, add the Department field to our controls, uncheck the Show option, and type “Police Department” (including the quotes) in the Criteria space. Your window should look like this:
   
5. Now click the X to close that Query Builder window, and Yes to the save prompt.
6. Switch back to Datasheet view, selecting Yes to save if prompted. Notice how the drop-down options for the officer assignment is limited to just those employees from the Police Department.
7. Let’s assign the first 20 tickets to a random selection of police staff  so we can query this data later.
8. Close and save tblTicketData.
   

Advanced Queries

1. 

   You have been given the task of following up on mayor feedback that was deemed Negative and had to do with safety (use the word “safe”). Create a query that lists consultant’ FirstName, LastName, and EmailAddress, as well as SubmissionDate. Ensure you filter the Sentiment and Comment fields correctly. Save the query as qryGE6.
   

2. Let’s expand that search for any feedback item that came in on 4/3/2025, as we think a large event may have been going on that day. Let’s also sort Ascending by LastName.
   • Run your query and notice consultants show up multiple times, because of different feedback submissions.
   • Since we are targeting April 3, we don’t need to list SubmissionDate. So let’s hide that appropriately.
   • Let’s also show how many submissions each consultant has assigned herein, and we’ll call that field FeedbackCount. That field should look like: FeedbackCount: [FeedbackID] and have an aggregate of Count.
   • Change the sort to Descending by FeedbackCount.
   • Now we have a list of the consultants handling these April 3rd feedback submissions and how many they are dealing with. We can contact them and follow-up as needed. Save this query as qryGE7.
     
3. We want to report out on how much capital we have listed in the database. Create a query that will give a AssetTotalValue of all asset current values. Format this field as Currency if necessary. Add a second field called NumAssets that counts all the assets we have. Save this query as qryGE8.
   
4. Now let’s narrow that down to a specific set of Computer assets. Create a query that will list the BuildingName and a calculated field that is the total current value called BldgAssetValue of all assets per department.
   1. Add a field called ValueChange which will calculate the difference between the current value and the acquisition cost. These will be negative numbers, as assets depreciate in value over time.
   2. Similar to the last query, add a NumAssets field to calculate the count for each building.
   3. You may optionally enable the Totals button in the query results and use a sum to verify that grant total here matches the one from qryGE8.
   4. Finally, filter the results by the AssetType “Computer” and sort Descending by BldgAssetValue. Save this query as qryGE9.
      

Notice that if we wanted to calculate the AVERAGE asset value per item, we’d have to take those total sums and divide by the counts. But we cannot do that in a single query in Access – that’s where we would use what we call “query on query”, but we will not cover that in this class.

More Practice

Haptown ERD