Activity Report

Document Sample
Activity Report Powered By Docstoc
					LSP 121 Activity 5 – Making Reports in Access
Create the following two reports using the report wizard, then go into design mode and modify the layout and texts as appropriate. Submit one Word or RTF document that will be 2 or 3 pages in length. NOTE: Begin by downloading RealEstate database, open the database then open the Listings table. Enter ‘Elmwood Pk.’ as the school district for Listing #17. Close this table. 1. Using the RealEstate database and the Listings table, create a report which includes the following fields in this order: Listing Number, Address, City, Frame Type, Taxes, and School District. Group By school district (this shows up in the wizard). Sort by Taxes in Descending order. For SUMMARY OPTIONS, select Taxes field, then click SUM. Complete the wizard steps to the end. You will see a preview of your Report. Any strings of ‘######’ indicates that the column width will have to be widened to display all digits of numbers. Now you have to check the width of the field boxes in Design View so that no data or numbers or text is chopped off. After each change you make in Design View, switch back to View to see what happens. If it is not right, change again in Design View. Change the format of $ values to currency. Select the field name, right-click and view properties and change format to currency. Change Listing Number label to Listing #. Change the Header to ‘Listings for Real Estate Database 2009’ Remove some extraneous text that appears in the Report output, such as, change Summary for School District = Chicago (#) detail record so the output becomes: Summary for Chicago (1 detail record) How to do this will be demonstrated in class or see the bottom of page 3 (look for the ► symbol). You have to change the ‘formula’ that generates this text. Formulae begin with ‘=’. Be careful you don’t destroy the formula or you may not be able to correct it back to what it was. You will find the concatenate symbol ‘&’ which works like a chain operator for text. If brackets [ ] surround the ‘label’ then the label is a field name. You will also have to reposition the Sum labels in the report (slide to right or left). Align the word Sum closer to the actual sum $. Same for Grand Total, shift it to the right somewhat as in image below. This is an image of what the output should look (try to get as close to this as you can). Note that I removed some of the data for OPRF school district (there are a total of 14 detail records, not 6). Also, your sum for OPRF and the grand total will differ. You can choose your own color and style for the report.


When the output of your report looks good, close the report completely. Right-click on the name of the report and export it as a Word RTF file. RTF is rich text format. Name this file Activity5a.rtf View the file to see if it exported OK. If yes, close this database. 2. Download authors.accdb from class resources web page. In the table, first change the field name ‘Last Name’ to ‘Author’. Save and close the table. Create a report using the Report Wizard. Group by Author, sort by book title, sort by edition and for Summary Options select summary for ‘Sales’ and for ‘Royalty’. Rearrange the layout of the report to match following format: Author Name Book Title Edition Units Sold Royalty Amount

Total Book Sales: $xxxxxxx 2

Total Author Sales: $xxxxxx Total Book Sales is the total royalties of all editions of a particular title. Total Author Sales is the total royalties of all books of a particular author. Change the report header to something more informative than ‘author’ Change the totals (sums) to ‘currency’ format (view properties to do this). You may also have to widen the sums display boxes to show the ‘$’ signs. For example, part of the report should look something like this; notice staggered layout of Totals for author books and author sales (sample author and titles are not all from this database): Author Book Title Edition Units Sold Royalty Amount Smith Fun in College 1st 8320 $14,902.48 2nd 7301 $10,366.92 Summary for Smith (2 detailed records) Total units sold: 15,621 Total Book Sales: $25,269.40 Total Author Sales: $25,269.40

Jones How to Study 1st 2nd 1000 2000 $2,000.00 $3,000.00

Total Book Sales: $5,000.00 How to Get a Job 1st 3000 $4,000.00 nd 2 4000 $5,000.00 Summary for Jones (2 detailed records) Total units sold: 10,000 Total Book Sales: $9,000.00 Total Author Sales: $14,000.00 Total Royalties (All Authors): $39,269.40

Total Units Sold are the books sales for each author (this is a number, not currency).
►This describes how to change Summary for ‘Author’ = Smith … to Summary for Smith: In design view, click on the first row appearing under Last Name Footer. What you need to do is basically cut out everything to the right of the first ‘&’ and to the left of and including the third ‘&’.


In the properties for Data tab, Control Source, click on ... (the 3 dots) This opens the Expression editor Change this: ="Summary for " & "'Author' = " & " " & [Author] & " (" & Count(*) & " " & iif(Count(*)=1,"detail record","detail records") & ")" to this: ="Summary for " & record","detail records") & ")" Notice the gap which shows what was deleted, be careful in doing this. The expression should look exactly like this after editing: ="Summary for " & [Author] & " (" & Count(*) & " " & iif(Count(*)=1,"detail record","detail records") & ")" Click OK and then view the change in the Report [Author] & " (" & Count(*) & " " & iif(Count(*)=1,"detail

Again, when the report looks good, close the preview and close the report, then output the report as a Word RTF file, name it Activity5b.rtf (process described above in #1). Now combine the 2 RTF files into one. Open Activity5a.rtf Go to the end of this document; do this by using Ctrl + End You should be at the end of the text: Page 1 of 1 (possible Page 2 of 2) Insert a page break by clicking on Insert in the menu bar, then click on Page Break. The cursor should now be at the top of a new page. This is where you will insert part 2 RTF file. Now position/resize the Word window so you can see the desktop. Open My Documents and position the window next to the open document. Drag the file icon for Activity5b.rtf (notice 5b) into the proper location of the open RTF file. You should now have 2 or 3 pages. Add your group names (full names) in the header of this document. Double click in the top 1” of the document, enter Activity 5 and names, then close header view by clicking on the red-X box. Save this ‘merged’ document as: Activity 5a_5b.rtf or Activity 5a_5b.docx Submit this using COLWeb for activity 5


Shared By: