Optus recharge card product optus and royal call

School of Business

Invoice

Tel Pacific is a registered trademark of Tel Pacific Limited.

Telstra recharge card is a product of Telstra, Optus recharge card is a product of Optus and Royal Call, Lucky Minutes are

It is best if you:

 Read through the entire assignment before you commence work;

Before you commence work on this assignment, it is strongly suggested you work through one or more of the on‐line tutorials at http://office.microsoft.com/en‐us/access‐help/CH010372755.aspx to become familiar with Access 2010. This website has free on‐line courses showing you (among many other things) how to create tables (the basic components of a database) and when to use Access and when Excel is to be preferred.

Assignment:�Background�information�

In every third week of the month Mr Mudusu tallies all his sale invoices and decides on the cards and their numbers required for the next month. This information is used in raising purchase orders. A sample tally report is shown in Figure 2.

1.Designing and creating the two basic tables for the application:

 A Customers table, detailing the Customer Account Number, Customer name, and customer contact details.

5.When the basic system described in points 1‐3 above is working, a new form is created using the queries in point 4 above to resemble the pre‐printed invoice shown in Figure 1.

6.Generating a report similar to the one shown in Figure 2 with the help of queries.

 Explain how you created and formatted the required report, step‐by‐step. Include a screen shot of the report in your write‐up.

 Mention the problems you experienced with this assignment (you will experience plenty) and explain how you got around them; and

Figure 3: Launch MS Access and Click to create a database

Creating�Tables�

To start with, this retailer management system application requires Customer,Card, Sale Header and LineItem tables. It is suggested that you use a consistent naming method for the tables; commonly a Customer table will be called tblCustomer and Card table tblCard. Good discipline in these matters makes it easier to find parts of the application quickly as it increases in size.

MS Access will prompt you for a table name. Enter “tblCustomer” as shown in Figure 7.

Figure 7: Naming tblCustomer

Figure 8: All Four Tables Created

Notice that all tables are closed. You can open any table by double clicking on the table name. Remember to work with one table at a time and if you don’t need it just close it.

 Card Name;

 Card Face Value;

 Change the View to Design View

The ‘Autonumber’ data type assigned to CardID means that MS Access allocates a unique

number to each card (just as UB allocates a unique student number to each student). Card name

 Set the field properties.

 Close the table. (By clicking the “X” at the right hand side of the table.)

Figure 11: tblLineItem in Design Mode and its Attributes

MS Access is a relational database. It divides the database into several tables to reduce the data redundancy. Some fields might appear in more than one table. For example, the field “CustomerID” is used in “tblCustomer” and in “tblSaleHeader”. Moreover, “CustomerID” is the primary key of the “tblCustomer”. When a customer comes to OnlineBuys for the first time their details will be entered into the customer table and a unique ID is allocated. Later, any purchases made by that customer are entered into the “tblSaleHeader”. That means the CustomerID in “tblSaleHeader” can only be from “tblCustomer”. This can be achieved by defining a relationship between the two tables by using “Look up Wizard” data type.

Creating�a�relationship�between�tblSaleHeader�and�tblCustomers�

 The wizard will guide you through a sequence of steps as shown below.

Figure 20: Lookup Wizard‐ Enable Data Integrity and click "Finish"

Figure 21: Lookup Wizard‐ create relationship and save the table

Testing�the�relationship�

Creating�the�rest�of�the�relationships�

1.Create a relationship between “SaleID” in “tblLineItem” to “SaleID”in “tblSaleHeader”

Figure 24: Relationships between all tables

Enter the data into the customer table – (tblCustomer) as shown in Figure 12. Make sure you enter your own name, and your partner’s name, as the contact for two of the new customers. 7 customers in total must be in your final submission.

 Enter some data into the sale header table – tblSaleHeader similar to that in Figure 26. Notice that Sale ID is inserted by the system, Sale Date will be a date selector and CustomerID will be a drop down box that shows the customer information from the customer table (tblCustomer).

Figure 26: tblSaleHeader showing current sales

Figure 27: tblLineItem data entry

Figure 31: Form Wizard‐ Layout Selection

Creating�Line�Item�subform�
The limitation of the form shown in Figure 33 is that it only updates the data in tblSaleHeader. In order for it to update both tblSaleHeader and tblLineItem a second form is needed. From Figure 34 onwards the creation of this second form is shown; also shown is how the two forms are merged into a single form, so that our data entry system updates both tables at the same time.

Close the TimeSheet data entry form and Launch the ‘Form Wizard’ again, as shown in Figure 28.

Figure 35: Specify "Tabular" so that multiple rows appear on one side

Figure 37: the form to update tblLineItem

Notice that the values you have entered in the data entry section automatically appears in the form. Don’t worry about them, just close the form.

Figure 38: Sale data entry form in Design view stretch the form as required.

After the form footer has been dragged down, and the form made wider by stretching the side out, the tblLineItem form can be dragged onto the space above the ‘form footer’ as shown in Figure 39.

Data�entry�using�the�forms�

Before work is commenced to make this form look better, and to be more efficient, it needs to be verified that it will correctly update both the tblSaleHeader and tblLineItem. Put the form in ‘Form View’ (that is, not design view) and enter several records as shown in Figure 40 on the following page.

To enter a new record, you must move to the end of the file by using the >* navigation arrow at the

New records added

Foreign key updated