microsoft access
Download
Skip this Video
Download Presentation
Microsoft Access

Loading in 2 Seconds...

play fullscreen
1 / 16

Microsoft Access - PowerPoint PPT Presentation


  • 177 Views
  • Uploaded on

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

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about ' Microsoft Access' - dory


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
microsoft access

Microsoft Access

Crosstab Query » Form »Chart

Succinctly Summarizing Data

desired final output
Desired Final Output

PgP MIS 342 Crosstab-Form-Graph

advanced access skills
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

starting point
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

create select query
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

create crosstab query
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

crosstab query
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

create chart to display data
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

microsoft graph applet
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

create chart to display data1
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

chart wizard
Chart Wizard
  • Click chart icon, place in upper left area of form to start Chart Wizard…

PgP MIS 342 Crosstab-Form-Graph

chart wizard1
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

final chart changes
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

working with graph applet
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

charting data on the form
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

chart design
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

ad