computer science - discussion board

1. Parking Lot fees are going up by 15%. Create a query to raise all parking lot fees by 15%.

a. Click on CREATE tab, click on Query Design

b. In the Show Table Dialog click on the 3D Parking Lots table. Click the Add button. Click the Close button.

c. In the DESIGN tab, click on the Update icon in Query Type section.

d. In the 3D Parking Lots Table field list, double click on Semester Fee.

e. Under the Semester Fee field in the Update To: row key in [Semester Fee]*1.15

f. On the DESIGN Tab Click the Run Icon

g. Click Yes in the Microsoft Access Dialog box. You are about to update 12 row(s).

h. You do not need to save the query.

i. You can open up the 3D Parking Lots table and see the new Semester Fee’s.

2. Parking Lot LOT-1A is being repaved and unavailable this semester. Delete all students that have parking in LOT-1A.

a. Click on CREATE tab, click on Query Design

b. In the Show Table Dialog click on the 3D Students table. Click the Add button. Click the Close button.

c. In the DESIGN tab, click on the Delete icon in Query Type section.

d. In the 3D Students Table field list, double click on Lot ID.

e. Under the Lot ID field in the Criteria: row key in LOT-1A

f. On the DESIGN Tab Click the Run Icon

g. Click yes in the Microsoft Access Dialog box. You are about to delete 3 row(s) from the specified table.

h. You do not need to save the query

i. You can open up the 3D Students table and see there are no students parking in LOT-1A.

3. In the Student table add a new field called Status, allow only the values (Freshman, Sophomore, Junior, Senior)

a. Right click on the 3D Students table, select Design View

b. Click in the first blank field (under Lot ID).

c. Key in Status

d. For the Data Type select Lookup wizard…

e. Select I will type in the values that I want. Click Next.

f. In Col1 key in the words freshman, sophomore, junior, senior with each status on a separate row.

g. Click Finish

h. If you want to see the code that was entered you can click the lookup tab.

i. Click on the Save Icon in the quick access toolbar to save the table.

j. To test it out click on Datasheet view. Scroll to the right to find the Status column. Click on a blank status, click on the dropdown and select from the list.

4. The user of the 3D Parking Lots Form wants to be sure they don’t assign students to LOT-4A since it’s for faculty only. Change the background color of the form to red whenever LOT-4A is displayed.

a. Right click on 3D Parking Lots Form and select Design View

b. On the DESIGN tab click on Property Sheet in the Tools section.

c. In the Property Sheet under Selection type: Section click the dropdown and select Form

d. Click the Event Tab

e. Click on the words On Current

f. Click on the 3 dots … button and select Code Builder, click OK

g. Do not move the cursor and key in the below code:

If Me.Lot_ID =”LOT-4A” then

Detail.BackColor = vbRed

Else

Detail.BackColor = vbWhite

End if

h. Click on the red X in the upper right corner to close the code window

i. Click the HOME Tab then click the View button.

j. Use the Record Navigator at the bottom of the form to go through the parking lots. On record 6 the background color should change to red.

k. If you have errors you can try to fix them by going back to design view, click on DESIGN tab then click View Code in the Tools Section.

5. The college is creating a new policy that 3 percent of all parking in a parking lot be designated for handicap. Modify the 3D Parking Lot Report to show in Green Lots that meet the new policy and Red for lots that do not meet the new policy.

a. Right click 3D Parking Lots Report and select Design View

b. On the HOME tab select the dropdown for View and select Layout View

c. Highlight the entire Lot Location column

d. Click on the FORMAT tab.

e. Select Conditional Formatting icon in the Control Formatting section.

f. Click New Rule

g. Change Field value is to Expression is

h. Key in: [# handicap spots]/[capacity]>=0.03

i. Click the Drop down for Font Color and select Green

j. Click on the B icon then click Ok. Click Ok again.

k. Click New Rule

l. Change Field value is to Expression is

m. Key in: [# handicap spots]/[capacity]<0.03

n. Click the Drop down for Font Color and select Red

o. Click on the B icon then click Ok. Click Ok again

p. Click HOME tab then click the View dropdown, select Report View

6. Your need to be able to send the data from the 3D Students table to someone who does not have Access 2013. You decide to send them a text file which can be opened up by any word processor.

a. Highlight the 3D Students Table

b. Click on EXTERNAL DATA tab.

c. In the Export section click on Text File

d. Click the Browse button and select a location to store the file.

e. Click Save to close the dialog box.

f. Make sure none of the Specify export options are selected and press Ok

g. Click on Fixed Width radio button and select Finish

h. Select Close.

i. If you want to look at the exported file you can open it up in notepad.

7. You need to create a simple menu that will allow the user to run forms and reports.

a. Click CREATE tab

b. Click Blank Form

c. Be sure the DESIGN tab is selected.

Add a Button to display a Form, do the following:

d. Click on the Button icon in the Controls, then click in the middle of the Form.

e. The Command Button Wizard should show up. Under Categories select Form Operation.

f. Under Actions select Open Form and click Next

g. Select the 3D Parking Lots Form

h. Select Next

i. Be sure Open the form and show all the records is selected, click Next.

j. Click Text and replace Open Form with Parking Lot Form, select Finish

Add a Button to display a Report, do the following:

k. Click on the Button Icon in Controls, then click in the middle of the Form.

l. Select Report Operation in Categories.

m. Under Actions select Preview Report and click Next

n. Select the 3D Parking Lots Report

o. Select Next

p. Click Text and replace Preview Report with Parking Lots Report, select Finish.

q. You will probably need to resize the buttons so you can see all the text.

r. To test out your menu, click on the HOME tab, select View dropdown then select View Form. Click on a button.

8. Users want a quick way to add faculty members to the parking lot designated for faculty only. Create a Macro and the 3D Parking Lots Form to accomplish this task.

a. Select CREATE tab

b. Select Macro under Macros & Code

c. Under the Add New Action select OpenForm

d. For the Form Name enter: 3D Parking Lots Form

e. In the Where Condition enter: [3D Parking Lots].[Lot ID]="LOT-4A"

f. Select Save and enter the name Faculty Parking Lot

g. Close the macro window.

h. To test out the macro just double click on the macro name Faculty Parking Lot

9. Students are allowed to have one parking permit. Check the Students table to make sure a student doesn’t have more than 1 Parking Permit.

a. Select the CREATE tab

b. Select Query Wizard

c. Select Find Duplicate Query Wizard and click OK

d. Select Table: 3D Students and click Next

e. Add the fields Last Name, First Name, Email to the Duplicate value fields and click Next

f. Add all the remaining fields to the Additional query fields and click Finish.

g. You should see two students that have more than one parking permit.

10. You need to display the 3D Parking Lots Report in a web browser.

a. Double click on 3D Parking Lots Report

b. Click on EXTERNAL DATA tab

c. In the Export section click on the dropdown next to More

d. Select HTML Document

e. Click Browse and select the USB Drive or another location then click Save.

f. Click Ok, click Ok

g. Click Close

h. Open up Internet Explorer and click File

i. Click Open…

j. Click Browse and locate the 3D Parking Lots Report HTML Document. Highlight it and Click Open. And click Ok.