# Exercises

Purpose:
Create a data entry form and enter new data


Purpose:
List students living on campus; narrow the list to students under the age of 21.


Purpose:
Retrieve student transaction data using related tables.


Purpose:
Put your database in the UD Dropbox so you can retrieve it when you return to your desk.


Purpose:
Become familiar with the Access 2007 interface


Purpose:
Enable all content and add database location to a trusted location.


Purpose:
Preview and export the Access Basics Exercises report.


Purpose:
Create a database to store student information

Notes:
The current database will close while you create a new database.  This documentation form will therefore not be available.


Purpose:
Set up relationships among the three new tables.


# ExerciseSteps

Description:
On the Ribbon,  click the External Data  tab. 


In the Import group,   click the Excel icon.


Description:
The Get External Data window opens. 


- “Specify the source of the data”:    Browse to the location of the file, select  StudentDemographics.xlsx, and click Open . 


 


- “Specify how and where …..”:     Select Import the source data into a new table…”    and click OK. 


 


The Import Spreadsheet Wizard opens.     


- Verify the data on sheet1.   Click Next  


- Put a checkmark next to “First Row Contains Column Headings”.    Click Next.  


- Allow Access to assign data type to the fields.  Click Next.  


- Select “Choose my own primary key”,  then select StuID as the primary key from the dropdown list.  Click Next. 


- In the Import to Table box,  type StudentDemographics  for this table.   Click Finish.  


In the Save Import Steps window,  Click Close.


Description:
Repeat steps a and b to import the data in StudentTransactions.xlsx.


Description:
On the Ribbon,  click the Create tab.


 


In the Tables group,   click Table Design.


Table1 opens in Design View  and  a contextual toolbar appears in the ribbon.   


 


You must specify field names and their data type.  You may give each field a description.


At the bottom of Design view is the Field Properties section where you can modify the properties of each field.


Description:
Create the following fields, assign data type, set field size, and create an input mask for the HomeZip and HomePhone fields. 


 


Field Name  :   Data Type   :    Description   


ID   :  AutoNumber:     


StuID   :  Text   :  limit to 6 characters    


HomeStreet :  Text  :  


HomeCity  : Text   :


HomeState  : Text  : limit to 2 characters   


HomeZip  : Text  :  Set input mask  


- In the Field Properties panel, click the Build button (...) 


- Save the table when prompted:


    -  Name the table:  Emergency Contact.    Click  Ok


    -  There is no primary key defined. Do you want to create a primary key now.    Click Yes


    -  In the Input Mask wizard, select "Zip Code".      Click Finish.


HomePhone :  Text   :  Set input mask


 - Follow the same steps as for HomeZip and select "Phone Number" in the input mask wizard.   


Mother  : Text    :


Father  : Text    :


Alumni :  Yes/No   :


Donation : Currency  :


Description:
Double click to open the EmergencyContacts  table.  


Click the Create tab.  


In the Forms group,  click Form.   


 


A simple form has been created for data entry.


Description:
Click the Office Button.  


- Select Manage. 


- Click   Back Up Database.


Description:
Access gives the file a default name of filename_today’s-date (where filename is the name of the current database file).   


 


Give the file another name if you like and  save the backup to the desktop.  (Saved files will be placed in the My Documents folder by default - in this exercise, we change that location.) 


 


Click Save to back up the database.  


 


The database backup file works exactly the same way as the original database, so if you ever have to use it, open it as you would any other database file.


Description:
Click on the Create tab. 


 


In the "Other" group, choose Query Design.


Description:
The ShowTable window appears.  


 


Select "StudentDemographics". 


 


Click Add, then click Close.


Description:
A list of fields in the table is displayed. 


 


Double-click on 


  - LastName  


  - FirstName


  - DOBfromSIS


  - City


  - StreetOrDorm


Description:
In the City column, In the Criteria box, type:  


    On Campus


 


Click in another box in the grid to make sure the syntax is correct.  


 


Click on the view button to see datasheet view. 


 


Then click on it again to return to design view.


 


In the design grid, in the City column, uncheck the "Show" box.  


 


Click on the view button to see datasheet view. 


 


Then click on it again to return to design view.


Description:
In Design View, choose the column "DOBfromSIS". 


 


In the Criteria row, type:     > "19880520" 


   (or change the date to current year minus 21, current month, and day, as in yyyymmdd)


 


In the FirstName column, choose "Ascending" from the Sort Row. 


 


Repeat for LastName.  


 


Save query as MinorsOnCampus.  


 


On the Design Tab, in the Results group, choose View.


Description:
From the Database Tools tab, choose Relationships in the Show/Hide group.  


 


Verify that the StudentDemographics table is related to the StudentTransactions table.


Description:
From the Create tab, choose Query Design from the Other group.


Description:
In the ShowTable window, choose StudentTransactions and click Add. 


 


Choose StudentDemographics and click Add. 


 


Click Close.


Description:
Click and drag the following fields to the Design grid:  


 


   LastName from the StudentDemographics table 


   FirstName from the StudentDemographics table 


   TranType from the from the StudentTransactions table 


   TranDate from the from the StudentTransactions table 


   TranAmt from the from the StudentTransactions table


Description:
In the Design Grid, find the TranType column.  


 


In the Criteria row, type:      HealthSvcs


 


Click on another box in the grid to check syntax.  


 


Click on the View button to see the results.


 


Click on the View button again to return to design view.


 


In the Criteria row, enter 8/13/2008 under TranDate.


 


Click on another box in the grid to check syntax.  


 


Click on the View button to see the results.


 


Click on the View button again to return to design view.


Description:
Highlight all the text in this box, and choose Copy from the Clipboard group on the Home tab.


  


Open a blank document in Word 2007 and choose Paste from the Clipboard group on the Home tab.  Save the Word document as "Drop Box Directions.docx". 


 


[Print/copy to e-mail?]


 


Close the Access Database. 


 - Make note of where you saved the database file.   


 


Find the Dropbox at http://www.udel.edu/dropbox.  


 - Log in with your UDelNet ID.  


 


 - Click Drop-off. 


 


 - You will see a window with your personal information filled in.   


 


 --- Complete the recipient boxes with your name and UD e-mail address.   


 


 --- In box 3, browse to the location of your database file and select it.  The pathname to the file should appear in the box. You may put a description in the description window if you choose ("Class Assignment DB").  


 


 --- Click on the button that is labeled "Drop-off the File(s)".  


 


 - Log out of this page, and close the browser window.


 


Return to your desk after class.   


 


Go to the Dropbox page (http://www.udel.edu/dropbox) and log in again.  


 


 - Click on "Drop-offs for Me".  You will see your Access Database listed with the description you entered when you dropped it off.  


 


 - Click on the filename to download the file to your computer.  


 


Log out.   


 


For more information, see http://www.udel.edu/topics/dropbox/.


Description:
Click Office Button and select Save.   The "Save As" window opens.   


 


Type:  EmergencyContactsForm.  


 


Click OK


Description:
Open Microsoft Access 2007 (Applications folder or Start/Programs/MS Office 2007/MS Access)


Description:
Click the Options... button next to the security warning.


Description:
Select the Exercises report and do one of the following:


  - double-click to open it and choose Print, Print Preview from the Office button; or


  - right-click and choose Print Preview from the drop-down menu


 In the Print Preview window, page through the report or use the More Pages button to view several pages at a time


 Close Print Preview.


Description:
In the right-hand panel, click Open and browse to the Desktop.  


Select AccessBasicsTrainingDocs.accdb and double-click to open or click Open.


Description:
In the open database window, locate the following:


  - the Office button


  - the Quick Access toolbar, with Save, Undo, and Redo buttons


  - the Access ribbon, with Home, Create, External Data, and Database Tools tabs. 


  - a security warning immediately below the ribbon


  - the Navigation Pane, with a list of all objects in this database (two tables, a form and subform, and a report with subreport)   Note:  Each object you open will have its own tab in the document window.


Description:
Use the drop arrow on the Quick Access toolbar to hide the ribbon and restore it.


Description:
Use the double arrow on the Navigation Pane header to minimize the Navigation Pane and restore it.


Description:
In the Security Options dialogue, select "Enable this content" and click "Open the Trust Center."


 Click on Trusted Locations in the left pane.


 Click Add new location... in the right-hand pane.


 Browse to the desktop and check "Subfolders of this location are also trusted." Click OK three times to close the Trust Center and save the security settings.


Description:
With the report highlighted, on the External Data tab click on Word in the Export group.


 In the Export - RTF file wizard:


  If the report destination shown is not the Desktop, use the Browse button to browse to the Desktop and if desired change the report name.


  Click OK and Close when done.


Description:
Open the .rtf file from the desktop in Word.  You may close the Word document or keep it open as a reference.


Description:
In the center panel, click on Blank Database.


Description:
In the right-hand panel, type the database name in the File Name box (e.g., TestDatabase.accdb).


Description:
Click on the folder icon to browse for a location other than the default. 


Choose Desktop in the File New Database window and click OK.


Description:
Click Create.


 The new database opens with a new empty table, Table1, with a single column (ID).


Description:
Click the drop-down arrow next to Tables in the Navigation Pane and choose Object Type and if necessary, All Access Objects.


Description:
Close Table1 using the close button in its upper right-hand corner (not in the Access window).  Do not save if prompted.


Description:
Click the Database Tools tab and choose Relationships in the Show/Hide group.


Description:
Click Show Table in the Relationships Tools Design group.


Select the three tables you created (Student Demographics, StudentTransactions, and EmergencyContacts) in the Show Table dialogue box.  Use the Shift or Ctrl keys to select more than one table at a time.


Click Add to add the tables to the Relationships window.


Click Close to close the Show Table dialogue box.


Description:
Create the relationship between StudentDemographics and EmergencyContacts:


Drag from StuID in StudentDemographics to StuID in EmergencyContacts (primary key in both tables).


In the Edit Relationships window, check to make sure you are relating the right fields.


Check "Enforce referential integrity" and click Create.


Description:
The join line shows that this is a (possible) one-to-many relationship.


Description:
Repeat steps c and d to create the one-to-many relationship between StudentDemographics and StudentTransactions (StuID is a foreign key, not the primary key, in StudentTransactions).


Description:
Save the Relationships layout (Save from the Quick Access toolbar) and close the Relationships window.


Description:
Enter the following data into the form in the correct fields.  


Press tab to move through the   fields.


 


a10007 :  123 Maple Street :  New Haven  : CT :  06514 :  Mary  : Daniel :  no :  0 


a10069  : 456 Main Street :  Atlantic City :  NJ :  08401  : Jane  : John  : no :  0 


a10319 :  98 Sugar Lane :  Bear :  DE :  19701 :  Mildred :  Paul  : yes :  1000 


a10465  : 78 Elm Street :  Wilmington :  DE :  19808 :  Virginia :  Andrew  : no  : 0 


a10999 :  14 Mission Ave  : Millville  :  NJ :  08332 :  Jeanette   : George  : no :  0


 


(Note:  Be sure to use the StuIDs listed here.  All other data can be different.) 


 


Close the form when finished.  Open the EmergencyContacts table to view the data and close the table.


Description:
On the ribbon,  click View,  select Datasheet view


 


The table is ready for data.


Description:
Create a report based on the query MinorsOnCampus.


Description:
Select the query, click on the Create tab and then on Report in the Reports group.


Description:
The report opens in layout view.  Use the View button dropdown to change to Print Preview.


Description:
Choose two or four pages from the Zoom group:  Notice that the report is too wide for the page.


Description:
Return to layout view and adjust the column and margin widths until the data fits.  Check your changes in Print Preview.


