1 / 16

Microsoft Access

Microsoft Access. Crosstab Query » Form » Chart Succinctly Summarizing Data. Desired Final Output. Advanced Access Skills. Combine skills learned so far-Access 2010 Objective: create one chart that can display an unlimited number of records

dory
Download Presentation

Microsoft Access

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Microsoft Access Crosstab Query » Form »Chart Succinctly Summarizing Data

  2. Desired Final Output PgP MIS 342 Crosstab-Form-Graph

  3. Advanced Access Skills • Combine skills learned so far-Access 2010 • Objective: create onechart that can display an unlimited number of records • Use a query and crosstab query to summarize the data • Use a form to display the chart PgP MIS 342 Crosstab-Form-Graph

  4. Starting Point • Download GraphingProblem2006_Data.mdb • Three main tables-Order Details, Orders and Products • Goal-produce a form containing a chart that will display 2006 Product Sales for each month, with a separate record for each product PgP MIS 342 Crosstab-Form-Graph

  5. Create Select Query • Query contains only necessary fields • Orders.OrderDate • Sales: [Order Details].UnitPrice* Quantity • Products.ProductName • Criteria-Where OrderDate is Between 1/1/2006 and 12/31/2006 • Total of 1059 records • Save as qrySalesByProduct PgP MIS 342 Crosstab-Form-Graph

  6. Create Crosstab Query • Use Crosstab Query Wizard • Based on qrySalesByProduct • Row Heading-ProductName • Column Heading-OrderDateMonth Value- Sum(Sales)… • Include row sums • Save as qrySalesByProduct_Crosstab PgP MIS 342 Crosstab-Form-Graph

  7. Crosstab Query • Save as qrySalesByProduct_Crosstab • Run the query • Much more compact! • 13 columns • 77 records • Notice empty cells • No sales that month-normal occurrence PgP MIS 342 Crosstab-Form-Graph

  8. Create Chart to Display Data • Chart Wizard cannot do it all • Only allows 6 fields, need 12 (months) • Adhere closely to following steps PgP MIS 342 Crosstab-Form-Graph

  9. Microsoft Graph Applet • Graph applet embeds charts in forms/report • Excellent way to summarize data • Similar but not same as Excel Charts! • Open new blank form in Design View PgP MIS 342 Crosstab-Form-Graph

  10. Create Chart to Display Data • Form Properties, Data tab, Record Source property is set to “qrySalesByProduct_Crosstab” • Make form detail area 5.25 inches high • Make form 9.9 inches wide (max) PgP MIS 342 Crosstab-Form-Graph

  11. Chart Wizard • Click chart icon, place in upper left area of form to start Chart Wizard… PgP MIS 342 Crosstab-Form-Graph

  12. Chart Wizard • Select qrySalesByProduct_Crosstab • Select first 6 fields to be on chart (Product, but not Total of Sales) • Accept default column chart • Accept default chart layout • Accept default-link form and chart by ProductName • Title- “Sales By Product” PgP MIS 342 Crosstab-Form-Graph

  13. Final Chart Changes • With Graph selected, in properties… • Select qrySalesByProduct_Crosstabas RowSource • Open SQL Statement Query Builder • Leave ProductName, • Add all months to QBE grid, make sure to choose ‘Sum’ • Form Design view, double click chart to edit • Make sure to save form as frmCrosstabChart PgP MIS 342 Crosstab-Form-Graph

  14. Working With Graph Applet • Resize chart • Legend, place at bottom, resize, align to columns, change legend font size to 11, change alias in SQL! • Add data labels to column for each month, modify font, set as currency, no decimal places • Delete y-axis autoscale, use 25,000 as max value • Not a user-friendlyapplication! PgP MIS 342 Crosstab-Form-Graph

  15. Charting Data on the Form • Notice when you view chart and press page down to go to new record- “Calculating” appears in Status Bar PgP MIS 342 Crosstab-Form-Graph

  16. Chart Design • From menu, Choose Chart>Chart Options • Explore changes that can be made • What other enhancements can you make? • Is this more efficient than making 77 or 10,000 individual Excel graphs? PgP MIS 342 Crosstab-Form-Graph

More Related