Will send you screenshots of assignment once handshake is made.

Advanced Reports

Week 13 – Assignment

This week you are to create two reports that use many of the features discussed this week . Feel free to make it your own by being creative and exploring the many tools in the report screen. Notice that there are some things that are better done in the Layout View than in the Design View of the report. The first sample report appears below. You can use the database from the recent past assignments to build the report. This will save you time and allow you to concentrate on the report and not the relationships etc. You will have to add some more orders, and from a variety of states in order to produce at least one full page report that looks realistic and professional. Be sure to include a customized font and size, the State grouping, the customized Totals “titles”, currency formatting and the horizontal line below the state grouping.

For the second report, you will need a different set of tables. Instead of creating a whole new database, you should seize this opportunity to use all your skills to “massage” the current database and “morph” it into the one you need. Below is a sample of the second report for a Car Rental Agency:

You would use the same Customer table and just add a Rentals table to “play the role of” the Orders table, and add a Cars4Rent table to do the job of the Inventory table. The two new tables will look like this:

Use the Relationship window to set a new relationship for these three tables while maintaining the old relationship structure.

When you use the Report wizard, choose Grouping on Cartype and on the next screen, select Summary Options and check the Price field. You can also choose to sort on State.

Modify the report, experimenting with Layout View and Design View to add a new column (calculated field) for # of Days (Datein – Dateout) as well as Total (# of Days * Price). Be sure to use the Property sheet to set the names and titles of these new fields/columns. Explore the Name vs. Caption on the Text Box labels, and Name vs. Control Source on the calculated controls.

There is one issue that we may have to discuss on the board and that is the =SUM(). Currently, the SubTotal will be summing just the price column and not the # of Days * Price. So, assuming you named the calculated field for Price * Days, Total, It seems from the readings that it would be OK to change the formula that is created by the wizard from =SUM([Price]) to =SUM([Total]) but using the SUM on calculated fields is not allowed. So it will have to read =SUM(Price * Days), in other words, just repeat the formula in longhand. However this will still cause an error as Days is also a calculated field so you will have to spell that out too! =SUM([Price] * ([datein] – [dateout]))

I’m sure we will be chatting about this some more.

As usual, change the name of the database to your name before uploading and submitting.

Submit your work by using the Assignment Drop-Box tool.

Be sure to use the discussion board if you need clarification on any topic.