Business Applications Of Ms Access Built In Functions To Stock Controls And Budget Controls

MS Access can also help you to easily design functional stock controls and budget controls. In the previous chapter I showed you how you can build functional stock reports and sales reports for your business.

It is very easy but you have to pay attention so that you can understand the concept and apply it in more complex challenges in future.

This is the part 3 and final part of this tutorial chapter. You are advised to first study Part 1 and Part 2 so that you can easily understand everything in this part. Also see the full tutorial course content of this Microsoft Access Tutorial Series.

At the end of this part, I will give you some practical exercises to test your knowledge.

Now let’s begin!

REAL LIFE APPLICATION OF MS ACCESS BUILT IN FUNCTIONS TO STOCK CONTROL

Stock control helps you to manage your day to day transaction and also keeps record of goods remaining in the store room. MS Access simplifies the whole process of setting up a standard, functional Stock control database for your firm. 

The practical examples explained below is meant to help you understand the concept of designing a stock control database in MS Access so as to help you build complex systems. You need to create a form so that your sales agent who is not well grounded in database administration can easily enter data for each sale made.

Stock control makes use of two data tables: one table for recording goods available in your store room at any point in time and another for taking records of sold goods at different periods.

PRACTICAL QUESTIONS ON STOCK CONTROL

1. The two tables shown in the diagram below are used as data source for stock control of Joe-Links Services for the month of January, 2017 designed in MS Access table.

Stock

Sales

a. Join or relate the tables.

b. Generate the selling price using the following scheme:

Mouse= $800, Scanner= $2000, Light pen= $700, CPU= $18000.

Calculate the following:

c. Unit profit on each product.

d. Total cost of each product.

e. Total sales of each product

f. Grand total quantity of all the products in stock.

g. Grand total quantity of all the products in stock.

h. Grand total quantity of all the products sold.

i. Total quantity balance.

j. Quantity balance of each product.

SOLUTIONS TO THE STOCK CONTROL QUESTIONS

Remember to first create the two tables above in an Access database. Save the first table with stock and the second with sales. Join the tables first before you create the form or specify them as data source for your form. 

Study these solutions carefully especially the IIF functions. It might require you to revisit the rules in creating a form and how to join or relate tables in MS Access.

a. Studying the two tables, above, you will notice that there is only one record for each product and also there was no duplication of product names and product ID in any of the columns of the two tables. Since the table should not allow the duplication of any item, then the field PRODUCT_ID in both tables should contain a primary key and should be the join fields.

To add a primary key to these columns or fields, open each of these tables and switch them to design view. Click on PRODUCT_ID and then click Primary Key (it has the logo of a key) located in the Tools group under the Design tab as shown in the figure below.

The relationship between the two tables is a ONE-TO-ONE RELATIONSHIP. Therefore join or relate the tables using the procedure described in chapter two. The figure below shows how the link would look like if you get everything correctly.

Create a form using both table as data source (use the form wizard method). Your form should resemble the one shown in the figure below. Revisit the procedure on how to create a form using form wizard.

Access added the words “stock” and “sales” to the PRODUCT_NAME and PRODUCT_ID fields of both tables respectively to enable it differentiate. So when creating a professional database, avoid using exactly the same field name in join tables. Switch the form to design view. 

For each question, select the text box control from the Controls group under the Design tab. Position your cursor below the last default text box, then click and drag to draw a text box on the form.

b. A nested IIF function is needed in this case since you want Access to insert different items when different amounts (conditions) are entered in the PRODUCT_NAME field of the stock table. 

The last text box already has text SALES_PRICE

Place your cursor inside the syntax box and type the syntax in design view:

=IIf([stock_PRODUCT_NAME]="Keyboard",1200,IIf([stock_PRODUCT_NAME]="Mouse",800,IIf([stock_PRODUCT_NAME]="Scanner",20000,IIf([stock_PRODUCT_NAME]="Light Pen",700,IIf([stock_PRODUCT_NAME]="CPU",18000))))) then press enter key.

c. The label is UNIT_PROFIT_ON_EACH_PRODUCT (don’t forget to draw a new text box)

While the syntax is =[SALE_PRICE]-[COST_PRICE] then press enter key.

d. The label is TOTAL_COST_OF_EACH_PRODUCT

While the syntax is =[STOCK_QTY]*[COST_PRICE] then press enter key.

e. The label is TOTAL_SALES_OF_EACH_PRODUCT

While the syntax is =[QTY_SOLD]*[SALE_PRICE] then press enter key.

f. The label is GRAND_TOTAL_QUANTITY_OF_PRODUCTS_IN_STOCK

While the syntax is =Sum([STOCK_QTY]) then press enter key.

SUM function was applied here because we want Access to calculate the total of all products in stock.

g. The label is GRAND_TOTAL_QUANTITY_OF_PRODUCTS_SOLD

While the syntax is =Sum([QTY_SOLD]) then press enter key.

h. The label is QUANTITY_BALANCE_OF_EACH_PRODUCT

While the syntax is =[STOCK_QTY]-[QTY_SOLD] then press enter key.

Save your form and switch over to form view to see the result. Don’t forget to add the Dollar sign ($) at the necessary syntax boxes. Your form should resemble the one shown in the figure below if you have gotten everything right.

BUSINESS APPLICATION OF MS ACCESS BUILT IN FUNCTIONS TO BUDGET CONTROL

Budget is a planned estimate due to probable future income for a given period of time. A budget could be excess or deficit.

While Expenditure is the planned estimate of the amount to be spent or invested in a sector for a given period of time.

Therefore, budget control is the comparative analysis between budgeted amount and amount spent for a given period of time.

MS Access can help you to set up and manage Budget a professional control system without much stress.

PRACTICAL QUESTIONS ON BUDGET CONTROL

1. The two tables, Budget and Expenditure shown in the diagram below are used as data source for the budget control of Joe-Links Services for the month of January, 2017 designed in MS Access table.

Budget

Expenditure

 a. Join or relate the two tables in the figure above.
Calculate the following:
b. Amount budgeted on finance department.
c. The amount spent on finance department.
d. Amount balance on finance department.
e. The total amount budgeted on finance and Education departments.
f. The total amount spent on finance and Education departments.
g. The total amount balance on finance and Education departments.

SOLUTIONS TO THE BUDGET CONTROL QUESTIONS

As usual, first create the two tables above in an Access database. Save the first table with budget and the second with expenditure. Join the tables first before you create the form or specify them as data source for your form.

Study how the syntaxes of the DSUM functions are combined in some cases. It might require you to revisit the rules in creating a form and how to join or relate tables in MS Access.

a. Studying both tables above carefully, you will notice that in the first table, each of the departments appeared once in the BUDT_DEPT field. While in the second table, some of the departments appeared more than one record.

The BUDT_ID field in the budget table should have a primary key to avoid duplication of any item or data in that field. But it is very wrong to assign primary key in the EXP_ID field of the expenditure table because it will prevent the duplication of any of the items. Such situation is not desirable because some of the departments should appear twice.

The BUDT_ID and the EXP_ID fields should be the join fields. The EXP_WARRANT field should have a primary key to avoid the duplication of its data since each record in the expenditure table must have a unique EXP_WARRANT.

The relationship existing between the two tables is ONE-TO-MANY RELATIONSHIP. Therefore join or relate the tables using the procedure described in chapter two: Part two. 

You may need to first click on Show Table in the Relationships group under the Design tab that appears when you first click the Database Tools tab before you can define another table join if you had defined one earlier. The figure below shows how the link would look like if you get everything correctly.

Create a form using both table as data source (use the form wizard method). When creating your form, use all the default options and add all the fields in both tables. Access will embed a sub form into the budget form. In form view, your form should resemble the one shown in the figure below if you get everything right. Revisit the procedure on how to create a form using form wizard.

Switch the form to design view. For each question, select the text box control from the Controls group under the Design tab. Position your cursor below the last default text box of the form and the expenditure sub form as shown above. Select the text box control and then click and drag to draw a text box on the form.

b. You were asked to calculate only the amount budgeted to finance department only. SUM function cannot help you solve such problems, but DSUM can.

In this case:

Expression is [BUDT_AMT].

Domain is budget i.e. the name of the source table. Make sure you spell it correctly as used in the table name.

Criteria is [BUDT_DEPT].

Specification is Finance.

The label is AMOUNT_BUDGETED_ON_FINANCE

While the syntax is =DSum("[BUDT_AMT]","budget","[BUDT_DEPT]='Finance'") then press enter key.

c. The label is AMOUNT_SPENT_ON_FINANCE

While the syntax is =DSum("[EXP_AMT]","expenditure","[EXP_DEPT]='Finance'") then press enter key.

In this case:

Expression is [EXP_AMT].

Domain is expenditure i.e. the name of the source table. Make sure you spell it correctly as used in the table name.

Criteria is [EXP_DEPT].

Specification is Finance.

Use these concept for other solutions below.

d. The label is AMOUNT_BALANCE_ON_FINANCE

While the syntax is =DSum("[BUDT_AMT]","budget","[BUDT_DEPT]='Finance'")-DSum("[EXP_AMT]","expenditure","[EXP_DEPT]='Finance'") then press enter key.

e. The label is TOTAL_AMOUNT_BUDGETED_ON_FINANCE_AND_EDUCATION

While the syntax is =DSum("[BUDT_AMT]","budget","[BUDT_DEPT]='Finance'")+ DSum("[BUDT_AMT]","budget","[BUDT_DEPT]='Education'") then press enter key.

f. The label is TOTAL_AMOUNT_SPENT_ON_FINANCE_AND_EDUCATION

While the syntax is =DSum("[EXP_AMT]","expenditure","[EXP_DEPT]='Finance'")+ DSum("[EXP_AMT]","expenditure","[EXP_DEPT]='Education'") then press enter key.

g. The label is TOTAL_AMOUNT_BALANCE_ON_FINANCE_AND_EDUCATION

While the syntax is =DSum("[BUDT_AMT]","budget","[BUDT_DEPT]='Finance'")+ DSum("[BUDT_AMT]","budget","[BUDT_DEPT]='Education'")-(DSum("[EXP_AMT]","expenditure","[EXP_DEPT]='Finance'")+ DSum("[EXP_AMT]","expenditure","[EXP_DEPT]='Education'")) then press enter key.

Save your form and switch over to form view to see the result. Don’t forget to add the Dollar sign ($) at the necessary syntax boxes. Your form should resemble the one shown in the figure below if you have gotten everything right.

Budget Control: Result

By now, you should have understood the concept of these MS Access built in functions. These real life examples have been illustrated to help you build more complex real life systems.

In the part two of this chapter, I will be talking on GETTING STARTED WITH MS-ACCESS AND ITS USER INTERFACE. See you there.

PRACTICE EXERCISE UNDER MS ACCESS FORMS

Try as much as possible to attempt these exercises below.

1. Create a database to generate a registration form for INEC using the following field names:

a. Registration Number

b. Surname

c. First name

d. Last name

e. Age

f. Sex

g. Marital status

h. L.G.A.

i. State

j. Phone number

k. Political party

l. Passport photograph

Generate up to 30 records for the table using your own names.

2. The table shown in the figure below is a stock control of Olive Shopping Mall.

Using the data contained in the table above, calculate the following:

a. Unit profit.

b. Total cost price.

c. Total sales price.

d. The total discount of 10% on each sale.

e. The actual selling price.

f. The total actual selling price.

g. The new profit.

h. The total discount.

i. Using IIF function and comparing the TOTAL_COST_PRICE field to the TOTAL_ACTUAL_SELLING_PRICE field, generate a remark using the following keywords where necessary:

PROFIT, LOSS, BROKE EVEN.

Was this tutorial helpful to you?

Inform your friends about this post by clicking the share button below. Comment below if you are hooked up along the installation process.

Also click Here to subscribe for free so that you will get our latest game updates in your email.