Microsoft Access Programmer Omaha, Nebraska

Microsoft Access Pivot Tables And Charts

Call MS Access Solutions at (323) 285-0939 for your FREE consultation.

The following material originally appeared in Alison Balter's book Mastering Microsoft Office Access 2007 Development and is reprinted here with the author's permission. There may be references to "Figures" or "Chapters" that are not reprintable and are not used on this page.

Creating A Pivot Table Or Pivot Chart From A Form

Pivot tables and pivot charts provide great ways for you to summarize detailed data stored in your Access Database Engine and SQL Server databases. Pivot tables present your data in a spreadsheet-like format, whereas pivot charts automatically render pivot table views as line, bar, or area charts. Access 2002 and Access 2003 provided two new views for forms: PivotTable and PivotChart.

Creating The Form To Display In PivotTable Or PivotChart View

You first must build a form that is appropriate to display in PivotTable or PivotChart view. Forms that lend themselves to be displayed in PivotTable or PivotChart view provide many ways for users to manipulate their data. An example of such a form is one that contains information about country, city, salesperson, sales, and date of sale. You could determine sales by city and salesperson for each month, or you could determine sales in each country for each salesperson during the year 2006. As you can see, the idea of pivot tables is to let you slice and dice the data in any way you need to at a given moment in time.

For this example, create the following query within the Northwind database. Then use the Autoform feature to base a form on the query:

1. Create a new query in Design view.
2. Add the Customers, Orders, Products, Order Details, and Employees tables to the query.
3. Add the Country/Region and City fields from the Customers table.
4. Add an expression: SalesPerson: Employees!LastName & ", " & Employees!FirstName.
5. Add the Order Date from the Orders table.
6. Add the Product Name from the Products table.
7. Add an expression: Total: [Order Details]![Unit Price] * [Order Details]!Quantity.
8. Double-click the join line between the Customers and Orders tables and select option 1. Click OK to close the dialog. This sets the join type to an inner join.
9. Double-click the join line between the Orders and Order Details tables and select option 1. Click OK to close the dialog.
10. Close and save the query as qryPivotTable.
11. Select the query in the Navigation Pane.
12. Select PivotTable from the More Forms drop-down on the Create tab.

You now have the foundation for your pivot table.

Displaying The Form In PivotTable View

Once you are in PivotTable view, notice that the empty PivotTable view appears and the ribbon contains a Design tab with the tools appropriate for working with a pivot table.

Included on the Design tab is the Field List button. There are four types of fields that you will add to your pivot table. They include the following:

• Column fields often hold date fields and generally hold information with the fewest number of data items
• Row fields are one or more fields that display data by attributes
• Totals or Detail fields are the crosstab data itself and represent the numeric values that make up the core of the pivot table
• Filter fields are one or more optional fields that restrict the data appearing in the columns, rows, or both

---

Author Attribution

The precding information originally appeared in Alison Balter's book Mastering Microsoft Office Access 2007 Development and is reprinted here with the author's permission.

---

Expert Microsoft Access Programmer Serving Omaha, NE

When you need a Microsoft Access programmer for your Omaha, Nebraska business, call MS Access Solutions at (323) 285-0939. We have over 25 years experience in Microsoft Access programmer solutions. We build and maintain Microsoft Access database applications for clients across a wide range of industries, including, but not limited to: hospitals, medical clinics, government agencies at the federal, state, and local levels, the U.S. military, universities, junior colleges, school districts, agricultural operations, employment agencies, human resources departments, insurance companies, and oil and gas companies.

Our services range from advanced, enterprise-level programming to targeted fixes on smaller, high-priority projects. We handle complex integrations between Access and SQL Server, build custom front-end interfaces, automate data workflows, and develop secure multi-user environments that support internal operations and reporting needs.

On the smaller end of the scale, we frequently take on projects such as:

• Fixing damaged or unresponsive Access database forms
• Correcting broken or inaccurate Access reports
• Repairing and modernizing Access macros
• Debugging and optimizing Visual Basic for Applications (VBA) code
• Troubleshooting ODBC connection issues between Access and SQL Server or other data sources
• Restructuring or rebuilding broken Access tables and queries
• Improving performance in slow-loading forms and reports
• Fixing navigation issues, broken buttons, or missing controls
• Migrating data from Excel or legacy systems into an Access-based solution
• Refining user experience with better form layouts, tab order, and field-level validation
• Converting legacy Access applications to modern versions with 64-bit compatibility
• Adding new features to existing databases, such as dashboards, export tools, or audit trails

When you need a Microsoft Access programmer for your Dallas Texas, business, organization, or agency, call MS Access Solutions at (323) 285-0939. We have over 25 years experience as a Microsoft Access programmer We create Access database applications for all industries, including, butr not limited to hospitals, medical offices, chiropractors, pharmaceutical companies, dentists, government agencies at the local, state and federal level, the U.S. military, school districts, junior colleges, universities, agriculture, horticultural, human resources, employment agencies, insurance companies, insurance agencies, and non-profit organization

---

Find out more about our programmer services on the Microsoft Access programmer Albuquerque, New Mexico web page.