This is a Microsoft Access Assignment
In the Lab: 2 Maintaining the Babbage Bookkeeping Database Assignment 7: Presenting Data in the Babbage Bookkeeping Database Problem: Babbage Bookkeeping already has realized several benefits from the database you created. The company now would like you to prepare reports for the database. Babbage Bookkeeping also needs to maintain additional data on the bookkeepers. The company wants to separate their clients into three different categories and also wants to create reports for displaying the data with grouping and summary option. Instructions: Use the database created in the Assignment6 for this assignment. Copy the file and rename the copying file to Your Last Name + First Name + Assignment7. Perform the following tasks: 1. Open the Client table in Design view. a. Add the field “Client Type” to the Client table. b. The field should appear after the Zip Code field. c. Define the data type of the field as text with a width (size) of 3. d. This field will contain data on the type of client. The client types are MAN (Manufacturing), RET (Retail), and SER (Service). Save changes to the structure. 2. Create an update query to change all the entries in the Client Type column to RET. This will be the type of most clients. Run this query and save the query as “Q31-Update Client Type to RET Query.” 3. Set the following properties to the fields in the Client table and save the changes to the table. a. Increase the size of the Client Name field to 25. b. Make the Client Name field a required field. c. Specify the legal values (i.e., set the validation rule) of MAN, RET, and SER for the Client Type field. Include an appropriate validation text. d. Set the Format property to ensure that any letters entered in the Client Number field appear as uppercase. e. Specify that balance must be between $0.00 to $1,500.00. Include an appropriate validation text. f. Make the Client Type field a Lookup field (i.e., a field with the drop-down value). g. Assign a default value of RET to the Client Type field. 4. Make the following changes to the Client table. You can use either the Find button or Filter By Selection to locate the records to change: h. Change the client type for clients G56, J77, and T45 to SER. i. Change the client type for clients B26 and S56 to MAN. j. Change the client name of the client S56 to SeeSaw Industries. 5. Add the following clients to the Client table: Client Number Client Name Address City Zip Code Client Type Balance Bookkeeper Number C21 Crompton Meat Market 72 Main Empeer 58216 RET $0.00 24 L50 Lou’s Salon 124 Fulton Grant City 58121 SER $125.00 34 6. Resize the Client Name column to best fit the new data, and save the changes to the layout of the table. Change the font to Courier New and font size to 9. Change gridline color (not the alternate color) to blue. We learn how to do this in chapter 1 (hope you do not forget it yet ) 7. Open the Client table and use Filter by Selection to find all records where the client has a balance of $0.00 AND has the Client Type of SER. Delete these records. Remove the filter. 8. Create the report as shown below for the Client table. Group the report by Client type and sort the records within Client Type by Client Name. Save the report as “Client Income Report.” Note that you will have to rearrange the order of the columns under the Design view after the report is created. 9. Create the report as shown below. Preview the report to check page margins and orientation. Adjust as necessary. Save the report as “Bookkeeper/Client Report.” Reminder: The file must be named as Your Last Name + First Name + Assignment7. For example, if your name is John Smith, the file must be named: SmithJohnAssignment7. 5% will be deducted for the wrong filename. Babbage Bookkeeping Bookkeeper NumberLast NameFirst NameAddressCityZipcodeHourly RateYTD Earnings 22LewesJohanna26 CottonPortage59130¤ 14.50¤ 19,245.00 24RodriguezMario79 MarsdenGrant City58120¤ 13.50¤ 18,745.00 34WongChoi263 TopperEmpeer58216¤ 14.00¤ 17,750.00 Client NumberClient NameAddressCityZip CodeBalanceBookKeeper Number A54Afton Mills612 RevereGrant City58120¤ 315.5022 A62Atlas Suppliers227 dandelionEmpeer58216¤ 500.0024 B26Blake-Scripps557 MaumGrant City58120¤ 229.5024 D76Dege Grocery446 LintonPortage59130¤ 485.7534 G56Grand Cleaners337 AbelardEmpeer58216¤ 265.0022 H21Hill Shoes247 FultonGrant City58121¤ 228.5024 J77Jones Plumbing75 GettyPortage59130¤ 0.0034 M26Mohr Crafts665 MaumEmpeer58216¤ 312.5022 S56SeeSaw Ind.31 LiatrisPortage59130¤ 362.5034 T45Tate Repair824 RevereGrant City58120¤ 254.0024 SELECT [Client Table].[Client Number], [Client Table].[Client Name], [Client Table].[Balance] FROM [Client Table] WHERE [Client Table].[Balance] > 300; SELECT [Client Table].[Client Number], [Client Table].[Client Name], [Client Table].[Address] FROM [Client Table] WHERE [Client Table].Address Like '*maum*'; SELECT [Client Table].[Client Number], [Client Table].[Client Name], [Client Table].[BookKeeper Number] FROM [Client Table] WHERE City like ['Please enter name of the city']; SELECT DISTINCT ([Client Table].[City]) FROM [Client Table] ORDER BY [Client Table].[City]; SELECT [Client Table].[Client Number], [Client Table].[Client Name], [Client Table].[Balance] FROM [Client Table] WHERE [Client Table].[Balance] > 300 and [Bookkeeper Number] = '24' or [Bookkeeper Number] = '34'; SELECT Bookkeeper.[First Name], Bookkeeper.[Last Name], Bookkeeper.[Hourly Rate], [Client Table].[Client Number], [Client Table].[Client Name], [Client Table].Balance FROM Bookkeeper INNER JOIN [Client Table] ON Bookkeeper.[Bookkeeper Number] = [Client Table].[BookKeeper Number] ORDER BY Bookkeeper.[Last Name], [Client Table].[Client Name]; SELECT Bookkeeper.[Bookkeeper Number], Bookkeeper.[First Name], Bookkeeper.[Last Name], Bookkeeper.[Hourly Rate], ([YTD Earnings]/[Hourly Rate]) AS [Hour Worked] FROM Bookkeeper; SELECT DISTINCTROW Sum([Client Table].[Balance]) AS [Sum Of Balance] FROM [Client Table]; SELECT DISTINCTROW [Client Table].[BookKeeper Number], Sum([Client Table].Balance) AS [Sum Of Balance] FROM [Client Table] GROUP BY [Client Table].[BookKeeper Number]; SELECT DISTINCTROW [Client Table].[BookKeeper Number], Sum([Client Table].Balance) AS [Sum Of Balance] FROM [Client Table] WHERE [Client Table].[BookKeeper Number] = '22' GROUP BY [Client Table].[BookKeeper Number]; TRANSFORM Sum([Client Table].[Balance]) AS SumOfBalance SELECT [Client Table].[City], Sum([Client Table].[Balance]) AS [Total Of Balance] FROM [Client Table] GROUP BY [Client Table].[City] PIVOT [Client Table].[BookKeeper Number];