Microsoft access
1 / 16

Microsoft Access - PowerPoint PPT Presentation

  • 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

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
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