James's Database
~~James
La Borde
Access Reports
~ Presentation is Everything ~
As the title says,
presentation is everything. What
good is all the data in the world, if you can not present it in a format
that is useful to the reader. Access
provides a fantastic tool for presenting your data.
For those of you who have been following along with the monthly
articles, you should now have a well-designed database complete with
Tables, for storing the data, Forms,
for inputting the data and controlling what data is viewed, and queries
to narrow the data to a usable subset of data.
All of this is nice but you need to be able to present your data
in a meaningful manner and this is where reports come in.
Access gives you a number of
options in creating a new report. First,
there is Design View, which lets you create your report from the ground
up. This can be an extremely tedious process but sometimes is the
best option. Next is the
Report Wizard. This is one
of the best Wizards Access has to offer.
It will walk you through the steps of getting your data into a
presentable form and then actually put it into that form.
Next is the AutoReport feature.
This will create a very generic report in one of two styles for
you. The Chart Wizard walks
you through using your data to create a dynamic chart.
And, last, but certainly not least, the Label Wizard walks you
through creating sheets of labels from your data.
Let’s take a look at all of these in a little more detail.
Design View
This is a tool best used
until you have some experience with the report creating tools.
When you open a new report in design view you have a completely
blank template. There are
three elements in the basic template, the Page Header, Detail and Page
Footer sections. Additional
Sections are available as well. These
include; Report Header, Report Footer, Section Headers and Footers, and
even sub-reports. Each of
these is fairly obvious where it appears in the report so we won’t go
into too much detail on that.
Many design elements are
available to the developer as you create your custom report.
As with Forms there is a toolbox available that includes most of
the familiar elements. There
is a wand with little stars coming out of it as the second icon on the
toolbox. When this icon is
selected and one of the design element buttons is clicked a wizard comes
up to walk you through using and placing that element.
Let’s look at what you have to work with.
Label
– This element places a static label on your report.
These are used for column headings, and identifiers.
Text
Box – This is the key piece of a report.
It holds your dynamic data.
You can either enter what is to be displayed in this field as
dynamic (i.e. the date) or to include a field from your data source.
Functions can also be used in these fields. What they do depends upon their placement.
Option
Group – This is available but not used.
Access designers appear to have simply reused the Forms toolbox
and this is a remnant of that.
Toggle
Button – This is available but not used.
Access designers appear to have simply reused the Forms toolbox
and this is a remnant of that.
Option
Button – This is available but not used.
Access designers appear to have simply reused the Forms toolbox
and this is a remnant of that.
Combo
Box/List Box – These options allow
you to use data that is in a combo or list box in your data source and
include the linked data rather than the actual data in the field.
Command
Button – This is available but not used.
Access designers appear to have simply reused the Forms toolbox
and this is a remnant of that.
Image
– Allows you to place an image in your report.
Unbound/Bound
Object Frames – These allow you to enter images into your report.
The format used here should match that used in the report.
Unbound should be used for frequently updated items, Bound for
static items or those that are rarely updated.
Page
Break – This one obviously allows you
to create a page break at a specific point in your report.
Tab
Control – This is available but not used.
Access designers appear to have simply reused the Forms toolbox
and this is a remnant of that.
Sub
Report – This allows you to enter a whole report as a subset of
the current report.
Line/Rectangle
– These are drawing elements that allow you to give your report the
look that you want it to have.
Now that you know what each
of the design elements is, you can use them a little more knowledgeably.
In addition to all the various types of objects within your
report, there is a multitude of properties available for each element.
These include font and background colors, shading, visible
properties, background and border styles, and even font size.
An important note here, an additional tool available to Access
2000 users and above is Conditional formatting.
This can also be accomplished through code in Access 97, the key
here is to turn off whatever you do if the row does not meet the
necessary criteria.
Using the Design View you
have to place each one of the elements above and set them as you want
them. This will take time
but can be very rewarding as you can get your report to look exactly as
you want it to. This will
also be necessary to do your report this way if you have too many
elements coming together into one report.
The key thing to remember is that anything you include in the
detail section will be included in your final report for each row in
your data source.
Report Wizard
The report wizard is the best
wizard available in Access. It
will walk you step by step through creating a report.
Let’s follow the steps together.
First, we need to select a
data source. This can be
either a table or a query. Bear
in mind that all rows from whatever data source you select will be
included in your report. Also
on the first page is the selection of the fields to include in your
reports. You can select
them individually or all of them at once.
It is not necessary to include all fields in your report.
Once all of your selections have been made, click Next and you
are taken to the Grouping Page. You
can select up to four levels of grouping.
Grouping takes your data and puts it into smaller groups of like
data. Grouping Options
allows you to select how items are grouped, for text fields you can
select normal, meaning the whole thing must match or anywhere from the
first to the first 5 initials. Dates
can be grouped individually or into months, quarters, years and so on.
The same date field can be selected multiple times and grouped by
Month and then by Day. When you have your grouping options selected, click Next and
you are taken to the Sorting page.
Remember when sorting that the fields you select here will be the
first fields to appear in your report, regardless of the order you
selected earlier. You can
again select up to four fields to sort on and whether you want them sort
in Ascending or Descending order. If
one of your fields contains numeric data, there is also a Summary
Options button available on this screen.
The Summary Options allow you to add a field in a section and
report footers to give you the Sum, the average the minimum or maximum
value. If you are wondering
about why Count is omitted, it is automatically done when you do these
calculations. You are also
given the option on this extra page of including the detail or just
getting a summary of your data. This
allows you to simply view the totals and not the detail records for each
section you have made. When
you have made your selections, click Next and the Layout page.
The Layout page allows you to select the orientation of your
report on the page and one of six layouts.
A nice sample of what the selected layout will look like appears
on the page. Next we come to the Style Page.
There are six default styles and you can create custom styles of
your own. As with the
layout page, an image of the selected style appears on the page.
Finally we come to the Title page.
Here you give your report a title (also the name it will appear
as in the database) and whether you want to immediately view the report
or view it in Design mode.
I highly recommend selecting Modify the reports Design.
When you do this, it immediately saves the report and shows the
layout in design view. If
you select Preview your Report, it runs your query and if something
happens before the report is completed, you will lose your work on the
report. Also in Design mode all of the functions available to you in
the custom design above are again available to you here.
AutoReport
AutoReport as the name
implies automatically creates the report for you.
You select the data source and then when you click on the
selection it goes through and automatically creates your report in the
Style selected (either columnar or tabular).
There are no other options here, everything in your data source
is selected and no grouping or summary is available.
This obviously is extremely limited.
Chart Wizard
The Chart Wizard is another
of the powerful tools available in Access.
It walks you through chart creation.
Let’s venture together through the wizard.
First we select our data source then the Chart Wizard Option.
Our first stop on the Chart Creation tour is to select our
fields. Simply select the
fields you want to include and click next.
The next page can appear overwhelming at first.
It is simply a graphical depiction and a short summary of the
twenty graphs available to choose from.
Once you find the one that best suits your needs and select it,
click next. The Next page allows you to set your graphs options.
Then it is on to the Naming screen.
The naming screen varies from that of the Report Wizard only in
that it gives you the option to include your legend or not.
A pretty simple, but powerful tool.
Label Wizard
The Label Wizard allows you
to select whatever data you so choose from your data source and create a
label out of it. When
selected it will walk you through the creation.
The first step is to select your label.
You can select from number of standard Avery labels.
Next you choose your font, its size, color and weight.
You also have the option of italics and underlining.
As with many Access wizard option pages, you get the handy dandy
sample section of the page to show you what your selections will look
like on your label. Next
comes the construction of you label.
This includes both adding fields from your data source and typing
in characters you would like to appear.
A sorting option page to allow you to sort them to make finding a
specific label easier follows this.
Last of course is the naming page.
This is a very useful tool and I have personally seen it used for
everything from the customary mailing label to creating custom Asset
Tags.
Wrapping It All Up
Reports make your data easier
to present to someone else. Access
gives you many options in creating your reports, enabling you to
customize them to best suit your needs.
Access seems to put its best foot forward in the section that is
designed to show to someone else. The
Wizards in the report section are by far the best in the database.
By practicing with the Report Wizard and doing further
customization with the design tools, you can be making professional
looking reports in no time!
Coming Soon!
*Normalization – A Primer
*Naming Conventions – What are they and What do they Mean to Me? |
,
