Creating Queries, sorting records, changing the appearance of the datasheet

Global Human Resources Consultants
CREATING QUERIES, SORTING RECORDS, CHANGING THE APPEARANCE OF THE DATASHEET

GETTING STARTED

• Open the file NP_AC365_2021_3a_FirstLastName_1.accdb, available for download.
• Save the file NP_AC365_2021_3a_FirstLastName_1.accdb as NP_AC365_2021_3a_FirstLastName_2.accdb
  • Edit the file name by changing “1” to “2”.
  • If you do not see the .accdb file extension, do not type it. The file extension will be added for you automatically.
• With the file NP_AC365_2021_3a_FirstLastName_2.accdb open, ensure that your first and last name is displayed as the first record in the _GradingInfoTable table.
  • If the table does not display your name, delete the file and download a new copy.

PROJECT STEPS

1. As project manager, you need to be able to query the database to help make decisions
   and answer questions from other managers. Create a query based on the Consultant table in Query Design View with the following options:
   a. Add the ConsultantID, LastName, and FirstName fields to the design grid in that order.
   b. Sort the records in ascending order by LastName.
   c. Save the query with the name LastNameSorted.
   Open the query in Datasheet View and then close it.
2. Open the ResideCriteria query in Design View and make the following changes to the query:
   a. Delete the ConsultantID column from the design grid.
   b. Add criteria to select only those records where the Reside field value equals USA.
   c. Save the changes to the ResideCriteria query.
   Open the query in Datasheet View and then close it.
3. Open the PaidOrBalance query in Design View and add criteria to select only those records where the Paid field value equals 0 or the Balance field value equals 0. Save the changes to the query. Open the query in Datasheet View, confirm that four records appear in the PaidOrBalance query results, and then close the query.
4. Open the ComparisonSalary query in Design View and make the following changes to
   the query:

a. Add the FirstName field to the query design grid. The FirstName field should immediately follow the ConsultantID field.
b. Add criteria to select only those records where the Salary field value is greater than 70,000.
c. Sort the records by LastName in descending order.
d. Run the query.
e. With the query open in Datasheet view, edit the first record by changing the FirstName value to Melodie from Melodee.
Confirm that the query results match Figure 1. Save the query again and close it.
Figure 1: ComparisonSalary Query Results

5. Open the ResideLastName query in Design View and make the following changes:
   a. Move the Reside field to the beginning of the design grid so that the order of the fields in the grid is Reside, LastName, FirstName, StartDate.
   b. Sort the records in descending order by the Reside field and in ascending order by LastName.
   Save the changes to the query. Open the query in Datasheet View and confirm that it matches Figure 2. Close the query.
   Figure 2: ResideLastName Query Results
6. Open the BalanceAndPaid query in Design View and add criteria to select only those records where the Paid field value equals 0 and the Balance field value equals 0. Save the changes to the query. Open the BalanceAndPaid query in Datasheet View, confirm that one record appears in the query results, and then close the query.
7. Open the DateTime query in Design View and add criteria to select only those records where the StartDate field value is greater than 1/1/2019. Save the changes to the query. Open the query in Datasheet View, confirm that 15 records appear in the query results, and then close the query.
8. Many queries require data from more than one table. For example, you may want the full country name to display rather than the country abbreviation. Create a query in Design View based on the Consultant and Country tables with the following options:
   a. Add the Consultant table and the Country table to the design window.
   b. Add the FirstName and LastName fields from the Consultant table to the design grid.
   c. Add the CountryName field from the Country table to the design grid.
   d. Join the Consultant table and the Country table using the Reside field in the Consultant table and the CountryCode field in the Country table. e. Save the query, using Country-Consultant as the name. Open the query in Datasheet View and then close it.

9. Open the ProjectMonths query in Design View and add criteria to select only those records where the Months field value equals 2. Save the changes to the query. Open the query in Datasheet View, confirm that two records appear in the query results, and then close the query.
10. Open the EstMaxMonths query in Design View. Modify the query by creating a calculated field. Enter MaxMonths: [Months] + 3 in the Zoom dialog box of the first empty column in the design grid. Save the query. Open the query in Datasheet View and then close the query.
11. Open the Consultant table in Datasheet View. Use the Find and Replace feature to find the consultant whose first name is Georgeanna and replace the name, using Gina as the new value. Close the Consultant table.
12. Open the Country table in Datasheet View and make the following changes:
    a. Change the font to Arial.
    b. Change the font size to 10.
    c. Sort the records in the table in ascending order by CountryName.
    Save the changes to the table and close it.
13. Open the Client table in Datasheet View. Select the Calculation field and modify the expression by changing the plus (+) sign in the calculation to a minus (-) sign. Save the changes to the table and close it.
14. Open the Skill table in Datasheet View. Sort the records in descending order by SkillName. Save the changes to the table and close it.
15. Open the Consultant table in Datasheet View and hide the Reside and Salary columns.
    Save the changes to the table and close it.
    Save and close any open objects in your database. Compact and repair your database, close it,
    and then exit Access. Follow the directions on the website to submit your completed project.