Microsoft Access Crosstab Query Form Chart Succinctly Summarizing
Microsoft Access Crosstab Query » Form » Chart Succinctly Summarizing Data
Desired Final Output Pg. P MIS 342 Crosstab-Form-Graph CTG-2
Advanced Access Skills �Combine skills learned so far-Access 2010 �Objective: create one chart 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 Pg. P MIS 342 Crosstab-Form-Graph CTG-3
Starting Point �Download Graphing. Problem 2006_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 Pg. P MIS 342 Crosstab-Form-Graph CTG-4
Create Select Query �Query contains only necessary fields �Orders. Order. Date �Sales: [Order Details]. Unit. Price * Quantity �Products. Product. Name �Criteria-Where Order. Date is Between 1/1/2006 and 12/31/2006 �Total of 1059 records �Save as qry. Sales. By. Product Pg. P MIS 342 Crosstab-Form-Graph CTG-5
Create Crosstab Query �Use Crosstab Query Wizard �Based on qry. Sales. By. Product �Row Heading-Product. Name �Column Heading-Order. Date Month Value- Sum(Sales)… �Include row sums �Save as qry. Sales. By. Product_Crosstab Pg. P MIS 342 Crosstab-Form-Graph CTG-6
Crosstab Query �Save as qry. Sales. By. Product_Crosstab �Run the query �Much more compact! � 13 columns � 77 records �Notice empty cells �No sales that month-normal occurrence Pg. P MIS 342 Crosstab-Form-Graph CTG-7
Create Chart to Display Data �Chart Wizard cannot do it all �Only allows 6 fields, need 12 (months) �Adhere closely to following steps Pg. P MIS 342 Crosstab-Form-Graph CTG-8
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 Pg. P MIS 342 Crosstab-Form-Graph CTG-9
Create Chart to Display Data �Form Properties, Data tab, Record Source property is set to “qry. Sales. By. Product_Crosstab” �Make form detail area 5. 25 inches high �Make form 9. 9 inches wide (max) Pg. P MIS 342 Crosstab-Form-Graph CTG-10
Chart Wizard �Click chart icon, place in upper left area of form to start Chart Wizard… Pg. P MIS 342 Crosstab-Form-Graph CTG-11
Chart Wizard �Select qry. Sales. By. Product_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 Product. Name �Title- “Sales By Product” Pg. P MIS 342 Crosstab-Form-Graph CTG-12
Final Chart Changes �With Graph selected, in properties… �Select qry. Sales. By. Product_Crosstab as Row. Source �Open SQL Statement Query Builder �Leave Product. Name, �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 frm. Crosstab. Chart Pg. P MIS 342 Crosstab-Form-Graph CTG-13
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-friendly application! Pg. P MIS 342 Crosstab-Form-Graph CTG-14
Charting Data on the Form �Notice when you view chart and press page down to go to new record- “Calculating” appears in Status Bar Pg. P MIS 342 Crosstab-Form-Graph CTG-15
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? Pg. P MIS 342 Crosstab-Form-Graph CTG-16
- Slides: 16