Due: 4/25/2016Project 8 Description40 Points

Building a form, querying the database, generating a report

Warning: You must use the Biblio database for completing project 8. Do not use the Northwind database that I use in the project sample and lab discussion. If the Biblio database is not used, I will not grade your submission.

Copy the Biblio.accdb Access database or the Biblio2000.mdb to one of your thumbdrives or save the database to your hard drive or Email the database to yourself. Several Email software packages do not support sending .mdb files, primarily those using Microsoft's Exchange server. Microsoft does not consider its .mdb Access databases safe since it supports Visual Basic for Applications which many hackers use to gain access to your computer. Microsoft's solution to Emailing .mdb files is to rename the file with a different suffix. For example, rename Biblio2000.mdb to Biblio2000.dat and then rename it back to a .mdb file after receiving it.

For your project rename the database with a name consisting of your initials, an underscore, and "Biblio". My database would be named jrn_Biblio.accdb for Access 2007 and later or jrn_Biblio.mdb for Access 2003 and earlier. This database already has a number of tables designed and populated with data.

Use this database to build a form for the existing Publisher table.Use the Form Wizard when creating the form; do not use the Form, Form Design, or Blank Form icons since they will not satisfy the requirements. The form must include all of the fields from the Publisher table except Fax and Name. Include one clipart or photo in an image box in the body of the form, not in the form header, and a rectangle with a fill color around all the address information (includes address type fields plus phone). Please keep the size and resolution of the image to a reasonable size. You must add the title, Publisher Maintenance Form, to your form in the form header section or if one is already present, you should rename it. Increase the text size for this title to at least 14 points. Add a text box in the form footer and insert the current date using the =Now() function in a text box control and place your name in the associated label control. The format for the date in the form footer should give both the date and the time. Give the form the name of PubInfo.

You must also build and save three queries. Give each of the queries below a meaningful name. The queries must satisfy the following criteria.

Query 1 Find all the publishers that are based in Indiana. Do not display the state, zip, fax, or comment fields but display all the other fields. You will need to include the state field in the selected fields in your query statement but you must turn off the Display checkbox. The state field is necessary so you can set the criteria for selecting the desired rows.

Query 2 List the publisher and the count of books that the publisher published in 1992 or 1993. The list should be sorted by count but in descending order. The count of books column header should be renamed to "BookCount". You will have to do a join between the Publishers and Titles tables. You will also have to use the count aggregate function and the "where" aggregate function. There should be only three fields selected from two different tables.

Query 3 List the Title, Author, Year Published, ISBN, and Subject for all the records in the Titles table that contains both 'SQL' and 'Oracle' in the title field. This is a three way join between the Titles, Authors, and Title Author tables. You will need to use the "like" statement along with a wildcard expression to pick up the requested rows for the two required values. Make sure you use the ‘and’ logical operator.

Build a report using the Publisher and Titles tables. Use the Report Wizard to select the fields from the two tables. Include the Company Name from the Publisher table and include Title, Year Published, ISBN, Description, and Subject from the Titles table. Create a grouping by Company Name. Add a group footer line after the detail lines to add summary information for this publisher that displays the number of books published by this publisher by using the Count aggregate function. Make sure that the Company Name from the Publisher appears in the group footer as well as the group header. If you use the Summary option during the Report Wizard prompts, make sure you understand what is being generated for you and whether it satisfies what is being requested. Adjust the layout of the report to make it reasonable and so it fits across a single page. All of the data values must be displayed. This means that you need to increase the height of some of the text fields in the detail records so the information will automatically wrap the text.

Project 8 Rubric
Item / Description / Points
1 / Correct filename / 2
2 / Form has correct 8 fields / 1
3 / Image is in the body of the form / 2
4 / Rectangle drawn around address fields / 1
5 / Solid fill color used for the rectangle / 1
6 / Rectangle sent to back / 1
7 / Added a meaningful Title in the Form header / 1
8 / Your name placed in a label field in form footer / 1
9 / Now() function used in textbox in form footer with correct date format / 2
10 / Form has name of PubInfo / 1
11 / Query 1 - good name for query / 1
12 / Query 1 - only six fields displayed / 1
13 / Query 1 - correct criteria / 1
14 / Query 2 - good name for query / 1
15 / Query 2 - only three fields from two tables / 1
16 / Query 2 - group by, count, where operators used in total line / 3
17 / Query 2 - correct criteria / 1
18 / Query 2 - sort by book count / 1
19 / Query 2 - alias assigned to the book count column / 2
20 / Query 3 - good name for query / 1
21 / Query 3 - correct five fields from three tables / 1
22 / Query 3 - like criteria used in correct field / 2
23 / Query 3 - correct use of wild card characters / 1
24 / Query 3 - correct values in criteria, includes logical operator / 2
25 / Report - good name for report / 1
26 / Report - has six fields from two tables / 1
27 / Report - grouping by company name / 1
28 / Report - summary line for group; must repeat company name in footer / 2
29 / Report - count function used in summary line / 2
30 / Report - fits in the width of a single page / 1
Total / 40

Access Techniques

Building a form using Form WizardI suggest using the lab discussion document for this project.

Query 1AC 78 – 82 and AC 85 – 86

Query 2AC 99 – 102, AC 114 - 118

Query 3AC 83 – 84, AC 91

ReportI suggest using the lab discussion document for this project.

JRNorth1Spring 2016