|
Exercise Databases 4-3: Computers Today
What you will do: |
Link tables
Create data entry form with subform
Create an Option group
Create report calculating totals
Create report with subreport in each record |
Start with:
Access open with no databases open,
resource files
The fictional company Computers Today publishes several
magazines/newsletters. You will create some forms and reports about the
subscriptions to their various publications. This is a much-simplified
database for managing such information! We will not deal with credit card
numbers, or special prices for multiple-year subscriptions, or shipping addresses that are different from customer's
address.
Computers Today needs forms to enter customer info and data about each
order and what items were ordered. Computers Today can use reports to answer a number of questions, like:
- How many subscriptions to which publications did we get for each
month? Altogether?
- What is the total price of subscriptions sold?
- How much commission did each salesman earn?
Computers Today uses two databases to manage their subscriptions- one
for the actual data tables and one for the queries, forms, and reports.
There are two advantages to splitting a database in this way.
- Customizing: Different people or different departments
can create their own forms and reports based on the same database of
tables, without cluttering up the database for everyone else.
- Editing: It is easier to transport the smaller database
of forms and reports for editing. The bulkier database of tables can
stay in one place and keep collecting new data while a database
designer works on updating/revising the other database.
There is one important disadvantage to splitting your database this
way.
- Moving: If you move one of the two databases, your links
will break. The tables can be re-linked, but you must know where the
database of tables is!
Link Tables
- From your resource files, copy the file
computerstoday-data.mdb to your databases
project4 folder on your Class disk.
- Create a new blank database named
computerstoday-ex4-3.mdb.
- Link to all of the tables in
computerstoday-data.mdb from the new database.
-
Check
the Relationships.
Form with Subform Containing a Subform
To completely describe an order, you need information on the Customer,
on the order as a whole, and on what individual items were ordered.
That's why there are three tables for this info and why you need 3 forms.
Once you have the basic forms, you modify them to use as subforms. So the
actual form that a salesman would fill in will have a subform inside a
subform! This offers many opportunities to make a mess!!
(In the interest of preserving the sanity of students and their
teachers, I have omitted much of the detail that would actually be needed
to do real orders of this type.)
- Create a form for the Customers
table information, named Customers.
- Include a Form Header labeled Customers and Orders.
- Add the logo icon, computerstoday-logo.gif, from the
resource files to the left side of
the Form Header.
- Create a form for the information in the
Orders table, named
Subform-Orders.
-
Use an Option group for the SalesmanID. A wizard sets this up. The
names and ID numbers of the 4 salesmen are in the Salesman
table.
Note: New salesmen will not be added automatically to an Option
group. You will have to revise the form yourself.
- Use a List Box for the Payment Method
- Create a form for the information in the
OrderItems table, named
Subform-OrderItems.
- Show the OrderItems in Continuous Form view.
- Use a drop list for the ProductID to show the name of
the publication while storing the ProductID number.
- Use a drop list for the number of years the subscription is
for. Possible values are 1, 2, and 5.
- Do not show the Order ID or the OrderItemID in this subform.
- In the Detail section, add a control that calculates the total
for the item ordered, which multiples the price by the number of
years subscribed.
- In the properties of the Detail section, set Keep Together to
Yes.
This will keep the customer record together on one page.
- In the Form Footer, add a control that calculates the total
for the order.
(This will be a sum of calculated controls! How does one do
that??? Do you remember?)
Note: To display this kind of total with the order info on the
Subform-Orders, you must do the calculation in a query underlying
the form because you cannot sum calculated controls.
- Add the Subform-OrderItems to
the form Subform: Orders as a
subform.
- Add the Subform-Orders to the
form Customers.
Size the controls and the forms so that there are no horizontal
scroll bars. That can take a lot of tweaking!
You may arrange and format your form to look quite different from the illustration below.
This is just a suggestion. One alternative would be for all customer
info to be at the top with Order info below it and the items ordered
at the bottom with the total for the whole order.

- Check Print Preview. If necessary for your layout, make changes
that will let the form fit within the width of the page. You may
need to change Page Setup to use Landscape
orientation or perhaps change the margins.
What happens with customers who have more than one order?
Each order is on a separate page. The Order subform does not
include the customer's name or ID number. That's fine for form view, but
possibly a problem for printed forms!
- Add your name to the Page Footer.
If you wish, you may create a more printer-friendly version by
removing the background colors from each section before
doing the next step.
-
Save
your changes.
-
Print the 2 pages for CustomerID #3.

The Form Header does not print with this customer. It prints only with the first record. For forms that
you will be printing often, it may work better to put such title
info in the Page Header or even in the Detail section.
Report: Counting Orders
- Create a query named QOrdersbyMonth that
will be the source for a report.
- Use the tables Orders,
Customers, and
Salesmen.
- Include all of the fields from Orders.
- Include the name parts from Customers and from Salesmen.
- Create two new fields, CName and
SName, that concatenate the name parts,
last name first, for Customers and Salesmen.
You will have to include
the name of the table in the concatenation expression. Both the table
of customers and the one for salesmen use the same field name for the
parts of a name. Not good choices!
For example, instead of using [LastName] in the expression for the new
column, you must use [NameOfTable]![LastName].
Be careful not to introduce any spaces into the expression!
- Create a report named Orders by Month and Year,
based on the query you just created.
- Group the orders by Year and then by Month. Show group headers
and footers.
(You will use the same field but different Group On values.)
- Sort the orders by OrderID after the
groupings.
- In the Year header, add a text box labeled Year which has as its
control source the expression =DatePart("yyyy",[DateReceived])
This will display the year value that the Sorting and
Grouping dialog is using.
- Similarly, for the grouping on the month, use the same
expression but change "yyyy" to "m". The month
number will show. The DatePart function will not show the
name of the month.
- Count the number of orders in each month and show the total in
the group's footer.
- Count the number of orders for each year and show the total in
the group's footer.
- Count the number of orders for the whole report and put the
total in the Report Header.
- Add your name to the Page Footer.
- Create two columns, going Down and then Across.
- Make adjustments so that the report fits on 2 pages.
-
Save
your changes.
-
Print (2 pages).
Your report may look a lot different from the illustration, which
is just a suggestion for formatting.

Report: Summary only
-
Copy the report and rename it Orders by Month and
Year, Summary
- Remove the details, leaving only the group and report totals.
- Change the title to Orders Summary.
-
Save
your changes.
-
Print (1 page).
Your summary report may look much different from the illustration,
which is just a suggestion.
Report: Group Totals of a Calculated Value
Computers Today wants to know commission paid to each salesman. You will
have to calculate the total of sales and multiply by the commission rate
for each salesman in the underlying query, otherwise you cannot get the totals you want in
the report. Remember the problems with trying to add up calculated values!
- Create a new query to be the record source for the report. You
will need a full name for salesmen. You already did that in another
query, so you might as well start with that one and adapt it.
- Copy the query QOrdersbyMonth
and paste it to the Database Window. Rename it as
QSalesman
Commissions.
- Delete the 3 fields about customers and delete the Customers
table from the query.
- Add the CommissionRate field from
the Salesmen table to the grid.
- Add the tables Products and
OrderItems to the query.
- Drag the fields SubscriptionLength
and PricePerYear to the grid.
- Add a calculated field named ItemPrice to calculate the total
paid for each subscription.
-
Create
a new report named Salesman Commissions
- For the source for the report, use a query
QSalesman Commissions.
- Do not use AutoReport or Report Wizard.
- Group on the full name of the salesmen, sort ascending, and show the Group
Header.
- Reduce the height of the Detail section to zero. You only need
the group header for this report.
- In the Group Header, add the following controls:
- Control to add up the ItemPrice values.
This is the total amount of sales by the current salesman.
- Name this control TotalSalesman
and edit its label to read just Total:.
- CommissionRate. Edit its label to read
Commission Rate (%):
- Control to calculate the amount of commission.
(Multiply the rate times the amount of sales and then by 0.01 since
the values are supposed to be percentages. That means that a
rate of 9 is actually 0.09).
- Move the labels for the new controls to the Page Header.
(Cut and Paste and drag to new position)
- Format all of the controls that are dollar amounts as
Currency.
- Add a label that shows your name in the center of the Page
Footer.
-
Save
your changes.
-
Print (1 page).
Your report may be quite different from the illustration.
Report with Subreport
To the report you just created, you will add a subreport that will list
the orders for each salesman.
-
Create a new
report named Subreport-OrderItems
that will be a subreport for each order.
- Include the OrderID.
- For OrderID, change the property Hide Duplicates to
Yes.
- Show the name of the publication ordered and not its
ItemID
- Include the price and number of years subscribed.
- Show the calculated total price, ItemPrice, for each subscription.
- Create a new report:
- Copy and paste the report Salesman
Commissions to the Database Window.
- Name the new report, Salesman Commissions,
with subreport
- Open the report Salesman Commissions,
with subreport in Report Design View.
- In the Sorting and Grouping dialog, add a grouping on
OrderID and show just the group header
and set its Keep Together property to With First
Detail.
- In the new group header, add a subreport control using the Subform/subreport tool
from the Toolbox bar. Drag to create a control that is about as wide
as the controls in the SName header.
- In the wizard for the subreport, choose:
- Use the existing form/report
Subreport-OrderItems.
- Accept the first suggestion for linking
- Accept the default name.
- Check the Print Preview. Make any adjustments/corrections
needed.
Your document may look different from the illustration but all the
same parts should be there.
-
Save
your changes.
-
Print (2 pages).
Your report may be quite different from the illustration.

Export
- Export the report Salesman Commissions,
with subreport to your databases
project4 folder on your Class disk, using the default names, in
the following formats:
- TXT
- RTF
- XLS
- HTML
- Snapshot
-
Save the report as a Data Access Page to your
databases project4 folder on your Class disk. Accept the
default name.
- View each of the new files in an appropriate program.
Any unexpected results??
TXT:

RTF:

XLS:

HTML:

Snapshot:

Data Access Page:
|