A common way of viewing data in Microsoft Access is through the use of rows and columns, or records and fields. Another view, which is known as a Crosstab query, has headers along the top and side, and the summary or aggregate data is in the table's middle. In this online tutorial, you'll have the opportunity to learn about getting a different look with crosstab queries.
Queries generally follow the rows and columns or records and fields model for their layout. There's also a slightly modified layout option called the Crosstab query, that gives us headers along the top and side and aggregate or summary data appears in the middle. Let's put one together using our phone list so you can see how this works. Now right now our EmployeePhone lists are stored in a second related table. This provides flexibility in adding multiple numbers for each employee where most employees will have at least one office phone, but some might have additional lines for their cell phone, their fax number or their home phone or an emergency contact number.
I would like to first put together a Select query that gathers all of these numbers and then convert that query into a Crosstab format, so you can see the improvement in legibility. I'll go ahead and close the EmployeePhone table and we'll start a new query in Design view. I'll add the Employees table and the EmployeePhone table and close the Show Table window. Now I'll pull in a few fields. From the Employees table I'll double-click on FirstName and LastName and from the EmployeePhone phone table I'll grab the Number and the Type.
Let's go ahead and view the datasheet to see the records we've collected. You'll see multiple lines for each employee which is not quite ideal. Let's go back into Design view and clean it up. I'm going to change the Query Type from Select query over here to Crosstab query. When I do that I get two additional rows down here in the grid, Crosstab and Total. In the Crosstab row we're going to define what fields go where in the grid. Our first and last names are going to be Row Headings. I'll click the dropdown and choose the Row Heading.
The Type will become our Column Heading and the number is what's going to appear in the middle, we're going to choose Value. Now also for the number we need to choose how we want to show those values. We're going to change this Totals row to First. The Total is how we want to aggregate or summarize the data in this area. Most of these options will make sense for numerical data such as sum or average but wouldn't make any sense for phone numbers. We'll choose First here, so that we're not applying any sort of mathematical transformations to the data. Now finally I want to make this a usable directory so I'm going to apply a Sort on LastName to alphabetize it ascending.
Now let's go ahead and view the datasheet to see the difference. It's exactly the same data as before just displayed in a more compact way. Each employee now only has one row but we still see all of their phone numbers with a type listed across the top. Further, this layout makes it very easy to see what employees are missing crucial information. Maybe it's time to put some pressure on people to give us those emergency contact numbers. Crosstab queries can provide a different organization to your data, sometimes making it easier to read, make comparisons or spot trends.
The aggregate values that appear at the intersection of the row and column can also be set to display account of how many records are in that group or their sum total, average, lowest, or highest values.
Author
Released
2/8/2013- Understanding table structures and relationships
- Setting primary and foreign keys
- Establishing relationships and maintaining referential integrity
- Sorting and filtering data
- Building queries with constraints and criteria
- Editing table data with queries
- Generating forms from tables
- Adding form controls
- Creating reports with totals and labels
- Embedding macros in buttons
- Repairing your database
- Protecting databases with passwords
Skill Level Beginner
Duration
Views
Related Courses
-
Excel 2013 Essential Training
with Dennis Taylor6h 32m Intermediate -
Setting Up a Database in Excel 2013
with Dennis Taylor49m 17s Intermediate -
Access 2013: Tips & Tricks
with Adam Wilbert2h 50m Intermediate
-
Introduction
-
Welcome1m 1s
-
-
1. Getting Started
-
Key database concepts4m 21s
-
Creating the database file3m 25s
-
Digging in to Ribbon tabs3m 39s
-
Using the Navigation pane3m 36s
-
Taking backups47s
-
Accessing help1m 8s
-
-
2. Creating Tables
-
Importing tables4m 57s
-
3. Setting Field Properties
-
Setting the default value2m 15s
-
Creating lookup fields4m 29s
-
4. Organizing Records
-
Formatting columns2m 52s
-
Sorting table data3m 18s
-
Filtering table data2m 21s
-
-
5. Using Queries
-
Understanding queries2m 2s
-
Building expressions5m 23s
-
-
6. Working with Specialty Queries
-
Using update queries3m 21s
-
Using make table queries2m 57s
-
Using delete queries2m 48s
-
Using append queries2m 49s
-
-
7. Creating Forms
-
Generating forms from tables3m 10s
-
Using the Form Wizard2m 38s
-
8. Designing Forms
-
Setting data sources5m 39s
-
Understanding input boxes3m 31s
-
Recording navigation2m 36s
-
Adding buttons4m 18s
-
Assigning tab order4m 5s
-
Creating a navigation form3m 36s
-
9. Creating Reports
-
Introducing reports2m 15s
-
Using the Report Wizard5m 9s
-
Creating calculated totals3m 32s
-
Creating labels4m 18s
-
Adjusting print settings2m 58s
-
-
10. Working with Macros
-
Creating macros5m 3s
-
-
11. Integrating Access with the Office Suite
-
Emailing with macros4m 4s
-
12. Maintaining the Database
-
Documenting your work4m 41s
-
Creating custom Ribbon tabs3m 35s
-
Conclusion
-
Next steps1m 6s
-
- Mark as unwatched
- Mark all as unwatched
Are you sure you want to mark all the videos in this course as unwatched?
This will not affect your course history, your reports, or your certificates of completion for this course.
CancelTake notes with your new membership!
Type in the entry box, then click Enter to save your note.
1:30Press on any video thumbnail to jump immediately to the timecode shown.
Notes are saved with you account but can also be exported as plain text, MS Word, PDF, Google Doc, or Evernote.
Share this video
Embed this video
Video: Getting a different look with crosstab queries