Page |1

Assignment
On
Online Buy’s Retail Management System
By
 Page |2

Executive Summery
Online Buy is an online e-business organization basically selling phone cards & was established

in 2005. From the starting they are usually purchasing calling cards from various Tele

communication companies like Tel Pacific, Optus, Telstra etc. & selling those to individual

customers through their online portal. Nowadays they have entered into the retail market as well

where they are supplying their calling cards to the grocery shops of Sydney city. The Manual

tallying process of invoicing was going good for this retail business till that time when the

number of their retail customer was very small. Presently due to the increased number of retail

customer of Online Buy’s the previous manual tallying system of sales invoicing & reporting is

getting invalid. Following this reality the owner of the company Mr. Mudusu has decided to

invest into a retail management system for his company in order to make a automated

management system for their retail business management which will immensely lessen their

effort for doing those work manually & also make it convenient for them to access right kind of

data in the right time. To create the system he appointed us as the project consultant. The

purpose of creating this automated retailing management system by using MS Access will be

maintaining records of all retail transactions as well as analyzing individual card sale & by this

way making their retail management system a dynamic one. By the end of creating all the

required tables there will be two major outputs for the reporting & analyzing purpose which are

“Customer Invoice” & “Monthly Sales Report”

 Page |3

Methodology
The primary methodology for preparing this report is to using MS Access for creating &

designing all the necessary tables & forms & also using some secondary data for the Ms Access

tutorial & functioning purpose.

Scope
This report preparing provided a wide scope of learning the MS access properly & implementing

the skill in our academic & professional work. It provided the chance of understanding, the

functional aspects of this software & its practical use.

 Page |4

Contents
1.0
Executive Summery 2

2.0 Methodology.........................................................................................................................................3
3.0 Scope.....................................................................................................................................................3
4.0 Introduction...........................................................................................................................................3
5.0 Principles of Database management System.........................................................................................3
5.1 Creating the Tables................................................................................................................................3
5.2 Defining the Fields & Data types............................................................................................................3
5.3 Creating Relationships...........................................................................................................................3
5.4 Data Entry Work....................................................................................................................................3
5.5 Creating Sale Data Entry Form...............................................................................................................3
5.6 Creating LineItem Sub Form & Combining the Forms............................................................................3
5.7 Creating the Report...............................................................................................................................3
6.0Problems Faced Through The task..........................................................................................................3
7.0 Evaluation & Conclusion........................................................................................................................3
8.0 Reference List........................................................................................................................................3
 Page |5

Introduction
Designing a database is critical to proficiently implement a rational database. “A database

management system (DBMS) is a software package with computer programs that control the

creation, maintenance, and use of a database. It allows organizations to conveniently develop

databases for various applications by database administrators (DBAs) and other specialists”.

(Wikipedia, the free Encyclopedia, May 2012, Database management system, viewed on 20

May, 2012, http://en.wikipedia.org/wiki/Database_management_system )

Principles of Database management System

From the time being human kind needed to store various forms of data with the continuous

progress of civilization. In the primitive periods data was stored on cave walls/ Rocks/ leaves

through drawing/writing etc. & then we find books, storytellers, myths which were nothing but

the former non-technological database management. As time evolved, with modern information

technology today this concept has taken a completely new phenomenon. Today, there are

hundreds & thousands of various IT systems around the world to create database systems.

The DBMS may use different design model for creating the database such as relational model or

object model to make the system convenient for the users. The object-based data modeling use

entities attributes, and relationships to present the information found in the required business

process. The Entity-Relationship (E-R) is an example of this kind of model where the relational
 Page |6

data model focuses on the relationships in the records of the business through mathematical

relation finding between the data variables.

Here, we are using MS access for our report presentation which is a relational model of database

management system. Usually big corporations use Oracle & SQlL software’s for these relational

database model but for small & medium companies like Online Buy it is all right to work with

MS Office Access. In MS Access we are required to create separate tables for separate entities,

creating forms & sub forms of the tables, combining the forms in order to create an automated

updating system between the tables, creating quarries & more. MS Access is user friendly, easy

to understand yet very strong software for preparing the relational model for a database system.

There are some common terms used in MS Access in order to creating the tables, both in design

& database view. Primary key, foreign key, auto number, data type is such kind of words.

“A primary key is a field or combination of fields that uniquely identify a record in a table, so

that an individual record can be located without confusion.”

(databasedev.co.uk,2003-2012,viewed on 20 May, 2012,

http://www.databasedev.co.uk/primary_foreign_key_constraints.html )

“A foreign key (sometimes called a referencing key) is a key used to link two tables together.

Typically you take the primary key field from one table and insert it into the other table where it

becomes a foreign key (it remains a primary key in the original table).”
 Page |7

(databasedev.co.uk, 2003-2012, viewed on 20 May, 2012,

http://www.databasedev.co.uk/primary_foreign_key_constraints.html )

So, here the primary key for one table can be a foreign key in another table of MS Access. But, a

primary key is always a primary key in its own table, where it uniquely identifies the record

inputted in the table & marks it. In primary key table every new entry takes a new unique

identity to the primary key. Where we cannot change manually the number of new entries, for

example the customer ID of each customer in customer table of MS Access is defined by the key

automatic way & cannot be created manually from the table. On the other hand in foreign key the

data can be manipulated manually as it situates in another table where it is acting as a foreign key

not a primary key.

“Every field in a table has properties. These properties define the field's characteristics and

behavior. The most important property for a field is its data type. A consists of either text or

numerical characters, but a field whose data type is Number can store only numerical data.

Field's data type determines what kind of data it can store. For example, a field whose data type

is Text can store data that consists of either text or numerical characters, but a field whose data

type is Number can store only numerical data.”

(Microsoft Office, Introduction to data types & field Properties, 2012, viewed on 20 May, 2012,

http://office.microsoft.com/en-us/access-help/introduction-to-data-types-and-field-properties-

HA010233292.aspx)
 Page |8

So, it is quite evident that in text format we can input both numbers & text jointly (alphanumeric

values) where in numbers we use only numbers, for example when input an address with both

text & number attributes we can input that field in a text format due to the fact that numbers in an

address or telephone number is not a number of any math, which means, we cannot add,

multiply, subtract with an address number or phone number which we can do with an amount

number. There are other data types like Date/ Time, Currency, Memo. In Memo we can input

information’s of sizes like gigabytes & in currency we input the monetary values of the data.

Auto numbers are used for those data types which required creating a new number for each entry

automatically. So in auto number, numbers are automatically generated for each record. The auto

numbers are more widely used in case of the primary key’s data type purpose. It is often found

difficult for the beginners to correctly locate which data type to use, especially in case of

numbers & text which can be avoided through a solid understanding of the data types.

Creating the Tables

First of all a database was created with the name of our, which is “Syed Azhar” in the pc folder

than by reopening the database the table creating work get started. We required four types of

tables for the purpose & they are Customer table, card table, Sale Header table, Line Item table.

The steps were like following,

 Clicking on “Create” & selecting Table

 Clicking on “ View” & selecting Design view

 Entering the name of the table as “ tblCustomer”

 Saving the table & closing the table not the MS Access.
 Page |9

 Repeating the above process for creating three more tables named “tblSaleHeader”,

“tblCard”, “tblLineItem”.

 With the four created tables, the database looks like the following screenshot.

Screenshoot1: All table in database.

Defining the Fields & Data types

The steps are like following,

 Opening the tblCard.

 Changing it to the design view.

 Entering the fields & data types manually in the design view.

 Entering the description & setting the field properties.

 P a g e | 10

 Closing the table & doing the similar kind of work for the rest of the tables.

Screen shot: Defining tblCard

Creating Relationships

The steps for creating relationships between the tables are like following,

 Opening tblSaleHeader

 Selecting the “customer Id” & choose the “look up wizard” like the following figure,
 P a g e | 11

 The Look up wizard will guide through a sequence of steps.

 We chose customer Id from the wizard & proceed.

 Then we saved the relationships & checked whether the relationship is created. We see a

relationship is created in between the tblCustomer & tbl Sale Header.

 Following the same process we create relationships between rests of the tables, such like,

creating relationship between sales ID of tbl Line Item with Sale ID of tblSale Header.

 In this way all the relationship between the tables are created & in all relationship view

in the database , the will look like following,

 P a g e | 12

Screenshot: Testing all the relationship created.

Data Entry Work

When the relationships have been created then comes the data entry work. The work was like

following,

 Opening the tblCard in datasheet view to enter the details given in figure 25 of

assignment.

 Open the customer table 7 entering the data given in figure 12 of the assignment 7 also

including our name to make at least 7 customers.

 Opening tblSaleHeader & entering data given in figure 26 of the assignment.

 Entering data in tblLineItem like the figure 27 given in the assignment.

 P a g e | 13

Screenshot: Data entry in the customer table

Creating Sale Data Entry Form

The process is like the following,
 P a g e | 14

 Using “Form Wizard” a form is created for tblSaleHeader.

 Follow the steps of the wizard. In the layout selection we select the columnar layout.

 Naming the form in our name “ Syed Azhar Sale Data Entry”

Creating LineItem Sub Form & Combining the Forms

 From the wizard almost like the same way by choosing customer ID & quantity from the

tblLIneItem through a tabular layout we create the LineItem sub form. Then we need to

combine both the LineItem sub form & sale data entry form.

 For combining purpose first opened the sale data entry in design view & dragged the sub

form below the footer & aligned & designed that accordingly.

 Then there was a data entry in this form using the form view & enter some new record in

order to check whether any joint update regarding to the entry happens in both of the

forms.

 Then some formatting of the tblLineItem form took place & doing this formatting we

used combo box wizard & replaced the Card ID text box with the Card Name.

 Then deleted the navigation tools.

 After that a combined “Syed Azhar sale Data Entry” form created like the following

screenshot.
 P a g e | 15

Screenshot: Combined Sale Data Entry form

 Then with a long process through quarry created “Syed Azhar” invoice through

combo wizard box & other long processes. The Final “ Syed Azhar Invoice”

form will be like following,

P a g e | 16
 P a g e | 17

Screenshots of Syed Azhar Invoice Entry Forms

Creating the Report

Now all works are done, only creating the final two forms of the assignment is not done,

which will be done in the following way,

 For thisw purpose creating another quarry & naming that “qryReport”

 Clicking the report wizard

 Selecting “qryReport” & click Next

 Selecting all the fields from the wizard

 P a g e | 18

 Selecting “stepped” layout from the wizard.

 Giving name “Syed Azhar Monthly Sales Report” to the report & close it. The

report will look like the following screenshot.

Screenshot: Syed Azhar Monthly Sales report.

Problems Faced Through The task

 The first problem faced was in the starting when opening a saved database required an

enabling option to view the data in the database. It was like the following screenshot,
 P a g e | 19

We solved the problem by enabling the option.

 As the assignment was on MS Access 2010 but we used Access 2007 so while working

on the form wizard we faced a problem like following screenshot

 P a g e | 20

We solved it by choosing the Access 2007 option given in the dialogue box.

 Besides these we faced many other problems most of which we solved through internet

tips on MS Access.

Conclusion

The whole process of creating the two major report forms (The Syed Azhar Invoice & Syed

Azhar Monthly Sales Report) for Online Buy company required a lot of tasks performing on

MS Access in order to create an automated interconnected relational model of database by

creating the tables, defining the data types & fields status, Inputting the data with proper data

type, creating the relationships between the tables which enables the automation process & the

interconnectedness between the separate entities. Creating the forms & sub forms under various

tables & combining the forms & sub forms so that any updates taken place in one table create
 P a g e | 21

consequential impacts in the other table. By this way the automaticity of the system prevails.

Through the entire task assignment we developed our MS Access skills & knowledge also

learned key aspects of database management systems & it types. These hands on experience on

MS Access enlightened us with the knowledge & use of business software in SME business as

well as the importance of this kind software like Oracle, SQL in the big corporations in today’s

information technology era.

 P a g e | 22

Reference List
Wikipedia, the free Encyclopedia, May 2012, Database management system,

viewed on 20 May, 2012, http://en.wikipedia.org/wiki/Database_management_system

databasedev.co.uk, 2003-2012, viewed on 20 May,2012,

http://www.databasedev.co.uk/primary_foreign_key_constraints.html

Microsoft Office, Introduction to data types & field Properties, 2012, Viewed on

20 May, 2012, http://office.microsoft.com/en-us/access-help/introduction-to-data-

types-and-field-properties-HA010233292.aspx