Microsoft Access NEWEST 1 || All Answers Are Correct
100%.
Use the Simple Query Wizard to create a select query for a single table. Include these fields in
this order: LastName, FirstName, and DOB fields from the Staff table. Select the option to open
the query to view information. Name the query Birthdays and view the query results. correct
answers On the Create tab, in the Queries group, click the Query Wizard button. In the New
Query dialog, Simple Query Wizard is selected by default. Click OK. Expand the Tables/Queries
list and select Table: Staff. Double-click each of the field names in this order: LastName,
FirstName, and DOB. Click Next. Type Birthdays in the What title do you want for your query?
box. Click Finish.
Start a new query in Design view. correct answers On the Create tab, in the Queries group, click
the Query Design 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. correct
answers 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.
Limit the query results to records where the insurance Premium is greater than 250. Run the
query to view the results. correct answers Type >250 in the Criteria row in the Premium column.
On the Query Tools Design tab, in the Results group, click the Run button.
Add criteria to this query to return records where the SubscriptionType field begins with the
letters Self. Run the query. correct answers Type Like "Self*" in the Criteria row in the
SubscriptionType column. On the Query Tools Design tab, in the Results group, click the Run
button.
Modify this query so the InsuranceType field is hidden in the query results, and then run the
query. correct answers 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.
Set the sort order so the results will display records with the highest Premium first. Run the
query and review the results. correct answers Click in the Sort row under the Premium field.
Click the arrow to expand the sort options list, and select Descending. 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 Premium field is
greater than 200 and less than 250. Run the query to view the results. correct answers Type >200
,and <250 in the Criteria row in the Premium 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 Premium field is <500
and the value in the SubscriptionType field is Family. Run the query to view the results. correct
answers 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. correct answers Type Dental in the Criteria
row in the InsuranceType column. Type Vision in the or row in the InsuranceType column
(below the Criteria row). 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 SubscriptionType field
is Self or Family and the value in the Premium field is <200. Run the query to view the results.
correct answers Type Self in the Criteria row in the SubscriptionType column. Type Family in
the or row in the SubscriptionType column. Type <200 in the Criteria row in the Premium
column. Type <200 in the or row in the Premium column. On the Query Tools Design tab, in the
Results group, click the Run button.
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. correct answers Click in the Criteria row in the InsuranceType column and type
[Enter Insurance Type]. Click the Run button. Type dental when prompted. Click OK.
Add a new calculated field named EmployeeCost in the first empty column to the right of the
ProviderName field. The new field should calculate the value in the Premium field multiplied
by .25. Run the query to view the results. correct answers Click in the first empty cell in the Field
row in the query grid to the right of the ProviderName column. On the Query Tools Design tab,
in the Query Setup group, click the Builder button to open the Expression Builder. Double-click
the Premium field and type *.25 to finish the expression. Click OK. Click in the new field and
replace Expr1 with EmployeeCost as the name for the field. On the Query Tools Design tab, in
the Results group, click the Run button.
Display the Total row for this query. correct answers On the Query Tools Design tab, in the
Show/Hide group, click the Totals button.
Modify this query so results are grouped by values in the InsuranceProvider field and values in
the Premium field are summarized with the Sum function. Run the query. correct answers On the
Query Tools Design tab, in the Show/Hide group, click the Totals button. In the Total row, under
Premium, expand the Group by list and select Sum. On the Query Tools Design tab, in the
Results group, click the Run button.
, Add criteria to this summary query to include only records where the value in the
DepartmentName field is Management. Do not include the DepartmentName field in the query
groups. Run the query. correct answers In the Total row, under DepartmentName, expand the
Group by list and select Where. In the Criteria row, under DepartmentName, type Management.
On the Query Tools Design tab, in the Results group, click the Run button.
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. correct answers 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.
Import data from the Departments comma-delimited text file to a new table. Use the first row as
field names. Do not change any field information. Use the DeptID field as the primary key.
Accept the new table Departments. Do not save the import. correct answers On the External Data
tab, in the Import & Link group, click the New Data Source button, select From File and click
Text File. Click the Browse button. Click the Departments file. Click Open. Click OK. Click
Next. Click the First Row Contains Field Names check box. Click Next. Click Next. Click the
Choose my own primary key radio button. Click Next. Click Finish. Click Close.
Import data from the UpdatedBenefitPlans text file and append the records to the Benefits table.
The data fields in the text file are separated by tabs. The first row in the text file is the header
row. Save the import steps. Use the default name suggested by Access. correct answers On the
External Data tab, in the Import & Link group, click the New Data Source button, select From
File, and click Text File. Click the Browse... button. Click UpdatedBenefitPlans, and then click
Open. Click the Append a copy of the records to the table: radio button, and verify that Benefits
is selected. Click OK. Click Next. Click the First Row Contains Field Names check box. Click
Next. Click Finish. Click the Save Import Steps check box. Click the Save Import button.
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. correct answers In the
Navigation Pane, click the InsuranceOptions query once to select it. On the External Data tab, in
the Export group, click the Excel button. Click the Export data with formatting and layout. check
box. Click OK. Click Close.
Export the InsuranceOptions query to a comma-separated text file. Include field names in the
export. You do not need to change the location of the saved file or save the export steps. correct
answers In the Navigation Pane, click the InsuranceOptions query once to select it. On the
External Data tab, in the Export group, click the Text File button. Click OK. Click Next. Click
the Include Field Names on First Row check box. Click Next. Click Finish. Click Close.
Go to the last record. correct answers You clicked the Last Record Button
