Week 6: August 4-8

TUES   THURS   TO DO

WEEK'S OBJECTIVE

MS Access 2013 Reports. Book: Lesson 5. On-Your-Own Tasks: Reports 1, Reports 2.

MIC ASSIGN 6, due Sunday August 10.

Tuesday

GAMEPLAN

  • Reports
  • Report Wizards
  • Report Layout
  • Report Print Preview
  • Reports Design
  • Report Properties
  • Controls
  • Subreports

DISCUSSION

Reports

Reports allow you to to view, format, and summarize data from Access databases. Reports provide a way to distribute or archive snapshots of your data, either by being printed out, converted to PDF or XPS files, or exported to other file formats.

  • Reports can include items of information selected from multiple tables and queries.
  • Reports are used to display data in a format that can be more easily understood than the table (spreadsheet) view.
  • Aggregated data (summarized) is usually put into reports for easier viewing.
  • Organize data to make it attractive and informative, on a printed page or on a computer.
  • You can use reports for mailing labels, an invoice, produce a directory, offer data summary, show a project status, etc. It depends on your database.
Report Views
  • Report View: How the report looks. Unlike a form, a Report does not allow searching, adding,or pulling up specific records. The report design is not editable from here.
  • Layout View: The layout of the report, where the design/layout is minimally editable.
  • Design View: The design of the report, where the layout of everything is editable.
  • Print Preview: In this view, you see your report exactly as it will look when printed.
Report Creation: Wizards

Access has basic Report Wizards for you to create simple reports. You can then use the Design View to edit the report's appearance and to add additional items, like controls.

  • All-Fields Report: You can easily create an All-Fields report by simply selecting a table or query in your Navigation Pane, then clicking CREATE/Report. A simple report will simply open, and be saved, with the same name as the table or query you selected.
  • Single Table Report: If you want a simple report but want to choose only some of the columns of a table, Choose CREATE/Report Wizard, which will let you create a basic report using Wizard questions and column choices.
  • Multiple Table Report: You would actually create a report from a Query that already has grabbed data from more than one table. However, like Forms, you can also create reports from more than one table.
  • Reports in the Reports Wizard can be reformatted with 3 different views: Stepped, Block, and Outline.
Report Editing: Design View

You might create a simple report that has all the columns you want in it, but you also might want to change the spacing, font colors, and other things. Like with forms, you can do this, using the Report Design View.

  • You can select inside a Control item in Design View and edit it directly, such as selecting a label and changing the font color or type from the Home Ribbon.
  • You can select a Control item and change its size, and position.
  • You can see the reports header and footer regions. There are both Report headers and footers, and Page headers and footers, since reports are often multi-page. They provide cohesive information linking the pages together.
    • The Report header and Report footer are the single header for the whole report (appears once) and single footer for the report. They print out only once. The footer is where you would put any totals, for instance.
    • The Page header and Page footer show up on every page that is printed, so these would be where the column heads (labels) for report data would go, the page numbers, etc.
Report Properties Sheet

This panel is available in both Layout and Design view, and is the primary place where you make Control behavior adjustments to controls in a report.

  • You can edit the behavior and criteria of Controls in reports and subreports here.
  • The properties you set or change using the Report Properties Sheet only affects the report, NOT the properties of the bound table.
  • You can make format changes, such as borders, background colors, etc. in the Format tab.
  • You can add specific data formatting and validation rules in the Data tab.
  • The Event tab (advanced) lets you add actions and special code to create behaviors of controls.
Reports Design View

This is the view in which you can edit the design of a report you create. You can also create reports from scratch with this view. You can:

  • Use rulers and a grid to help with positioning.
  • Add/edit Header and Footer items here.
  • Adjust the size of sections.
  • Apply a theme.
  • Change the size of controls.
  • Arrange controls logically to make data entry easier.
  • Adjust the properties of report elements in the Property Sheet.
  • Add fields from the Field List.
  • Add controls to limit data entry choices or add functionality to a report.
  • Set the tab order of the report fields for easier usage.
Controls

Like in Forms, you use controls in a report to enter new information, to edit or remove existing information, or to locate information

  • Some controls are Bound: linked to a field in a table or the datasheet created by a
    query.
  • Some controls are Unbound, or not linked to any specific table/query and its data.
  • There is also a Calculated control, which displays the results of an expression.
  • Text box controls: You can view or enter information in these controls. Bound
  • Label controls: These are heading lables, which tell you the type of information you are looking at in the corresponding text box control. Bound
  • Images: You can embed an image on your report, like a logo. Unbound
  • Tabs and Page Breaks: Unbound
  • There are the other controls (also used in Forms), like frames, checkboxes, radio buttons, etc., that you can research on your own. You might use a multi-select list box to select several items at once, and open a report limited to those items.
Calculated Controls

This is a control that lets you make calculations from data to produce temporary results (like sums, averages, etc. in a report), leaving the database otherwise unchanged. They:

  • Work by using  Expressions (formulas).
  • An expression combines arithmetic operators, fields, prebuilt formulas, and values to produce a sum.
  • An expression can use data from a table, a query, or another control.
  • To create an expression, you would select the control, click the Properties button, and select the Data tab.
  • Beside the Control Source property box, you would see three dots. Clicking those opens the Expression Builder dialog box.
Subreports

A subreport is a report that is contained within another report. You would use this to:

  • View data from multiple tables or queries on the same report (that is being used for input).
  • Display data from multiple tables where there is a one-to-many relationship, like if you wanted to display an order with the order details.
  • To create a main report and subreport that include only some of the fields in their underlying tables, use the Report wizard, which offers a "Report With Subreport(s)" option.

DEMOS

We'll do some simple report creation and editing.
  • Open theGardenCompanyReports.accdb database.
  • We'll use the Report button to create a report from a table selected in the Navigation Panel - try the Employees table.
  • We'll use the Wizard to create a basic report from a single table - Try the Shippers table.
  • We'll use the Report Wizard to create a basic two-table form with selected columns, much the way we do queries and forms. (Products) ProductID, ProductName, LatinName, and (Categories) CategoryName, Description.
    • NOTE: Unlike Forms, we do NOT need to add the CategoryID (necessary "Join Key") because with reports, we are reporting out, NOT adding new info into tables.
    • View by Product, Group by ProductName, Sort by ProductID, choose Stepped, choose Landscape, and name Products/Categories Report.
    • See in the Print Preview that some labels look cut off, and some things look misaligned? Same in Report View.
    • Layout View, choose ProductName label, and in the Design/Grouping & Totals, choose Group & Sort. Change the sort to "with Z on top", then change back.
  • We'll look at some changes that can be made in the Properties Sheet.
  • We'll change the theme MS Theme, colors, fonts.
  • We'll try making a change in a table / query the report is an output of and see if the data changes in the report.
    • Note in the report that Herbs description shows "For flavoring and fragrance". Close the report.
    • Open the Categories Table, and in the Herbs Description, change it to read "For flavoring, fragrance, and health". Close the table.
    • Re-open the Products/Categories Report. See if the Herbs description changed.
We'll do some simple report editing in Design View.
  • Stay in the GardenCompanyReports.accdb database.
  • Create a Simple Query. Table:Customers - FirstName, LastName, City, & Region and name the query Customers in BC Query. Open in Design View, and change criteria for Region to "BC".
  • Close the Customers in BC Query, but keep it selected. Then use the ReportWizard - Choose all but the City. Group by Region, LastName Ascending, and choose a Portait Block report. Name it Customers in BC Report.
  • The Report will open in Print Preview.
  • Look at the Print Preview of Customers NY. This shows how the report will paginate on the paper/margins you choose. Close Print Preview.
  • Look at the Report View of Customers NY. Note that this is view only, no input. You can see the whole report by scrolling down.
  • Look at the Layout View of Customers BC. Resize the Input fields.
    • In the Design tab, change the theme, then change the font palette; you can also add a sort to the first name, if you choose.
    • The Arrange tab allows for choosing layouts and adding/deleting rows and columns. This is for complex reports (lots of rows/columns) where you really want to redesign.
    • In the Format tab, you can select the input fields, add images, change the font size, etc.
    • In the Page Setup tab, you can make changes to how the report will print out.
    • Look at the Property Sheet, and seclect a couple of input fields to see the "format" of the text, etc. Looks at Properties/Event, and see the Tab Index numbers. Most stuff can stay at default. Choose the LastName input field, and change the font in the Properties tab to Navy bold and border-width: 2pt.
  • Look at the Design View of Customers in BC Report. This is much more detailed for designing reports. See how the controls and field names are placeholders for the data that will populate the report in Report view.
    • Move the Page Header and Page Footer bounding areas down to see more of the space.
    • Right-click the Design grid to choose to also show the Report Header and Report Footer areas.
    • In the Header, rename Customers BC to Our BC Customers, and resize, change color and boldness, etc. Then insert an IMAGE. These are Unbound.
    • In the Header, use Design and the control Label to create a label called Region.
    • In the Footer, use the Design tab to add Page Numbers, choose Page N of M and Bottom of Page, Right. Then, in the Design View, set to 9 point and italic.
    • Then, resize the width of the Region Label and Data Field to be narrower.
    • In the Body/Detail section, use Add Existing Fields to add City. Try to move the city label to the Header area - you cannot. Then, delete only the City Label. Move the City field to the right of the Region field.
    • In the Body/Detail section, select all of the Controls, and change the background and line color. Also, in the Arrange tab, choose Size/Space, Arrange to Fit. Notice how they all remain anchored to the top left of each.

Let's do a calculated field in a report.

  • First, make sure that Products and OrderDetails have a 1-to-many relationship.
  • Choose Report Wizard, (Products) ProductName, (OrderDetails) OrderID, Unit Price, Quantity. Show by Order Details, Group by OrderID, No sorts, NO Summary Options, choose Outline & Portrait, name the report Products and Orders Totals Report.
  • In Design view, choose the Unit Price field, then choose Design tab/Grouping & Totals/Totals icon, and choose Sum.
  • In Design view, choose the Unit Price field, then choose Design tab/Grouping & Totals/Totals icon, and choose Average.
  • In Design view, choose the Quantity field, then choose Format tab/Control Formatting/Conditional Formatting icon, and choose New Rule.
    • Choose Greater Than and type 2, then choose a Blue font color, bold. Choose ok, apply, ok. Then look at the report.

Let's set Print Options.

  • Open the Products and Orders Totals Report in Print Preview.
  • DO NOT print these here - class printer has only 8.5 x 11, and this has too many sheets.
  • Choose a Page Size: Legal
  • Choose Margins: Narrow.
  • Choose Landscape, and see the difference. Choose Portrait instead.
  • Zoom to 2 pages, then back to 1 page. Increase view magnification to 100%.
  • DO NOT print these here - class printer has only 8.5 x 11, and this has too many sheets.
BACK TO TOP

@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@

Thursday

GAMEPLAN

  • Finish Reports
  • Final Project Discussion
  • Getting Started on Final Project
  • Building databases from scratch
  • Planning Methods

DISCUSSION

NOTE: You need to attend the classes during all of Week 7 and Tuesday of Week 8, since I will use class time to demonstrate how to start an example basic database from scratch.

Reports Output

You can export reports to other programs, like Excel and to a PDF.

  • The PDF allows embedded styles/fonts to be set and unchanged when opened on another computer.
  • Excel allows you to export specifically capture information for further use, like Graphs and Charts. Does NOT export the whole database or core tables the reports/queries were generated from.
Final Project

Final Project Details: I'll be grading your work based on what you upload to the CANVAS tool before 12:30pm on Thursday, August 21. Name your file exactly like I require or you will lose points. This assignment will focus on building a basic database with the requirements listed below, using MS Access 2013. NO OLDER VERSIONS ALLOWED. Plan your time well.,

Create a simple relational database for a small business (see choices below). Upload is DUE before 12:30pm on Thursday, August 21. 110 points. There will be absolutely NO LATE FINALS accepted, don't even ask.

Database Users
  • Most folks will end up working with existing databases, as general users - they input information, such as information taken while working in a call center.
  • Many folks may also generate queries and reports from databases, such as folks requesting information about products sold over the past quarter, or preparing reports for annual meeting Powerpoint sessions.
  • Some folks may work on a team of database users to help improve a database, but creating/editing forms and reports, and adding/using Database parts.
  • Some folks will be database administrators, who spend time managing massive databases and running detailed queries and reports.
  • Some folks will become database builders, either as part of website projects or as database designers.
Database Design

Here is a list of database considerations, from Microsoft. The design process consists of the following steps:

  • Determine the purpose of your database. This helps prepare you for the remaining steps.
  • Find and organize the information required. Gather all of the types of information you might want to record in the database, such as product name and order number.
  • Divide the information into tables. Divide your information items into major entities or subjects, such as Products or Orders. Each subject then becomes a table.
  • Turn information items into columns. Decide what information you want to store in each table. Each item becomes a field, and is displayed as a column in the table. For example, an Employees table might include fields such as Last Name and Hire Date.
  • Specify primary keys. Choose each table’s primary key. The primary key is a column that is used to uniquely identify each row. An example might be Product ID or Order ID.
  • Set up the table relationships. Look at each table and decide how the data in one table is related to the data in other tables. Add fields to tables or create new tables to clarify the relationships, as necessary.
  • Refine your design. Analyze your design for errors. Create the tables and add a few records of sample data. See if you can get the results you want from your tables. Make adjustments to the design, as needed.
  • Apply the normalization rules. Apply the data normalization rules to see if your tables are structured correctly. Make adjustments to the tables, as needed.
Important Tips

While most folks may never build databases from scratch, it is good to need to do one by yourself to truly understand how small-subject tables, relationships and queries really work, and how to streamline and display your work through forms and reports.

Even the smallest database needs a lot of pre-design thought in order to build the tables correctly, esablish the correct table relationships, and have successful results in multi-table queries. Here are some tips:

  • Do your homework. Truly plan and understand what the database is for, what will be asked of it, and plan accordingly. Identify the core process - what is it that is key to your database requirement?  Think ahead to all the growth potential.
  • Avoid repeating/redundant data - break data into separate tables that group "like" info by subject.
  • Break information in the tables into the smallest parts for the subject matter. For instance, a Customers table would have FirstName, MI, LastName, Address1, Address2, City, County, State, Zip, Country, etc. You would NOT use one field for the whole address, or one field for the full name.
  • Use a simple and consistent and predictable naming convention - for your Access Objects, for the column heads in your tables, etc.
  • Be sure to create one totally unique column in each table that serves as a potential Primary Key (even if you do not set one).
  • Take the user’s keys away. It’s usually a bad idea to choose a user-editable field as a key, so an autonumber can be set instead. 
  • Carefully check and set the formats and values for each field in a record, so that these are clearly defined rather than just left as text.
  • Avoid nulls by specifying NOT NULL whenever you can. Use nulls rarely, and only when you truly need them.
  • Use constraints wisely: constraints let you prevent changes to the database that violate the internal consistency of your data.
  • Use Third Normal Form (3NF) normalization to help you eliminate the redundancy of data in a database by ensuring that all fields in a table are atomic.
  • Plan forms to Standardize data entry procedures and also to potentially limit access by users to most of the objects in your database.
  • Plan how to back up, secure, and even split your database for security and minimizing of corruption.
Data Storage and Use
  • Data integrity: Accuracy and correctness of data, such as data formatting.
  • Redundancy: Having the same data in ofre than one place in the same database, such as a customer's address in two different tables.
  • Duplication: Having the same data more than once in the same table.
  • Consistancy: Consistant method of entering data in order to prevent duplications / redundancy.
  • Comparability: The ability to compare information in database tables and queries, namely through using different data formats, like text, numbers, dates, currency, etc.
  • Scaleablility: This means how easy it is to adapt your database if it needs to grow very rapidly. Can your database still handle the increased usage without putting you into time consuming troubles? Examples would be web databases like Amazon which might have 100 new cusyomers creating acocunts one day. and two-thousand another day, up to a couple of million in one year.

 

Example Planning

Say you need a database for a small online business you are building for creating and selling specvific products. You plan to stock with maybe 50% your own work, 30-40% of work by another 4 subvendors you know, and the remainder with special things you find later on. What next?

  • What do you need the database to DO? What information will you need to look up the most (Queries)? What information will you need to share the most (Reports)? How will the database be maintained (new information - Forms)?
  • Say you decide this database will exist primarily to show inventory, sales, and contact names of customers. What info must be in the database for it to work (sorts, filtering, querying)?
  • Likely you need all info on customers, the artists, the products, the orders, fulfillment, special requests, and on comments/feedback. What tables do you need to capture all of this?
  • Likely you will need a separate table for customers, artists, products, and shippers. Do any of these main tables have a subset of information that should be in a separate table instead?
  • Likely you might have a Category type for the products. Your Orders table might have a second table for details on specific orders.
  • If you are not sure, sketch on paper an example, from beginning-to-end, of a single transaction that would be captured by this database.
    • Who is the artist and what is the product? What does the product cost? Who is a customer, and what did s/he order? How many and on what date? Where will it be sent and billed? Has it been paid for yet? Is there tax? How much will the artist earn? Will it need to be shipped, and by who, and for how much? Has it shipped yet or when will it? Etc. This process should give you a good idea of how to plan the Tables and then to relate them for Queries.
  • Think about how the information has to get INTO the database. Who is going to type it, and how? What if you know the customer and phone number but not full address? What is 20 new pieces of come in and need to be added to the database? What if you add a new shipper? This is how you think ahead on your Forms.
  • What if you need to have information in February to prepare your annual small business taxes? How do you get that info from the database so you know how to fill out the Tax Forms/program? This is how you think about Reports.
  • Who is going to keep the database safe? Who will store it, clean it up, protect it from hacking? This is where you think about the security and utilities to plan to use.

DEMO

Database Building

I'll do a Pet Store one like I discussed at the beginning of the quarter. I'll only do bits and pieces, since I want you to have in-class time to work and explore.

Planning:

  • I want to build a simple, workable database for a single pet shop which also has a website of information. HappyPaws, sells products, offers basic grooming services, and has a visiting vet twice a month and teeth-cleaning service once a month.
    • Products sold include food, toys, training items, hygiene, pet home furniture, litter and boxes, etc.
    • Services include several grooming choices: cut, shave, shampoo, flea dips, nail clipping, etc. Also a twice-a-month visiting vet, once-a-quarter visiting microchipper, and once-a-month dental hygenist.
    • Events include a summer Bark-b-Que, a Holiday pet picture with Santa, and twice-a-year Vendor Freebie visit.
    • HappyPaws has customers, employees, product vendors/reps, service providers/contacts, numerous products,

Goals:

  • Employees are NOT payrolled from this database - we use Quickbooks. However, employee hours and contacts should be collected in the database for export to Quickbooks.
  • Products have individual prices, case prices, taxes. These need to be detailed to show sales summaries, tax changes, sale prices, etc.
  • There are categories of products: food, hygiene, toy, training, vitamins, etc. These need to be describes and named.
  • Service providers have service fees, hours spent, scheduling. The service providers need all contact into, and scheduling info (separated?)
  • There are only specific services and packages offered.
  • Customers have human contact info, pet(s), and specific needs/notes.
  • There are only specific urban/suburban pet types HappyPaws handles: cats, dogs, rabbits, guinea pigs, small rodents, a couple of lizard types. No snakes or fish, and no rodents as food.
  • There are specific health needs that pets have that need to be considered when recommending food, treats, toys, and basic health vitamins.
  • What am I missing?

Core Tables needed:

  • 1 Database notes table - to be kept in database for other users, NOT to be related to any other table.
  • PENDING

Table Steps/Tips:

  • Create 1 core table to at a time. Make sure you really design the heck out of it - all details, before moving on to the next.
  • Name simply/effectively. Ex: Customers Table.
  • Create basic columns, either in datasheet or design view.
  • Decide on the ID number/method for each record, make UNIQUE so that it cannot be duplicated.
  • Set your Primary Key
  • Use simple, no-spaced Column names. Keep using these kind of name consistently throughout yourt Database.
  • Make sure to break up all appriopriate information into separate columns you might need to sort, like LastName, MiddleInit, FirstName; and ProdName, ProdCategory, UnitPrice, CasePrice
  • Use only columns in this table than cannot/should not be separately typed into another table.
  • Set every Data Type right away: type, field size, input mask format, etc.
    • IF you need a new field to start with a specific Default Value (like Reorder Number 5), add this.
    • IF you need a new field to be required (other than Autonumber), use the Validation Text to type something like (Required Info)
    • Make sure that Indexed is set to Yes, NO Duplicates.
  • Use a short description in EVERY table's field - as if someone else will need to see it later.
    • For instance, in ANY field that might become a Foreign key in another table, note this in the Description field.
  • HOT: In this table, create one (1) single Test Case record/row that is appropriate for the table. This is a keeper to help try out Data Types and Test Relationships BEFORE you import/create mass data.
  • NOTE: Once you have a well-functioning contact table, you can Copy and Paste it into the Database, then RENAME the copy to the appropriate name for the new use of the table.
    • Be 100% certain to go through the Reusable table to CHANGE all Column Headings, appropriate Data Type lengths and info, and Comments so they are ready for the New table as it WILL be used.
    • DO NOT try to "reuse" tables for every purpose - do Like with Like, like Customers, Vendors, Supplies.
  • When you COMPLETE creating your tables, Set and Test all relationships with your Single Test Case rows to verify the tables and data formats all work properly. DO NOT IMPORT / ADD actual data until you fully test the basic tables and relationships FIRST.

Special Note: LookUp Tables

A Lookup table is really good for common information you want to be selected from a dropdown choice box when someone inputs NEW records. However, keep in mind:

  • Create a LookUp table for ONLY brand new/unimported information in a database, like specific Category Names, etc.
  • DO NOT try to create a LookUp table for information that will be imported en masse - there will be horrid conflicts.
    • For instance, you might want a State Look-Up table, right? DO NOT create one to link to a Customer or Vendor Table IF YOU PLAN TO INPUT EXISTING addresses and states. Create one to use as a LookUp table for only empty tables you are inputting the info in for the first time.

Think of Table Relationships as you plan:

  • What tables will you need to ask questions about?
  • Are the Data Types/Formats in each of these tables the same format for when you try to relate the tables? Ex: (CustID in Customer Table, and CustID in Orders Table?)
  • What kind of Joins do you want your query results to show?
  • Be sure to ALWAYS choose Referential Integrity in your Relationships.
  • Choose Cascade Update Related and Cascade Delete Related when creating Relationships.
  • After you set Relationships, and as you create Queries, Forms, and Reports, check the Object Dependencies to see how the objects are related to each other.

Info to use/import as desired: Names spreadsheet

BACK TO TOP

@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@

TO DO

  • MIC ASSIGN 6: Reports, due before 11:59pm, Sunday, August 10, 45 points.
    • Complete ALL of Lesson 5, use GardenShop5.accdb. Also images ClayPots and Garden Gate. You don't need to upload the extra database you use to import from.
    • Save YOUR version of the database file as assign6firstinitiallastname.accdb so I know whose file it is I am grading.
Resources
BACK TO TOP