My Collection

For this project you will create a generic (and therefore useful) database to store information about a collection of items. The items could be your Pokemon cards, Jeopardy questions, simple task list, grades, video games, favorite websites, etc. In some of the cases you would want to specialize the database, but this should get you started. There will be fields for the item, description, and picture. The first "real" tasks the database will be used for will likely be a database terms dictionary and a treasure hunt. In the meantime, the database will contain only documentation of itself.

Instructions

The project is broken up into four smaller parts in which each of the database components are created. The database will eventually be populated with screen shots of each of these four parts. They are a table, form, query, and report. An example report is provided for reference. It contains screen shots which may help you complete the project. Note that these instructions are for Access 2010. Although earlier version have all the necessary capabilities, the instructions (keystrokes, menus, screen shots) may not match.

Table

Your raw data is stored in a table and that table is stored in a file. In this first part you create the file and then build the table that is stored in it. Later, you add data to the table.

• 1. Start Access. Navigate to the backstage view via the File tab, select New, choose a Blank database from the Available Templates, specify MyCollection as the File Name, choose an appropriate folder for the file, and finally click on Create.

  2. The template arranges for the creation of Table1 in Datasheet View. Stick with that for now. Click to Add a first field. Make it a Text field and call it Item.

  3. Click to Add a second field. Make this one Rich Text and call it Description.

  4. Click one more time to add a third field. This one is an Attachment. For some reason, Access does not allow you to name the field here, but that's OK.

  5. Switch over to Design View using the button on the Fields subtab of the larger Table Tools tab. It is the button on the left that you want. Click on the down arrow to see more choices and select Design View. Alternatively, use the very small buttons on the bottom right of the screen. In order to change views, you must save the table. Give it the name tblCollection. This naming convention will help you distinguish between tables, forms, etc.

  6. Now in Design View click on the Data Type of Item. It should be showing Text. In the General tab in the bottom half of the pane find the entry for Required, which is set to No. Change the setting to Yes. You will not allow a record that does not have an Item specified.

  7. Just below Required you should see Allow Zero Length. Change that to No. Databases distinguish between having no value and having a blank value. You don't want either case to be allowed.

  8. The Description field is good, but the third Field1 needs a better name. Click on the name and change it to Picture.

  9. It is fairly obvious what these fields are for, but that won't always be the case. Practice documenting your table by filling in the Description column in the upper pane. Write something for each similar to "Name of the item," "A more detailed explanation of the item," and "A graphic representation of the item.". Your table is now complete and you can close the window. Be sure to save your changes if you are asked.

Form

It is certainly possible to enter data into a table in a Datasheet view, but that doesn't work especially well with either Rich Text or an Attachment. In this part you will make a simple form that facilitates both data entry and browsing your collection.

• 1. Change to the Create tab and click on Form Wizard. You only have one Table and no Query, so the first question the wizard asks should be filled in properly. In the Available Fields column select Item and then press > so that it is transferred to the Selected Fields column. Do the same with Description and Picture and then press Next >.

  2. The Columnar layout will probably work best, so just click Next >. Give your form the title frmCollection. Again, this is to help you distinguish the various types of objects in Access. Have Access Open the form this time so that you can see what it looks like before you modify it. Click Finish.

  3. The form looks reasonable, but it definitely needs a different title and the Item will not require more than one line of text, so that user interface element can be simplified. To do this, first change to Design View using the Design subtab of the Form Design Tools tab or one of the buttons in the lower right corner. Click on frmCollection and change the text to My Collection.

  4. Click on the wide box where the text for Item will be entered. In the Property Sheet, find the entry for Scroll Bars in the All tab. Change that entry to None so that they don't appear.

  5. Further down in the list find Enter Key Behavior and change it from New Line in Field to Default. This will (help) prevent entries from spanning lines of text.

  6. If only one line of text will be allowed, the text box does not have to be so tall. It should match the one line to the left of it, which is a Label with the Caption Item. That label is 0.25" tall. Make the Text Box that you were just configuring the same height. You can do this by clicking and then grabbing the handles of the orange box that appears. Drag one edge of the box until this box is as tall as the one beside it. It will snap to the grid marks to help you.

  7. Now the spacing is messed up. There is more room between the Item and Description than there is between Description and Picture. Select the two Description controls and the two Picture controls and move them all up until the Description Text Box is two dots below the Item Text Box.

  8. To get more of a taste of how you can format a form, right click on the background of the Detail area. On the menu that appears select Fill/Back Color and choose your favorite.

  9. Now the Picture that will be attached has a background color different from the white background of the other two controls. Right click on the picture's background and give that a white Fill/Back Color so that everything matches again.

  10. Click Date and Time to add a timestamp to the header. There is much more opportunity for customization, but hold off until later.

  11. Change to Form View and enter values for Item and Description. There isn't an obvious way to submit the form. Navigation keys on the very bottom will help with this, but they aren't obvious. You'll add a button to Save Record. Change back to the Design View and find a Button among the Controls in the ribbon. Click on it, then click in the Detail area of the window to draw the button. A Command Button Wizard will appear. Under Categories choose Record Operations and under Actions choose Save Record. Press Next >. Click on the Radio Button next to Text and press Finish. This button can be used to save your record.

  12. In almost the exact same way, create a second button to Add Record.

  13. The form should now be working. You'll need at least one record in the database in order to test out the report created in the next section. Switch to Form View and fill in the blanks of your form. In order to attach a picture, you need to double click the Attachment control and complete the Attachments dialog. Attach a picture so that you can see how it looks in the report.

Report

You now have at least two ways to enter data: in the table's Datasheet View or in the Form. Both methods are also able to display the data. The Datasheet doesn't have an especially good way to display the picture. The form only shows one record at a time. One can get both the picture and multiple records by creating a report. Once the report has been programmed and generated, you also have a copy of your data that is independent of Access, which can also be beneficial.

• 1. In this case you will also let a wizard, the Report Wizard, get you started. Find the wizard among the Report options of the Create tab's ribbon and click it. Again there is only one Tables/Queries, so it should be preselected. From the Available Fields select one at a time and for each click the > button to move to Selected Fields the Item, Description, and Picture. Press Next >.

  2. This project does not require any grouping, so just press Next > on the Report Wizard's second step. No ordering is necessary, so Next > works for the next step as well. Try Justified as the Layout and press Next >.

  3. Entitle the report rptCollection so that the name identifies the type again and click Finish to Preview the report. Picture probably shows up smaller than desired, so that is one of the changes that will be made to the report. The title should also be made more suitable for humans and the item is now taller than it needs to be. Click on Close Print Preview and navigate to Design View if you aren't there already.

  4. Click on rptCollection and change the text to My Collection.

  5. Make the Text Box for Item 0.25" high using the Property Sheet.

  6. The other controls should move up to fill in the empty space created. Click on the Label for Description and then Control-click on the label for Picture so that both are selected. With the mouse, move them all up to near the border with the Item Text Box.

  7. To avoid having duplicate borders, select the Item Text Box and then set the Border Style to Transparent.

  8. Make more room for Picture by extending the Attachment Control to the 4" mark.

  9. The Detail portion of the report includes a grey Rectangle. Expand that to 4" as well.

  10. Drag the Page Footer up or down to match. Check your work in Report View mode and save your report.

Query

This report tells you nearly everything about every single entry in your table except for the unique, database assigned ID of each record. Of course, if some part of the record, the description or picture, is missing, it doesn't appear on the report. It wouldn't be very efficient to create an entire report in order to determine that something wasn't there. Instead, make a quick query that answers the same question. Run it to see which item has not yet been defined or pictured. This allows you to populate the database with a required item value, but delay entry of the other fields. You can easily go back later and check for blanks.

• 1. Before you write the query, add some records that they query will find. Double click on frmCollection. You should see the first entry you made. Click on the Add Record button and add No Nothing for the item. Leave the description and picture blank. Press the Add Record button to save the current record and start another.

  2. The next Item should be called no Picture. Add a Description such as Description, but leave the Picture alone. Again press Add Record to save the pictureless record and start new one.

  3. Now make a descriptionless record called No Description. Leave the Description blank and attach a picture. Click Save Record. You should now have four records with every combination of having a Description and Picture or not. Verify this by checking tblCollection in Datasheet View.

  4. On the Create tab, find and click on Query Design. A Show Table dialog appears asking whether to display the one and only table in the database. Click Add so that it appears. Before you forget, press Ctrl+S to save the query and name it qryCollection. If the database was going to contain any other queries, it would need a better name, but it will do for now.

  5. You should be in Design View. In the first column select tblCollection.* for Field so that you see everything. Show should be checked.

  6. In the next column specify Description for the field.

  7. Uncheck the show box. You don't need to display another copy of the value.

  8. For the Criteria cell enter Is Null. This checks for a missing value. Run the query by clicking the Run button. You will be switched to Datasheet View and should see two records returned: No Nothing and No Description. Return to Design View.

  9. In the next column specify Picture as the Field. Uncheck the show box.

  10. In the Criteria cell again add Is Null. Run the query again. Now you should only see No Nothing in the results.

Data

Your database has been built and you have even populated it with several records used in testing it. Continue in this recursive manner to document your work. Add records that show what you have done. If that works, then your database is ready for more serious tasks.

• 1. Double click on frmCollection and click the Add Record button so that you don't overwrite any other records. For the Item write frmItem.

  2. For the description write something like "This form is used to create and update records".

  3. Now take a screen shot of the form. The easiest way to do this might be with the keystroke Alt+PrintScreen or Alt+PrtSc. Open up Paint. Paste the picture into the program with Ctrl+V. Save the picture as frmCollection.png.

  4. Add the picture you just made to the record. Double click the attachment control to get the Attachments dialog, click Add..., track down the picture and click Open and then OK. You should see a picture of exactly what you are working on now. Finally, press Add Record to move on.

  5. Double click tblCollection and change to Design View. You may have to do this in read-only mode. Take a screen shot of this and save it as tblCollection with Paint. Return to the form and enter tblCollection for Item, "This is the table that stores the data" for Description, and tblCollection.png for Picture. Press Add Record again.

  6. Document the query now by double clicking on qryCollection, changing to Design View, taking a screen shot, and saving it in Paint as qryCollection.png. Call the Item qryCollection, Description "This shows how a query is designed", and Picture qryCollection.png. Press Add Record.

  7. Last is the report. If you double click on it, you should see a substantial report having been generated. Change to Design View and take a screen shot called rptCollection.png. Add one last record for rptCollection, "This is how the report was designed" and the graphic file attached. Save this record.

  8. Last, but not least, report on your work. Find the External Data tab. There you will find an Export group with a PDF or XPS button. Click on that and a dialog appears. Name your PDF file MyCollection and press Publish. You now have a PDF version of your database and it is ready for prime time.

Delivery

You will need to submit your database, MyCollection.accdb, along with the PDF report, MyCollection.pdf. They may be attached to a single email or transferred directly with a USB drive. The large file size involved may make the USB option more convenient.

Grading

Again points will simply be awarded one per instruction followed. Below is a very abbreviated summary of each instruction that can be used for the grading sheet that is produced with Excel.

• 1. Table

     1. 1. Specify MyCollection as the File Name.

        2. Make a Text field and call it Item.

        3. Make a one Rich Text field and call it Description.

        4. Add a third field, an Attachment.

        5. Give the table the name tblCollection.

        6. Change the required setting of Item to Yes.

        7. Change Allow Zero Length to No.

        8. Change the third field's name to Picture.

        9. Fill in the Description column.

  2. Form

• • • 1. Add Item, Description, and Picture to the Selected Fields.

      2. Give your form the title frmCollection.

      3. Change frmCollection text to My Collection.

      4. Remove scroll bars from Item.

      5. Change Enter Key Behavior to Default.

      6. Make the Text Box 0.25" tall.

      7. Move controls up so Description is just under Item.

      8. Color the Detail area.

      9. Color the other backgrounds.

      10. Add a timestamp to the header.

      11. Add a Save Record button.

      12. Create an Add Record button.

      13. Add a record to the database.

• 3. Report

• • • 1. Move Item, Description, and Picture to Selected Fields.

      2. Try Justified as the Layout.

      3. Entitle the report rptCollection.

      4. Change the text to My Collection.

      5. Make the Text Box for Item 0.25" high.

      6. The other controls should move up.

      7. Set the Border Style of Item to Transparent.

      8. Extend Attachment to 4".

      9. Expand the Rectangle to 4".

      10. Drag the Page Footer up or down to match.

• 4. Query

• • • 1. Add a No Nothing record.

      2. Add a no Picture record.

      3. Add a record called No Description.

      4. Create a query named qryCollection.

      5. Select tblCollection.* and check show.

      6. Specify Description for the field.

      7. Uncheck the show box.

      8. For the Criteria cell enter Is Null.

      9. Specify Picture and uncheck the show box.

      10. In the Criteria cell again add Is Null.

• 5. Data

• • • 1. For the Item write frmItem.

      2. Write "This form is used to..."

      3. Now take a screen shot of the form.

      4. Add the picture you just made.

      5. Enter tblCollection, a description, and tblCollection.png.

      6. Call the third Item qryCollection.

      7. Add rptCollection.

      8. Name your PDF file MyCollection.

• 6. Delivery

• • • 1. Submit MyCollection.accdb with MyCollection.pdf.

      2. Attach them to a single email.