CGS Access
From the current view (Layout view), add automatic totals to the Premium column to calculate the average premium
Click any value in the Premium column. On the Report Layout Tools Design tab, in the Grouping & Totals group, click the Totals button. Select Average.
Create a parameter query where the user will enter a value to use as the criterion for the InsuranceType field. Use the prompt Enter Insurance Type. Run the query using the insurance type dental
Click criteria row under InsuranceType column, type [Enter Insurance Type], click Query Tools Design tab, results group, click Run, type dental in Enter Insurance Type dental, click ok
Change the Navigation Pane grouping option to Object Type
Click down arrow next to All Dates in the access group. In the Category/Group menu, you clicked the Object Type menu item.
Using the current view, delete the Attachments field from the table
Click in the Attachments field. On the Table Tools Design tab, in the Tools group, click the Delete Rows button. Click Yes.
Apply the Zip Code input mask format to the selected field (Zip/Postal Code). Do not change default format or placeholder characters. Store the data without the symbols
Click the Input Mask box in the Field Properties pane. Click the Build... button. Click Zip Code. Click Next. Click Next. Click Next. Click Finish.
Add a new record to the table with staff ID 10-9999 and advance to the second field.
Click the New (blank) record button at the bottom of the table. Type 10-9999 in the StudentID field and press Tab.
Modify this query so the InsuranceType field is hidden in the query results, and then run the query
Click the Show box in the InsuranceType column to remove the checkmark. On the Query Tools Design tab, in the Results group, click the Run button.
From Datasheet view, add a new field to the far right of the table to store a picture of each staff member. Add the picture JerryWashington to the fifth record
Click the arrow in the Click to Add column to expand the list of available field data types. Click Attachment. Double-click the Attachment field in the fifth record. The Attachments dialog opens. Click the Add... button. Double-click the JerryWashington file to add it. Click OK.
From the current view (Layout view), change the width of the ProviderName column, so the company names do not wrap the two lines (approximately 1.3 inches)
Click the right border of the ProviderName column and drag to the right until the entire name Health Care One is visible on one line.
Create a new table in Datasheet view. Save it with the name: InsurancePlans
Create tab, tables group, click table, click Table from the left, click save at the top left of the screen, type InsurancePlans in the Table Name
From Design view, modify the IncreaseType field to use a lookup list with Merit and COLA in a single column, Limit the field to values in the list only
Expand the Data Type list for the IncreaseType field, and select Lookup Wizard... Click the I will type in the values that I want. radio button. Click Next. In the first cell under Col 1, type Merit. Press Tab. Type COLA. Click Next. Click the Limit to List check box. Click Finish.
From Design view, change the data type for the StaffID field to use a lookup list. The values in the list should include LastName from the Staff table. Items in the should sort alphabetically by the LastName field. Allow Access to hide the key column. Enforce data integrity, and do not allow deletions that would violate the integrity of records. Save the table
Expand the Data Type list for the StaffID field and select Lookup Wizard¿ Click Next. Click Table: Staff. Click Next. Double-click LastName. Click Next. Expand the first sort box, and click LastName. Click Next. Click Next. Click the Enable Data Integrity check box. Click Finish. Click Yes.
Switch this report to Design view
Home tab, views group, view arrow, design view
From the current view, move the StartDate field so it is the last field in the table
In the Access Table, you clicked on StartDate, and dragged StartDate after LastName.
Create a Single Record form from the InsurancePlans table
In the Create Ribbon Tab in the Forms Ribbon Group, you clicked the Form button.
Run the Compact & Repair tool to optimize database efficiency
In the Database Tools Ribbon Tab in the Tools Ribbon Group, you clicked the Compact and Repair Database button.
Create a new query in Design view using the following fields in this order: From the InsurancePlans table, add the InsuranceID, InsuranceType, SubscriptionType, and Premium fields. From the InsuranceProviders table, add the ProviderName field. Run the query
On the Create tab, in the Queries group, click the Query Design button. In the Show Table dialog, double-click InsurancePlans and InsuranceProviders. Click Close. Double-click these fields in the InsurancePlans table in this order: InsuranceID, InsuranceType, SubscriptionType, and Premium. Double-click the ProviderName field in the InsuranceProviders table. On the Query Tools Design tab, in the Results group, click the Run button.
Create a new relationship between the StaffID field in the Staff table and Reviewer field in the Reviews table. Enforce data integrity between the two tables
On the Database Tools tab, in the Relationships group, click the Relationships button. Click StaffID in the Staff table and drag to Reviewer in the Reviews table. In the Edit Relationships dialog, click the Enforce Referential Integrity check box. Click Create.
Import all of the fields from the Staff worksheet in the Staff Excel file to a new table. Use the first row as column headings. Do not change any field information. Use the StaffID field as the primary key. Accept the new table Staff. Do not save the import steps
On the External Data tab, in the Import & Link group, click the New Data Source button, select From File, and click Excel. Click the Browse button. Click the Staff file. Click Open. Click OK. Click Next. Click the First Row Contains Column Headings check box. Click Next. Click Next. Click the Choose my own primary key radio button. Click Next. Click Finish. Click Close.
From the current view (Layout view), group this report by values in the ProviderName field.
On the Report Layout Tools Design tab, in the Grouping & Totals group, click Group & Sort. Click Add a Group. Click ProviderName .
Add the Name Quick Start to the end of the table
On the Table Tools Fields tab, in the Add & Delete group, click the More Fields button. Scroll to the bottom of the Field Types gallery to the Quick Start section. Click Name.
From the current view (Datasheet view), change the Field Size property for the StaffID field to 7
On the Table Tools Fields tab, in the Properties group, type 7 in the Field Size box and press Enter. Click Yes.
Add the Services table to the Relationships window
Relationship Tools Design tab, Relationships group, show table, double click services, close
Apply the Gallery theme to this report without changing the view
Report Layout Tools Design tab, themes group, themes, gallery
Rename the table Work Logs to: Time Sheets
Right click Work Logs, rename, type Time Sheets, enter
From the current view, change the data type for the LastName field to Short Text
Table Tools Fields tab, formatting group, expand data type list, short text
From Datasheet view, apply the Medium Date format to the IncreaseDate field
Table tools fields tab, formatting group, format drop down, medium date
Add criteria to this query to return only the records where the value in the Premium field is <500 and the value in the SubscriptionType field is Family. Run the query to view the results
Type <500 in the Criteria row in the Premium column. Type Family in the Criteria row in the SubscriptionType column. On the Query Tools Design tab, in the Results group, click the Run button.
Add criteria to this query to return only the records where the value in the InsuranceType field is Dental or Vision. Run the query to view the results.
Type Dental and Vision in the Criteria rows in the InsuranceType column. On the Query Tools Design tab, in the Results group, click the Run button.
The company no longer offers this insurance plan. Delete this record
You clicked the Insurance Tab Header, In the Home Ribbon Tab in the Records Ribbon Group, you clicked the Delete button arrow. In the Delete menu, you clicked the Delete Record menu item. you clicked the Yes button.
Delete the Copy of Staff table
click copy of staff, delete in records group, yes
Add a new record to this form
click the play button with yellow square to the right of it at the bottom left
Go to the first record
click the |< on the bottom left
Use the Simple Query Wizard to create a select query combining fields from two related tables. Include these fields in this order: LastName, FirstName, and MedicalInsurance fields from the Staff table and the Premium field from the InsurancePlans table. Include every record in the results. Select the option to open the query to view information. Name the query Insurance and view the query results
create tab, queries group, query wizard, Inside the New Querydialog, you clicked the OKbutton. Inside the Simple Query Wizarddialog in the Table: InsurancePlansdrop-down, you selected Table: Staff. Inside the Simple Query Wizarddialog from the Available Fieldslist, you double-clicked the LastNamelist item, double-clicked the FirstNamelist item, and double-clicked the MedicalInsurancelistitem. Inside the Simple Query Wizarddialog in the Table: InsurancePlansdrop-down, you selected Table: InsurancePlans. Inside the Simple Query Wizarddialog from the Available Fieldslist, you double-clicked the Premiumlist item. Inside the SimpleQuery Wizard dialog, you clicked the Next >button, clicked the Next >button, typed Insurance in the What title do you want for your query?input, and clicked the Finish button
Create an automatic basic report from the InsurancePlans table
create, reports group, report
Create a new blank database named Payroll
file, new, blank database, type Payroll in name box, create
Create a new database from the Time card template. Do not change the location. Name the database: Time Sheets.
file, new, search time card, click time card, name Time Sheets, create
Preview how the results of this query will look when printed. Change the preview options to show two pages at once
file, print, print preview, zoom group, two pages
Create a backup of the database. You do not need to change the backup location
file, save as, back up database, save as, save
Add the image MedicalInsurance to the form header as a logo. Do not switch views
format layout tools design tab, header/footer group, logo, click MedicalInsurance, ok
From the current view (Layout view), use the Field List pane to add the InsuranceID field from the InsurancePlans table to the form
on the FORM LAYOUT TOOLS DESIGN tab, in the TOOLS GROUP, click the ADD EXISTING FIELDS button/ in the FIELD LIST PANE, click the + in front of the "InsurancePlans". doucle click "InsuranceID"
From the current view, rename the ID field to: PlanID
right click ID, click rename field, type in PlanID, enter
Export the InsuranceOptions query to Excel, maintaining all data formatting and layouts. You do not need to change the location of the saved file or save the export steps
right click the InsuranceOptions query, hover over export, excel, check export data with formatting layout, ok, close
Go to record number 14
type 14 in the bottom left where you navigate to records, enter
Limit the query results to records where the value in the DOB field is greater than or equal to January 1, 1980. Run the query to view the results
type >=1/1/1980 in criteria under DOB, query tools design tab, run
Set the sort order so the results will display records with the highest Premium first. Run the query and review the results
under premium click in the sort field, click the more arrow, select descending, in the home tab, click view, click data sheet view
