Tip:
Highlight text to annotate it
X
okay in this video I want to show the powerful
crosstab query in Microsoft access.
So let's say I have the table that's called orders,
this would be a list of all the individual transactions from a certain
company.
This is one of the tables from the sample files that come with Microsoft Access in the
Northwind database.
So notice how I have the Order ID, the customer, the employee, the order date ,
and then I have the
ship country from the information as well.
and then we have the order amount..Now lets how many transaction we have, and we
have 830 record, 830 transactions.
So let's say the request is that they want a summary
report by country broken down by Month.
So let's see how we're gonna do that. We have a very powerful
function in access that's called the crosstab query wizard.
I'm gonna pick the create menu , in access 2010.
I'm going to click on create. I'm gonna pick on the Query Wizard
and we'll pick crosstab query wizard.
So this is just a message from my own computer. I click on
okay. So now the crosstab query will start.
It asks us which tabley contains the fields you want for the crosstab query.
So you pick your table or even a different query. In this case I pick
table that is called orders.
I click on next. Now a crosstab is actually similar to a pivot table
they might see in Microsoft Excel so it says what field do want for your row
headings.
Well you want to think about the report you want to generate.
I want to generate a report that's going to be by ship country
by month so that kinda gives you some clues.
So ship country might be my
row heading .
Now you could actually pick more than one
row heading so you get subtotals within subtotals,
when you use multiple fields but in this case , we wil use ship country.
On the next screen, it now says what fields you want for your column headings.
You really could use any other field. So if you want your cross reference perhaps
on the ship country verses the employee,
then I might pick the employee field
as the column heading.
however,
if I wanted it by Month or by quarter or by year, then you would use
the a date field as the column heading. I pick order date and click on next. Now it knows that I just picked the date
field,
so I can do it by year, quarter
month , or even by date or time ,so
in this case let's do it by month. Lets look at the sample down here . I have a by ship
country and then by month.
I pick on next. Now it says what number do I want calculated for for each column and row
intersection so in other words
for each country for each month
what number do you want to calculate so usually
you would pick some kind of number field. although you don't have to pick a number field. but when do pick a number field,
than we can pick a different type of calculation
I will pick the order amount, then and sum on this column. So what I am
asking for?
I want to see for each country for Each month.
I want to show the sum of the orders for that month.
Now I pick pn finish and let's see what we have.
Notice what it did. This is a great result that shows each country broekdn down month ny month.
So now after just a few minutes
I have a breakdown by country by month
very very quickly
and that's called a crosstab query in Microsoft access.
All I did was click on create and then the query wizard,
and the cross-country wizard and then we just follow the steps.
very great results there with a cross tab query.