Welcome to Office 2016, video number 41.
Hey, we're talking about Access 2016.
And in this video, we have a simple topic.
We need to see how to use a Report Wizard to create reports
based on queries.
And we have two examples.
We want to take our inventory value query
that we created last video and using the Create Ribbon tab
and then over here in the Reports Group,
we want to use the Report Wizard.
And here's the wizard we'll use for inventory value.
And then we'll take our product Reorder Phone List,
use the wizard and create two reports.
And then, of course, as always, when the raw data changes,
the queries will change and our report output will also change.
All right, at our class web site--
if you click on the link below the video,
you get to this class website--
You need to either have the completed access
database from the last video or download
WoodenCraftsDatabaseFinished after video 40.
It's a zipped folder.
So you have to download it and unzip it.
Once you get that Access database, here it is.
Tables.
That's the heart of any database,
that's where keep our raw data.
Queries, that's where we ask questions.
Forms, that's where we enter or change, edit raw data.
Our goal is to create the fourth type of object
we can have in Access.
Now, here is Inventory Value.
I'm going to select it.
Now go up to Create.
And in the Reports Group, we want to use the Report Wizard.
Now, a report is simply going to be a formatted version
of our query.
Inventory value is something that we periodically check
and similarly, when we do Product Reorder Phone
List, that is something we use at the end of each week
to figure out what we need to reorder.
Now.
The wizard is simple.
Step 1.
We already selected the correct query, so it is listed there,
but you can change it to whichever
object you'd like to look at.
Inventory Value is what we want.
Here's a list of the fields.
I can choose from the list by using the single arrow
or I can use the double arrow to bring all of the fields over.
I'm going to select the double arrow and just
like that, selected fields, Description and Inventory.
I click Next.
Now this is asking if we want to group.
If you have duplicate in the columns,
you can then choose which column to group by.
Then it won't list all those duplicates,
it'll list just one item.
We don't have any duplicates.
So we're going to click Next.
Sorting.
I'm going to select Inventory Value
and we're going to do Ascending.
Next.
"Columnar" means in a column.
"Tabular" means in rows.
We're going to select Tabular.
This report is not very wide, so I'm
to choose Orientation Portrait.
Click Next.
It automatically checks Adjust Fields Width
so that all the fields fit on the page.
I'm going to click Next.
This is the title of the object.
So I'm going to click right before the I and backspace.
Inventory Value is fine.
I want to preview the report.
So I'm going to select that one and click Finish.
And there it is.
A formatted version of our Inventory Value.
Now we're going to go over and close Print Preview.
In Design or in Home, we can go to the View Group.
Click the dropdown.
I'm going to select Layout View.
We saw this view when we created our form.
I can change the column width.
Here, I'm going to select the field name at the top.
Holding Control, I'm going to click on the inventory values,
and then very carefully, I'm going
to use my horizontal black arrow and adjust the column width.
I'm going to do the same thing over here, click.
Hold control.
Click.
Click and drag.
Now I'd like to select all of the inventory values
and then I'd like to align them to the left.
I'm going to go up to Format.
Click Left Align.
I don't think I like that.
I'm going to try Center.
All right.
Now, I'd like to highlight the field name.
I'm going to hold Control and click the other field
name that I'd like to add.
Bold and increase the font size.
Let's try 14.
All right.
Now we can go over to Design.
Over to Views and Print Preview.
There we go.
I'm going to Control S. Now when I close this object, down here
is our fourth group, Reports.
I can double click that at any time and there is our report.
And we notice that inventory value for Railway Bridge is
$8.50.
This better be dynamic.
It better update when the raw data changes.
So I'm going to close the report.
Go up Products Form.
Double click.
I'm going to click in description and I
want to find that record--
Railway Bridge.
Control F is the keyboard for Find.
We talked about that over in Excel and Word.
Control F got us to Find.
Find what?
Now, if I select Whole Field, I better
type everything correctly.
I'm going to select Any Part of Field.
That way you could search for the first few letters
and a last name, for example.
I'm simply going to type "Railway" and Find Next.
There it is.
Railway Bridge.
Click Close.
I'm going to change this to 10.
This is raw data.
This is the form.
So I can simply Close.
And when I come down to double click Reports, double click.
Sure enough.
Oh, look at that, it's even sorted all the way
to the bottom--
Railway Bridge.
$8.50 times 10 is $85.
Now I'm going to close this report.
Come over to Products.
Click in Description, Control F. Any Part is already selected.
Railway is still there.
I'm going to say Find Next.
Close this.
Backspace on that zero.
And Close.
Now when I come over to my report.
That is totally amazing.
Inventory Value report based on our query.
Now, we also want to create a report for Reorder Phone List.
Now double click and open this up.
Remember, this has a condition or criteria.
Any time the On Hand falls below 5,
the products will appear in this query.
Also notice that we have duplicates.
That's a situation where we can use something
like Grouping to list the last name and the telephone number
one time, but then list the products below.
I'm going to close this query.
With it selected, now I can come up to Create.
Over to Reports Group, Report Wizard.
I'm making sure that I have the correct query selected
in step 1.
I want all of the fields.
So I'm going to click the double arrow.
Selected Fields have all the fields.
Click Next.
Now, how do you want to view your data?
Well, we're pulling data from two tables.
So when I select Supplier Data, that's
the one side of a one to many relationship.
Remember, one supplier each--
last name and telephone number for the one supplier.
Because that's the one sided it appears at the top.
All of the products on the main side will be listed below.
Now this is a type of grouping, but this
is occurring because we have a one to many relationship.
Supplier is the one side, many is the product side.
All right, I'm going to select Supplier Data and click Next.
Now it's going to ask us if we want to group,
but we don't have any duplicates that we need to group.
So we're simply going to click Next.
Reorder Phone List.
I definitely want to do this On Hand.
The product with the smallest number of on hand units
will be listed first.
I'm going to click Next.
Now we have a choice between portrait and landscape.
I think since we have more fields here,
it's going to be wider than it is tall,
so we're going to choose landscape.
Stepped.
You can see what it will do.
It'll have a stepped effect to the report.
Block, you can see a preview.
That's actually kind of cool.
This wizard gives us a preview.
But we're going to stay with Stepped.
Adjust Fields is fine.
Click Next.
I do not want Supplier Data as the name,
so I'm going to select this and type Product Reorder Phone
List.
All right.
I want to preview the report.
I'm going to click Finish on this wizard.
And there's our report.
We can use the horizontal scroll bar to scroll over.
Whoa.
That's way too far over.
I'm going to scroll over here and we're
going to close Print Preview.
Go over to either Design or Home.
Find the View Group and we'll go to Layout View.
I'm going to select Last Name, hold Control, click the column,
and then adjust the column width just a little bit.
I'm going to click on Telephone and the Telephone Numbers.
Click and drag to adjust the width.
On Hand.
I'm going to select the field name at the top, hold Control.
Click on the column.
Try and adjust.
Now, watch this.
I'm going to use the arrow keys to move the entire field name
and column of numbers.
So I'm using my arrow key.
Click on Product ID and elements in the column.
I'm using my arrow keys to arrow over.
Description I'm and to leave it there for the time being.
And then this Cost column, I'm going
to click on the field name, hold Control, click on the numbers.
And from the right, I'm going to adjust the column width.
And then I can use my arrow keys.
So that's looking OK.
I would like to select the field name,
so I'm going to select Cost.
I'm holding Control.
And I'm going to click on each one of the field names.
Now I'm going to come down and get my horizontal scroll bar.
Now I'm going to re-select Control,
click on the last field name.
Up in Format.
Bold.
Maybe something like 12, 14, maybe.
All right.
Over to Design.
Views.
Let's look at Print Preview.
And there is our Reorder Phone List.
And you can see there's a one to many relationship
that is, in essence, grouping it.
Listing the last name and telephone number only one time.
Now, this is perfect.
At the end of each week, we just come here, print this,
and we have this report we can use to phone and reorder.
Now I'm going to close this.
It'll ask me to Save.
Definitely say Yes.
Come over to Products Form.
I'm going click the Next Record button down here.
Blocks in Box.
This is past the hurdle.
So I'm going to backspace and change it to 4.
And now this better show up on our Reorder Phone List.
I'm closing the form, the data is automatically saved.
I come down here.
Double click the report.
And sure enough, there's the Blocks in Box.
That is just a great example of the power of a database
where we're storing the raw data and we
have some end results, some useful information,
presented in this report.
As soon as the raw data changes, this totally updates.
All right.
That's it for video 41.
We used-- over in Create, we used Reports Report Wizard
to create two reports-- one for Inventory Value and Product
Reorder Phone List.
And we saw when we changed the raw data, it totally updated.
All right, next video, we have one last Access video.
We'll talk about exporting data to Excel and PDF form.
If you liked that video, be sure to click that thumbs up.
Leave a comment and sub, because there's always lots more videos
to come from Excel is Fun.
All right.
We'll see you next video.