[Music] in this video I'll demonstrate how to create a report from multiple tables in
Microsoft Access reports are one of the most commonly used features of an Access database
as a user most of the time you're not going to be creating databases and you're not going to be
designing them and you're not going to be creating your tables in a database even though you can do
that however most of the time you're going to be creating queries in a database and you're gonna
be creating reports from the existing data from that database now the reports can come in handy
whether you're in a corporate environment or you have a corporate database or whether you have an
Access database but the concept it's going to be pretty much the same since the tables are linked
typically and the linking of the tables you can see it here under the database tools and
relationships we have customers customers have contracts and then for each contract there are
invoices as well so it kind of all this stuff it's linked together however now in our case
we can create a report from fields from the customers table original first name last name
and then contact information and also include the contract so you see who our customers are
what contracts those customers have so that's what we're going to learn in this session so to create
a report you can go here under create and then we want to go under this section here under the
reports section and we want to look here under the reports section my suggestion if you're starting
is to use the report wizard so we click on that and then we are going to pick some fields here
from the customers table so we go for example if you wanted a customer ID you can pick it as well
click on add customer ID first name last name and let's say we want just their telephone number and
the email address notice you can pick only certain fields then you can also go here under the drop
before we move to the next step and we can pick from the contracts table from another table you
can pick fields from that table as well because those tables are linked with primary keys and
foreign keys so we go here under table contracts and now let's say we want the contract amount and
then the contract type so that's all we want in our case then we click on next since we are
using multiple tables the system is asking us do we want to group the report results by customer
or by contracts so in this case if you have a customer that has multiple contacts you want all
those contracts for that customer grouped together so in my case I'm going to choose customers and
it's going to list me the customer and then a sub listing of all that customers contracts so that's
what this is for so you have customer up here and then the contracts will be listed right below it
then we click here next and then so what kind of levels do we want here so we could say we want a
customer ID first name last name telephone email and then the contact information right below it
so then we click on next and then how do you want to sort those so you could sort those records and
then you could also choose here that the report summary options and here you could say you want
to see what the average is or what the sum of all the contracts was and all that type of stuff for
each customer you can just choose here sum and the system is going to give you the total of all their
reports for each customer or all of their invoices or contracts for each customer so this option is
going to be available if you have numbers in there number values or amounts in there then we click on
next and then it's asking us do we want to have the orientation as portrait or landscape and you
can tinker with that and check it out but in our case and so we do not have as many fields it okay
to have the sported then we click on next again and then we have to give a name for this report
so in our case we'll say customers and contracts or you could say contacts by customer now you can
change this later as well but it's important to give meaningful names as you start with a report
then click on finish and this is how our report will look like so this is kind of a preview we
need to kind of adjust it a little bit notice we have these amounts or dollar signs here and all
this type of stuff so we'll need to customize this in a little bit so for now I'm going to close it
here to customize this further we go under view and if you wanted a preview of it at this stage
you could do a preview but that's not ready yet you know this is not quite ready with all this
stuff yet we can go back here close the print preview option go under layout view and we can
adjust the layout of this a little bit so notice how we have the amounts here we could actually
move this farther to the left so these will be the amounts and then we can resize this field then we
can go and get also the description of what the contract is and then resize that as well how we
want it to look then also resize other objects that you'd like here now notice we'll assume
here we know what the contracts are and such but notice that they're the actual labels are here on
the right hand side I'm going to delete them for now just so that for simplicity because we kind
of know the amount here and also we know what the contract was now this over here this is supposed
to be the total for each contract because we have chosen the totals so this you might want to move
it also right below the amounts area and then you know this you have here summary for customer
ID and then 30 year records and such you could change the wording here now this is that some
you can move that field closer and instead of just some you can say total and basically just
the formatting any way you want remember you can also apply themes for this to customize it so it
looks slightly fancier without you having to waste your afternoon with us and then if this
is not very useful in your case you can simply delete that specific field here and then also
remember that you can apply here the formatting so let's say I wanted the formatting to be something
like the previous cells here to remove this border and notice you can go under format take the format
painter apply it to another field and notice it's apply so that's an easier way to apply
the formatting and again you can take the time to customize this and make it as fancy as you want
of course you can also adjust the labels here in the very top you could the format that differently
as well and let's assume that you wanted these labels here on the top you want them bold you can
again format them any way you want let's say that you want it also the actual customer information
just the customer data a certain font or color or whatever you could format it however you want and
it would make it more visually pleasing so this is one way to customize this using the layout view so
if I close this and save the changes by the way you have to save the changes in Microsoft Access
only when you're changing the design of something otherwise it will save the data automatically into
your table so into your forms so now if I open this up again this is what it will look like
if I go to the print preview this is what it'll look like again for the print preview not bad you
have the customer 11 over 11:15 here whatever that number is and notice you have all their contracts
and also the total for each contract here and you have also the footer for this page for this report
now if you wanted to make this and make more changes in a granule format and such and go into
more detail for tweaking the formatting of this you can also use under if I close here the Print
Preview you can use the design view and under the design view you can adjust here additional
components for example the contact amount here notice it's not fully lined up I could adjust the
size of it that adjust how much space is between the amounts and contacts and the total here and
format certain things a different way and let's say between each customer I wanted a little bit
more space I could adjust the spacing after each record and so on now if I go and save this and run
it again by double clicking on it notice there is a little bit more space between each customer and
the total and the prior customers information so that's one way that you can customize this
and tweak this further as you are working with the reports and such and we learned about this
a little bit in a previous video sometimes you might want to add a new field to the report so for
example we have this report here but for whatever reason we wanted to also know here what the actual
contract ID is whatever reason is part of this report so instead of us recreating the report from
scratch we could simply add one more field to this report to this existing report so to add the field
you could either go here on layout view and then under this area right here add an existing field
you could do it from here and you could choose to add so right now it's showing us the fields from
the customer table however we could choose all tables and we want it under the contract table
and we want it let's say the contract number and we want that contract number at the end of the
contract information area or wherever you need it so we could drag it from here if you actually put
it in the very beginning and place it basically wherever you need it wherever you want it I'm
going to delete the little label for it on the top for now so that we keep our report slightly
cleaner adjust the size of it accordingly how you want it change also the font for it and that's one
way to display it in there using the layout view the other way to display this field in there to
add this field and this is what you'll probably use in most business environments is by using the
design view so if I go here to my report I'll close it for now open up the report again and
go under view design view or I could right click on it and choose design view that's another way
to do it now at this point you notice these are my existing fields I have the contact and the
amount I have the contract type however let's assume that right next to contract type I want
to add another field in this case I go here under add the existing fields and I could add the field
the same way that I showed a moment ago in there add contract number over here I want to delete
the label for it unless you have a use for it at this point in this view and probably don't need
the number to be that long and then that field at this point should have been added so if I close it
and run it I should have the contact number at the end of each contract for each customer here
so at this point let me explain how this data is actually linked to the and how you control the
linking of each field to the actual table because the data from the report is actually pulled from
the tables it's not really stored in the report it's actually just at any time you're under the
report its querying it's looking up the data in the table and it's displaying whatever the
criteria is that you have specified go here under the design view and if you go to any of these
fields or the data fields here let say contract type or contact amount and such you can select the
field and then just go under the property sheet property sheet it's going to do is it's going to
bring up the property details for this specific field that we just selected and then here it's
telling us how it's going to format it and all that type of stuff and it's the contract amount
that we have selected currently it's good we could select any of the fields here we'd want to from
this report and we could customize here any of the details how we want this to be formatted basically
so this is just a formatting currently for this item we're controlling how the formatting is going
to display for that value so far however if we go here under the data tab and the control source is
where is this linked to it's telling us what the source of the control to what table and what field
is it picking so in this case it's actually going to the contract amount from the contracts table
and if you click on the drop down if for some reason you need to link it to a different field
that you have selected initially in your report you could just pick it from here or if you want to
link it to a completely different field that does not show up over here under the list of fields for
your current report you can click on these three dots and then go under your data you have opened
and you got to link it to a different field within one of your tables so for example under contracts
here if I wanted to link it to the date or to another one whatever it may be that I want it
simply select it from here and then click OK and then run the report again and it's going to link
it to a different control source from a different field on that table that you select so that's how
we can create a report from multiple tables and also group the results and create a calculated
field for the amounts within each order for each customer and group those orders together
so that's one way to do that so if you want to further customize this report and add or change
the order of those fields and such you can simply drag those fields by going here to the design view
so if you're here under design view and let's say we wanted to change the order of those fields you
can simply shuffle the fields around here so this will put the contract number in the beginning you
can resize this how you want it the amount now it'll be in the end also the total we'll put
the total on the right-hand side at this point right below the amount then you can readjust the
spacing between them and now if we close it save the changes run it again you know this we have
the number in the beginning we have the actual contract information and then we have the amount
on the right hand side if we wanted to further customize it in this case such as formatting
and such we can go here under view layout view and tweak this a little bit more use a format
painter and click on the destination cell and we'll apply the formatting of the previous cell
that you have selected now if we close it save the changes run it again now it should have been
readjusted the layout of it if you want to see a preview of it click on print preview and at this
point it will show up like this of course you can customize this stuff and make it as fancy
as you would like you can make the reports very complex it's very customizable basically
[Music]