Assignment 6: Creating the Babbage Bookkeeping Database Create a query to display the Client Number, Client Name, and Address fields for all clients with an address on Maum. Save the query as “Q22-Client-Maum Query.” Create a query that will allow the user to enter the city to search when the query is run. The query results should display the Client Number, Client Name, and Bookkeeper Number. Test the query by searching for those records where the client is located in Portage. Save the query as “Q23-Client-City Query.” Create a query from the Client table to display the cities in ascending order. Each city should appear only once (i.e., no repeating city). Save the query as “Q24-City-Sorting Query.” Create a query to display the Client Number, Client Name, and Balance fields for all clients where the bookkeeper number is 24 or 34 and the balance is greater than $300.00. Save the query as “Q25-Client-Bookkeeper24-34 Query.” Create a query to display the First Name, Last Name, and Hourly Rate fields from the Bookkeeper table and the Client Number, Client Name, and Balance fields from the Client table. Sort the records in ascending order by bookkeeper’s last name and client’s name. Save the query as “Q26-Client-Bookkeeper Query.” Create a query for the Bookkeeper table to display the Bookkeeper Number, First Name, Last Name, and Hourly Rate in the design grid. Create a new field named as “Hour Worked” in this query to calculate the number of hours each bookkeeper has worked (YTD Earnings/Hourly Rate). Display the data in this “Hour Worked” field as a standard number with 0 decimal place and also show this new created field in the Datasheet view . Save the query as “Q27-Bookkeeper-Hour Worked Query.” Create a query to display the following statistics: the total balance for all clients - Save the query as “Q28a-Client Total Balance Query.” Display Balance field. the total balance for each bookkeeper. Save the query as “Q28b-Bookkeeper Total Balance Query.” Display Bookkeeper Number and Balance fields. the total balance for clients of bookkeeper 22 - Save the query as “Q28c-Client-Bookkeeper22 Balance Query.” Display Bookkeeper Number and Balance fields. Create the crosstab query as shown in the Figure below: The crosstab groups total of clients’ balances by city and bookkeeper number. Save the query as “Q29-City-Bookkeeper Crosstab Query.”

Question

Assignment 6: Creating the Babbage Bookkeeping Database

Create a query to display the Client Number, Client Name, and Address fields for all clients with an address on Maum. Save the query as “Q22-Client-Maum Query.”

Create a query that will allow the user to enter the city to search when the query is run. The query results should display the Client Number, Client Name, and Bookkeeper Number. Test the query by searching for those records where the client is located in Portage. Save the query as “Q23-Client-City Query.”

Create a query from the Client table to display the cities in ascending order. Each city should appear only once (i.e., no repeating city). Save the query as “Q24-City-Sorting Query.”

Create a query to display the Client Number, Client Name, and Balance fields for all clients where the bookkeeper number is 24 or 34 and the balance is greater than $300.00. Save the query as “Q25-Client-Bookkeeper24-34 Query.”

Create a query to display the First Name, Last Name, and Hourly Rate fields from the Bookkeeper table and the Client Number, Client Name, and Balance fields from the Client table. Sort the records in ascending order by bookkeeper’s last name and client’s name. Save the query as “Q26-Client-Bookkeeper Query.”

Create a query for the Bookkeeper table to display the Bookkeeper Number, First Name, Last Name, and Hourly Rate in the design grid. Create a new field named as “Hour Worked” in this query to calculate the number of hours each bookkeeper has worked (YTD Earnings/Hourly Rate). Display the data in this “Hour Worked” field as a standard number with 0 decimal place and also show this new created field in the Datasheet view . Save the query as “Q27-Bookkeeper-Hour Worked Query.”

Create a query to display the following statistics:
1. the total balance for all clients - Save the query as “Q28a-Client Total Balance Query.” Display Balance field.
2. the total balance for each bookkeeper. Save the query as “Q28b-Bookkeeper Total Balance Query.” Display Bookkeeper Number and Balance fields.
3. the total balance for clients of bookkeeper 22 - Save the query as “Q28c-Client-Bookkeeper22 Balance Query.” Display Bookkeeper Number and Balance fields.

Create the crosstab query as shown in the Figure below: The crosstab groups total of clients’ balances by city and bookkeeper number. Save the query as “Q29-City-Bookkeeper Crosstab Query.”