Customer Invoicing:
A Database Management System Case
Using Microsoft Access
INSTRUCTIONS FOR STUDENTS
Purpose
The purpose of this case is to reinforce database concepts that we have
acquired through in-class exercises, using Microsoft Access. When you finish
Parts 1 and 2 of this project you should feel comfortable with Access and the
basic concepts of a relational database. Hopefully, this exercise will give you
the confidence to build your own relational database for information that is
relevant to you!
This project will guide you through the process of building a database invoice system.
This process includes the following:
1. Create five relational tables.
2. Establish relationships among the tables.
3. Populate the three master file tables.
4. Create an invoice form for sales transactions.
5. Use the invoice form to input sales data and thereby populate the two
transaction file tables.
6. Use the invoice form to display and print invoices.
Typographical Conventions
● Bold indicates text that you type. Bold is also used to indicate a menu item or
check box that needs to be selected. For example, to cut and paste a text
passage, you should highlight the text, select Edit ∙ Cut from the menu bar,
then move your curser to the location for the paste and select Edit ∙ Paste.
● Double question marks (??) mean that you need to figure out for yourself the
appropriate entry or action.
● “. . . etc.” is used when a similar process or set of steps has been previously
explained. When you see “. . . etc,” you should follow the processes described
previously for other entries and actions that are similar.
● For field names in the Access tables, ALLUPPERCASE letters are used for
primary keys and MixedCase for nonkey fields.
● Notes on Access techniques, database concepts, and accounting software
integration are included in boxes.
Page 1
Page 2
Creating an Invoice Form for Entering Sales Transactions
Begin
● Load Access and select Blank Desktop Database.
● To the right of your screen, you will see a box that asks for your “File Name” –
this is the name that you will use to save your database. Click on the folder to
the right of the box to select the drive and directory location where you want to
save your file. Type in a file name that includes your first and last names, e.g.,
SuzanneSmith.accdb. Click on Create.
● Make note of your file location so that you can find your file again at a later
date.
Notice when you are on the main window in an Access database (DB) you can create
the following objects:
● Tables
● Queries
● Forms
● Reports
● Pages
● Macros
● Modules (programmed procedures)
Understand Information Needs
If we were starting a new company, one of the first things we would do is hire
employees, so we would need to record information about these employees. Next,
we would purchase inventory items. Then, we would obtain customers and make
sales to the customers using invoices to document the sales transactions. In an
accounting information system database, all this information is recorded in the five
tables shown below.

Page 3
Most of the detailed contents of each table are shown in Exhibit 1 and will be
explained more fully as you complete this assignment. In the next section of this
assignment, you will begin the process of creating the five tables.
Page 4
Create Table Structure
Before you can enter data in a table you have to create that table’s structure.
● Under Create Tab, select Table Design from the Tables ribbon. (A table in
datasheet view will be created by default the first time you create a new
database.)
Using the Table Design View, you will create the five tables described above. Please
try to use the names and abbreviations as shown in the following sections to avoid
confusion and to fit in the space provided on the invoice form.
Data Structure
In the design view, you will be creating the database’s table data structure, also
called record structure or internal level schema. The data structure can be viewed
as a component of the data dictionary. (For more information about data structure,
see reference materials such as Heagy et al., 1999, pp. 10-19, or Bagranoff et al.,
2008, Chapters 13-15.)
Design tblEmployee
The first table structure you create is for the employee table. Enter the fields and attributes as
described below.
First Field:
Name: EMPCODE
Make EMPCODE the key field by clicking on the key icon in the
menu.
Data Type: Short Text
Data Type
An important component of data coding is determining the data type. For
EMPCODE we use short text because the employee code includes an
alpha prefix (the letter e). See Exhibit 1. For more information on data
types, use the Access help menu and search for field data types. In this
project you will be required to use three other field types: number,
currency, and date/time.
Field Size: 6. This size will accommodate the six-digit employee code.
Caption: Employee Code. The caption property provides an alternative name
for the field. This alternative name, or alias, appears in Access reports
and forms. The captions are provided for your benefit, and you should
Page 5
use names that are descriptive and that you will recognize and
understand.
Required: Yes. Because EMPCODE is the primary key, the Required attribute
should be set to yes. Setting the Required attribute to yes provides an
internal validation control. When entering data, Access will not allow
you to go to the next record without entering a value for EMPCODE.
Allow Zero Length: No. It would be possible to have an attribute required, but
allow zero length if a field may not be applicable to a particular record.
However, you do not have this situation in any of your tables.
Indexed: Yes (NO duplicates). Primary keys must be indexed so Access can
perform searches for the indexed values. Indexing is not recommended
for nonkey fields. You cannot allow duplicates for EMPCODE because
this indexed field is your primary key. (You may reference an accounting
information systems text, such as Heagy et al., 1999, pp. 10-6 through
10-9, or other supporting materials regarding purpose, advantages, and
disadvantages of index files.)
Index Files
Every time you tell Access to index on a field name, Access creates
an index file to increase the speed at which records in the main file are
sorted and accessed. This index file has the same number of records as
the main file but only two fields—an index key (which is the field you
told Access to index on) and the record address. Each time you add or
delete a record in the main file, all its related index files also are
updated because the index file has to stay sorted on the index key. If
you do not expect to sort frequently on a particular field, creating an
index file is a waste of resources. You can sort on any field in Access
whether or not you have an index file for that field. (See Heagy, et al.,
1999, pp. 10-6 through 10-8, or other reference material.)
There are several other table structure settings, e.g., Unicode compression. You may
ignore these settings as they are not necessary for this introductory case.
Second Field:
Name: EmpName
Data Type: Short Text
Field Size: 20. Use your judgment in setting field sizes. 20 to 30 characters is
common for last names
Caption: Employee Name
Required: Yes
Allow Zero Length: No
Indexed: No. You may index nonprimary keys, but it is not necessary. And if
you do index them, be careful when you decide to allow or not allow
Page 6
duplicates. If a nonprimary key is a foreign key on the many side of a
one-to-many relationship, then you MUST allow duplicates, because
there may be MANY of this attribute for each item in the related table.
Third Field:
Name: CommRate
Data Type: Number
Field Size: Single
Format: Fixed
Decimal Places: 3
Caption: Commission Rate
Default Value: Set the default value to zero (0) so if no entry is made, the
commission rate will default to zero.
Validation Rule: Assume the minimum commission rate for the company in this
exercise is zero because middle managers sometimes make sales, but
they receive no commission. The maximum commission rate is 20%.
Therefore, set the validation rule to >= 0 And <= .2.
Validation Text: Valid commission rates are 0% to 20%. This message will
appear if you attempt to enter a commission rate outside the valid
range.
Required: No
Indexed: No
Note regarding the CommRate field: For data type, you could use either
Number or Currency. If you use Number, you should use Single field size
to reduce the amount of space and memory used by the system. For a
more in-depth discussion, go to the Access Help Menu and search
FieldSize Properties.
Save the employee table with the name tblEmployee.
The table’s Design View should look like Exhibit 2 when completed.
As a naming convention, we use tbl as a prefix for all tables and frm or f
as a prefix for all forms. This naming convention helps you quickly
distinguish between tables and forms in pull-down menus.
File ⋅ Close.
For the remaining tables, you will notice that not all of the
information is provided. This means that you must pay close
Page 7
attention to the instructions that were provided for the employee
table, and then make appropriate choices for attributes (and how
you define them) for the tables that follow on the next pages.
Page 8
Design tblInventory
Enter the fields and attributes for the inventory table as described below.
First Field:
Name: PRODNO (Make this the primary key field.)
Data Type: Short Text
Size: ?? (Refer to Exhibit 1.)
Caption: Product No.
Required: ??
Allow Zero Length: ??
Indexed: ??
Second Field:
Name: ProdDesc
. . . etc.
Third Field:
Name: SalePrice
Data Type: Currency
Format: Currency
Decimal Places: 2
Caption: Sale Price
Default Value: 0
Required: Yes
Indexed: No
File ⋅ Save (as tblInventory), File ⋅ Close.
Design tblCustomer
Enter the fields and attributes for the customer table as described below.
First Field:
Name: CUSTCODE
. . etc.
Second and third field:
. . etc.
Design tblInvoice
Enter the fields and attributes for the invoice table as described below.
First Field:
Page 9
Name: INVNO
…etc.
Second Field:
Name: InvDate
Data Type: Date/Time
. . . etc.
Third Field:
Name: EmpCode
. . . etc.
Fourth Field:
. . . etc.
Design tblInvoiceLine
Enter the fields and attributes for the invoice line table as described below.
First Field:
Name: INVNO. (Do NOT make this the key field yet.)
Data Type: Short Text
Size: ??
Caption: Invoice No.
Required: ??
Allow Zero Length: No
Indexed: Yes (Duplicates OK). Duplicates are okay in this case because
INVNO is only half of the key for this table. Therefore, we can have
duplicates of the INVNO as long as INVNO is concatenated with
PRODNO.
Second Field:
Name: PRODNO
. . . etc.
Make INVNO and PRODNO the composite key as follows:
Hold CTRL and then click on the row selector for each field. With both rows
selected, move your pointer and click the Key button . Both rows now
should be selected as the concatenated, or composite, primary key.
Third Field:
Name: QtySold
Data Type: ??
Field Size: Long Integer
Decimal Places: Auto
Page 10
. . . etc.
Save as tblInvoiceLine.
Close the file so you are in the database main window.
Establish Relationships among Tables
Now that you have created the structure for your tables, you can establish
relationships among the tables. The ability to make the tables interact is the feature
that makes databases so much more powerful than conventional file systems.
If you haven’t already done so, close out of the table’s Design View, and select
Relationships from the Relationships ribbon under Database Tools Tab
(Make sure you save and close all tables before you establish relationships).
If the Show Table box is not visible, use the right click to open the Show Table dialog
box. Add all five tables, then close the Show Table box. Move tables (click on
heading and drag) into the configuration shown in Exhibit 3, the
Resources-Events-Agents format.
Page 11
Database Concept Note
An REA diagram explains the relationship between all the entities that are involved in
a business process. Entities are categorized as resources, events, or agents, and the
REA diagram describes the relationship between those entities. Resources include
cash, inventories. Events are business activities such as a sale or a purchase. Agents
are persons or entities that participate in events, e.g., employees, customers, tax
authorities.
Each table in the Relationships window of Access represents an entity. The lines
between the entities describe the relationship between the entities. There are three
types of entity relationships: (1) one-to-one, (2), one-to-many, and (3) many-to-many.
For example, one manager manages one department, and one department is managed
by one manager (one-to-one). On the other hand; one customer can have many
invoices (one-to-many) because a customer can make many separate purchases from a
company. One customer can purchase many products, and one product can be
purchased by many customers (many-to-many).
The linkages between entities in the invoicing cycle are shown in the REA diagram in
Exhibit 3. These relationships are built into the Access database based on the join lines
you will add in the next section.
To establish the relationship join lines, drag a primary key to a foreign key, for
example, drag INVNO in tblInvoice to INVNO in tblInvoiceLine. Refer to the tables
(Exhibit 1) and the Table Relationship Screen (Exhibit 3) to guide you. Note primary
key field names are in BOLD UPPERCASE letters.
After each drag, an Edit Relationships window will appear. Check the Enforce
Referential Integrity box. Referential integrity guarantees that a foreign key can never
point to a nonexistent primary key.
At the bottom of the Edit Relationship dialog box, you will see the Relationship Type.
Review this to be sure each relationship type properly describes the underlying
relationship between those two entities. For example, the Relationship Type for the
join line drawn between customer and invoice should be one-to-many.
If this relationship is not correct, then you have made an error somewhere. Check
each of the fields in each table to be sure that the index settings are correct (the
index settings for foreign keys should be Yes (duplicates OK)). If index settings are
wrong, Access may have an incorrect view of the database structure.
You can edit a relationship by pointing to the relationship line and right clicking.
Click Create.
Page 12
Create the remaining relationships by dragging from a primary key to a foreign key as
described above.
File ⋅ Save ⋅ File ⋅ Close.
Accounting Software Integration Note
Without referential integrity, database systems would fail when pointers in one table
could not find a matching row in the related tables. In accounting software, referential
integrity will prevent you from being able to enter a customer name on an invoice form if that
customer is not in the customer master file. For example, in an accounting software package,
you could not enter a customer name on the invoice unless that customer is already in the
customer master file. Referential integrity also prevents you from entering a product code on
an invoice when that product is not already stored in the inventory master file.
Referential integrity also prevents you from deleting or changing records in a primary
table that would result in an orphan record in a related table. Accounting software packages
enforce referential integrity between the chart of accounts and the check register. For
example, assume you entered check #101 and charged the insurance expense account (a debit
to insurance expense, account #527). Then assume you tried to go the chart of accounts and
delete the insurance expense account. Referential integrity would not allow you to delete the
insurance expense account, because such a deletion would leave check #101 with a debit to a
nonexistent expense account, i.e., an orphan is in the check register table.
You can test referential integrity in an accounting software package by attempting to
delete one of the frequently used expense accounts, e.g., insurance expense. The system will
not allow you to complete the expense account deletion because the deletion would leave an
orphan in the check register table.
Page 13
Cardinality Notes
Note how Access depicts the cardinality relationships.
∞ 1
Microsoft Access (2000)
This relationship means that one given customer can have many invoices. But each
invoice, can have only one customer.
Here are three alternative ways to depict relationships:
(Boockholdt, 1999)
* 1 (Heagy, et al., 1999)
(1, 1) (0, n) (Romney & Steinbart, 2003)
The Microsoft Access relationship notation is different from that used by many
authors and professionals. For example, Access relationship notations are opposite
from those used by Romney and Steinbart (2003).
Populate Master File Tables
Use the information in Exhibit 1 to populate the master file tables:
● tblEmployee
● tblInventory
● tblCustomer
Do not populate the invoice and invoice line tables.
From the table menu, select the tblEmployee master file table and Open it. You
should be in the Datasheet View. If any captions are truncated, you can widen these
columns the same way you resize Excel or Word columns. Enter data, tabbing from
field to field. Access automatically opens a new record when you tab out of the last
field of a record.
Now that you have seen how to enter data using the table, we will create a user
interface input screen to populate the other two master file tables. Under the Create
Page 14
Tab, click on Form Wizard from the Forms ribbon. Select tblCustomer
and move all fields to the right using the double arrow. Follow the remaining
instructions with default options, and name the form frmCustomerInput. You are
ready now to enter the information on your customers from Exhibit 1. The completed
form should look similar to Exhibit 4. The input form in Exhibit 4 is similar to the input
screen used by accounting software packages for updating master files, e.g., the File
Maintenance screen in Peachtree or the New Customer menu in QuickBooks.
Now create a form to enter the inventory data, following the instructions above.
Create Access Form
You will now create an Access form that serves a dual purpose:
1. A data entry screen to populate tblInvoice and tblInvoiceLine
2. An invoice document that can be printed and delivered to customers.
In this exercise, you will create the invoice in rough form. Initially, our goal is to
demonstrate that you have the skills to integrate database concepts and create an
invoice form similar to the invoice form used in accounting software packages.
However, please practice your skills to improve the appearance and functionality of
the form.
Multitable form: main form and subform
Many forms involve more than one table. The invoice form you will create in this exercise is
an example of a multiple form because some data come from tblInvoice and its related
tables—tblEmployee and tblCustomer—and other data come from tblInvoiceLine and its
related table—tblInventory.
The main form is usually the one side of a one-to-many relationship. In this case, tblInvoice is
the one side of the relationship between the invoice and the invoice line tables (See Exhibit 5).
Although five tables are included in this exercise, remember the purposes of designing the
form are to populate the invoice and the invoice line tables and to create an invoice document
from these two tables.
Some field names appear in more than one table, e.g., INVNO appears in both the
tblInvoice and tblInvoiceLine. Therefore, it is important that the main portion of the
invoice form derives the fields from the correct table. For each invoice form to contain
unique information, the main portion of the invoice form must derive the invoice
number from the tblInvoice, and NOT from the tblInvoiceLine. INVNO is the tblInvoice
primary key; therefore, if the main form derives the invoice number from tblInvoice,
each invoice form will contain unique information about one specific invoice.
Also, the main portion of the form gets EMPCODE and CUSTCODE data from
tblInvoice and NOT from tblEmployee or tblCustomer. These two latter tables are
Page 15
incidental to tblInvoice. Thus, if your main form tries to derive the CUSTCODE from
the customer table, Access won’t know to which customer you are referring. Here is
an analogy. Imagine you have an invoice in front of you and that invoice is for a
specific customer. However, you can’t see the customer field on the invoice. Instead,
the only customer data you have is the customer master file. With only the customer
master file, you could not determine which specific customer is appropriate for that
specific invoice, unless you can look at the customer number contained on the
specific invoice. Therefore, you must derive the customer number from the invoice
table, or Access won’t be able to tell which customer information should be included
on the specific invoice.
Because the main form will derive its data from the one side of the one-to-many
relationship, the main form information is nonrepeating, e.g., there is only one invoice
number, date, employee code, and customer code for each invoice form. The
subform will capture and display information from the many side of the relationship —
the tblInvoiceLine side. Therefore, the main subform will derive product number,
quantity sold, product description, and sale price from the tblInvoiceLine table. The
subform information may have multiple lines because there can be more than one
invoice line for each invoice number.
1. From the database main window, click on the Create Tab.
2. Select the Form Wizard and select tblInvoice in the pull down list box.
3. Click the double right chevron >> to move all fields into the Selected Fields for
the form. Do not select Next yet. (If you already have, just touch the Back
button.)
Procedural Comment
Step #3 established tblInvoice as the source for the following data: INVNO,
InvDate, EmpCode, and CustCode. If you derive these fields from any other
table, the form will not work properly.
The Access form wizard places fields on the form in the order that they are
selected, so a little forethought about how you want the form to look would be
useful. Accordingly, you may want to complete steps 4 through 7 in a different
order than shown below. Another way to impact the selection order is to
highlight one of the selected fields prior to adding new fields. The new fields
will be added immediately after the highlighted field.
4. Within this same form wizard screen, select tblEmployee from the top-left
pull-down menu, and add EmpName to form.
Page 16
5. Within the same screen, select tblCustomer, and add CustName, CustCity,
and CustState.
6. Within the same screen, select tblInvoiceLine. Add PRODNO and QtySold.
7. Within the same screen, select tblInventory, and add ProdDesc and
SalePrice.
8. Click Next.
Page 17
9. Now that you have selected the fields for the form, you will give Access
additional information to set up the structure and appearance of the form. In
the top-left panel of the Form Wizard select by tblInvoice for viewing the data.
This step is important for defining the overall format of the invoice form. By
selecting tblInvoice, we instruct Access to define tblInvoice as the main
section of the form and tblInvoiceLine as a nested component of the main
form.
10.Click Form with subforms, then Next.
11. Select Tabular for the layout, then Next.
12.Change the title for the form to frmInvoiceMain and subform to
fsubInvoiceLine.
13.Select Open the form to view or enter information, then Finish.
Your form should be similar to Exhibit 6. You are automatically in Form View as
opposed to the Design View. All fields are empty at this time because you have not
yet entered the transaction data. NOTE: You have the main form in the upper half
and the invoice line subform in the lower half. Navigation buttons are at the bottom of
the form. The upper set of buttons moves you through the subform; the lower moves
you through the main form.
The steps above performed join and project operations on the five tables. The join
operation connects two or more tables based on primary and foreign key
relationships. The project operation displays selected information from tables. These
join and project operations are performed by an underlying query that was created by
the Access wizard. To view the query, open the frmInvoiceMain in Design View and
open the form’s properties using the Property sheet icon under Design Tab.
Then select Data tab and click on the build button to the right of the Record
Source. The underlying query is shown in Exhibit 7.
Close screens until you get back to the Design View for the frmInvoiceMain (Exhibit
8). Notice the main form has a header section, detail section, and footer section. The
subform that is nested in the main form has a header section and a detail section.
While in Design View, adjust all information (and boxes that will contain
information) so that all data is viewable, i.e., so that you do not have to scroll to
view any information on the form (i.e., make sure that the customer can see at
least 4 lines of product information. See Exhibit 8.
Page 18
ALSO: Center the headings for each of the data elements (PRODNO, QtySold,
ProdDesc, and SalePrice). Let justify the information that will be entered in the
Product Number and Product Description boxes, and right justify the
information that will be entered in the Quantity Sold and Sale Price boxes.
Save the form and change to Form View by selecting Form View after clicking on
View button.
Enter Sales Transaction Data
Enter the sales transaction data listed in the table below. Be sure to continue
touching the TAB key until you have entered all of the lines for each particular
invoice. To move to a blank invoice, press the ►* button [new(blank) record] at the
bottom left of your screen.
Sale transactions:

Warning: Because invoice number is the primary key for this form, Access will not be
happy if you try to complete other fields in the form first. So always be sure your
curser is in the Invoice No. data field when you start each invoice. If you accidentally
start a new invoice in a field other than Invoice No., use the escape key to exit the
invoice. If escape does not work, you may have to File ⋅ Close. A warning message
may appear that says the data changes will be lost. Answer Yes. Double click on
frmInvoiceMain to begin re-entering data.
As you enter the sales transactions, notice that when you enter employee code, the
employee name field automatically fills. When you enter the customer code,
Page 19
customer name and city fields automatically fill. And when you enter the product
number, the product description and sale price fields automatically fill.
Also notice that when you enter the first sales transaction, before you enter data in
the invoice lines you do not have a vertical scroll bar on the embedded subform.
However, after you touch the Tab key to begin a second line, a vertical scroll bar
appears. The embedded subform occupies a small portion (approximately 8 x 2
inches) of the main form. Therefore, as the subform grows in length, you may have to
use the vertical scroll bar to see all the data on the embedded subform.
Your first invoice should look similar to Exhibit 9, but your invoice will have at least
four lines of product information that may be viewed without scrolling.
Accounting Software Integration Note
In this Access exercise, you are creating an invoice form that is functionally
similar to the invoice form in any accounting software package. For
example, the form you are creating here will have the same functionality as
the Create Invoice form in QuickBooks.
Please visualize the pull down menus (boxes) in the accounting software
invoice form, e.g., the customer field. The pull-down menus represent
linkages to other tables in the accounting software database. The
accounting software will not allow you to add a new customer to the
customer master list from within the invoice form, but will prompt you to
add the customer to the customer master list first (In QuickBooks, you
would see the Quick Add or Set Up the new customer pull-down menu).
This is an example of forced referential integrity. The invoice form you
create in this exercise will be similar: it will reference several tables, but it
will not allow us to edit tables such as tblInventory from within the invoice
form.
Review Your Results
After entering the sales data, save and close the form. Remember, when you fill
in the invoice form, Access populates the invoice and the invoice line tables for
you. To see if this happened, open tblInvoice. The table should contain the data
as shown in Exhibit 1. Close the table and repeat this process for tblInvoiceLine.
Adding Calculated Controls and Formatting Controls
Inserting Calculated Fields
Page 20
The invoice form (frmInvoiceMain) is not complete if you are using it as a printable
invoice for distribution to customers as well as a data input form. To meet both these
functional uses, the form needs to have a calculated total for each invoice line and a
calculated total for the entire invoice. To accomplish these last two steps, open
fsubInvoiceLine in Design View. Maximize the form. If necessary, resize any of the
headings (Label boxes) or the input boxes (Text boxes) so that your form has enough
room to accommodate the calculated totals. See Exhibit 10.
Line Item Total
Add a line item total field to the detail section of the subform as follows:
1. While still in Design View, select Aa under Design Tab and draw a Label to
the right of sale price in the Form Header section. Then select ab| under
Design tab and draw a Text box in the Detail section (a label will be created as
well associated with the textbox, delete the label).
a. Point to the Label box and click to select it. Move the label so it is above
the new Text box in the detail section.
b. If the Label box is not already highlighted, do so. Change the wording to
Extended Amount. If both words are not on one line or you cannot see
all the letters, resize the box.
2. Right click on the new text box in the detail section and select Properties.
Change the format to Currency. Select the Data tab and enter the following
formula into the Control Source field: =([QtySold]*[SalePrice]). Select the
Other tab and change the field name to ExtAmt. Close the text box properties
menu.
3. Save the subform but do not close.
Your Design View for the subform should be similar to Exhibit 10.
Total Amount Due
Add a total amount due field to the footer of the subform as follows:
1. Expand the depth of the footer about 1/4 inch.
2. From the Toolbox, select ab| and draw a text box in the footer.
3. Position the label and text box so they are on the right side of the footer with
the text box aligned under extended amount formula in the detail section. You
Page 21
can move both boxes simultaneously by changing the pointer to a hand with
five fingers extended .
4. Click one time on the label box to change the wording to Total amount due.
5. Right click on the text box and select Properties. Change the format to
Currency. Select Data tab, and enter the following Control Source:
=Sum([QtySold]*[SalePrice])
6. Close Properties.
Your Design View should now look similar to Exhibit 11.
File ⋅ Save, File ⋅ Close.
From the database main window, open frmInvoiceMain. Select Design View.
Your frmInvoiceMain should be similar to Exhibit 12. To observe the Total amount due
field in the subform footer, you must click on the down arrow of the vertical scroll bar
on the embedded subform. Alternatively, you can resize the subform as shown in
Exhibit 12.
Close the design view, then select Form View. The form view should be similar to
Exhibit 13. Save the form. This is your revised form for not only recording sales
transaction data but also for printing invoices to deliver to customers.
Formatting Controls
In Access, each box on a form is called a control. You can place three types of
controls on a form.
● A bound control is linked to an underlying table.
● An unbound control is not linked to any underlying table.
● A calculated control is computed from fields in the underlying tables.
You have already done some minor formatting by moving and resizing some of the
controls, e.g., the label and text boxes.
Open frmInvoiceMain in Design View. Open the form header by dragging the Detail
bar down from the Header bar about ½ inch. If the toolbox is not already open, open
it. In the toolbox, select Aa and then change the font size to 18 using the format bar
at the top of the Access screen. Draw a box in the header and enter INVOICE in the
box, then close the toolbox. Resize and move the box so it is in the center of the
header.
Page 22
The final step in designing your screen form is to change some of the background
color of the invoice. Although the light gray is attractive, you’ll use a lot of ink when
printing the invoices. Place the pointer in the header bar and left click to highlight the
header bar. Select the Fill/Back Color button, and change the back color to white.
Repeat the white background formatting in the detail section. When all format
changes have been made, save the form.
Many more enhancements could be made to your form but are not required for this
assignment. For example, you could add color and graphics images to the form. You
could add a customer list box to display a list of possible customers. Or you could
use a combo box, which combines a text box with a list box. Finally, you could create
command buttons to streamline functions such as .
Page 23
Creating Reports
You can create reports using procedures similar to those that we used to create the
invoice form. In most accounting software packages, a report menu is used to create
reports such as the income statement, balance sheet, customer account balances,
and trial balance. These report menus use report-writing tools that are similar to
those found in Access.
Due Date, Submission Format, and Point Assignment
Check your syllabus for the due date. Remember that you must send your
assignment through Blackboard (under assignments folder).
As a last check before you send your assignment, please check over each of
your tables and forms to be sure you’ve followed the instructions. Also, check
to be sure your forms work properly. Are your forms user-friendly? You might
want to take a little extra time to make the forms look more attractive or to
make them easier to read. Also, you might want to.
References
Boockholdt, J. L. (1999). Accounting information systems. New York: Irwin McGraw-Hill.
Heagy, C. D., Nash, J. F., & Courtney, H. M. (1999). Design, selection, and
implementation of accounting information systems. Mason, OH: Thompson
Learning Custom Publishing.
Bagranoff, N., M Simkin, and C. Strand Norman. (2008). Core concepts of accounting
information systems. New York: John Wiley & Sons, Inc.
Romney, M. B., & Steinbart, P. J. (2003). Accounting information systems. Upper Saddle
River, NY: Prentice Hall.
Page 24
Exhibit 1. 3rd Normal Form Tables
(Upper case denoted a primary key. Mixed case italicized denoted a foreign key.
Uppercase italicized denotes a primary and foreign key.)
Table name: tblEmployee

Table name: tblInventory

Table name: tblCustomer

Page 25
Table name: tblInvoice

Table name: tblInvoiceLine

Page 26
Exhibit 2. Design View of tblEmployee
Page 27
Exhibit 3. Table Relationship Screen
Note: The Resource, Event, Agents, and Bridge Table text boxes are added for instructional
purposes. You will not see these boxes in your Access window.
Exhibit 4. frmCustomerInput
Page 28
Page 29
Exhibit 5. Multiple Form Relationship
Page 30
Exhibit 6. Form View of frmInvoiceMain
Page 31
Exhibit 7. Join and Project Query Underlying frmInvoiceMain
Page 32
Exhibit 8. Design View of frmInvoiceMain*
Page 33
Exhibit 9. Form View of frmInvoiceMain with Transaction Data
Page 34
Exhibit 10. Design View of fsubInvoiceLine
Page 35
Exhibit 11. Design View of fsubInvoiceLine with Total Amount Due
Page 36
Exhibit 12. Design View of frmInvoiceMain with Calculated Fields
Page 37
Exhibit 13. Form View of frmInvoiceMain with Transaction Data and
Calculated Totals
Page 38
Exhibit 14. Final Invoice Form
Page 39