Business Applications Of Ms Access Built In Functions To Sales Reports And Stock Reports

There are some real life applications of SUM, DSUM and IIF functions in MS Access. I will show you the practical applications of these functions in sales reports, stock reports, stock controls, budget controls (budgets and expenditures). Please try to practice these business examples on your own.

Don’t copy the syntaxes, rather type them directly by yourself so that you will be conversant with them and master their concepts.

MS Access can also help you to easily design functional stock reports and sales reports. 

This is the part 2 of this tutorial chapter. You are advised to first study Part 1 so that you can easily understand everything. In the Part 1, I introduced MS access form object and their manipulations, customization and also some powerful built in functions in MS Access. Also see the full tutorial course content of this Microsoft Access Tutorial Series.

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.

Now let’s begin!

REAL LIFE APPLICATION OF MS ACCESS BUILT IN FUNCTIONS TO SALES REPORT

Sales report is a report that shows how much a product is bought and how much it was sold. You can easily prepare a functional sales report with the help of SUM, DSUM and IIF functions in MS Access.

Now let me show you how you can achieve it below.

PRACTICAL QUESTIONS ON SALES REPORT

1. Below is a sales report of Joe-Links Services for the month of January 7, 2017 designed in MS Access table.

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

a. Unit profit.

b. Total cost price of each product.

c. Total selling price of each product.

d. Total profit for each product.

e. The discount of 5% on each sale.

f. The actual selling price.

g. The new profit.

h. The total discount.

i. The grand total quantity.

j. The grand total cost.

SOLUTION TO THE SALES REPORT QUESTIONS

You should first create the table above in an Access database, then specify the table as data source for your form (it is advisable to use the auto from method when creating your form). 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. You will type in the syntax for each question in the right box of each text box while the label will be typed on the left box of the text box. 

The syntax starts from the equality sign then rightward while the label ends before the equality sign.

Observe all the rules of creating a functional form.

a. The label is UNIT_PROFIT

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

The figure below illustrates it clearly.

Use this concept for the rest of the questions above. After typing the syntax and label of each question, save your work and switch to for view to see the output. If Access returns any error, crosscheck your syntax and correct any typographical error.

You can reshape the label box and the syntax box separately by clicking on them and then dragging from the resize nodes. 

b. The label is TOTAL_COST_PRICE

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

c. The label is TOTAL_SALES_PRICE

While the syntax is =[QTY]*[SALES_PRICE] then press enter key.

d. The label is TOTAL_PROFIT

While the syntax is =[QTY]*([SALES_PRICE]-[COST_PRICE]) then press enter key.

e. The label is DISCOUNT_5%

While the syntax is =[SALES_PRICE]*5/100 then press enter key.

f. The label is ACTUAL_SELLING_PRICE

While the syntax is =[SALES_PRICE]-([ SALES_PRICE]*5/100) then press enter key.

g. The label is NEW_PROFIT

While the syntax is =([SALES_PRICE]*5/100)- [COST_PRICE] then press enter key.

h. The label is TOTAL_DISCOUNT

While the syntax is =[QTY]*[SALES_PRICE]*5/100 then press enter key.

i. The label is GRAND_TOTAL_QTY

While the syntax is =SUM([QTY]) then press enter key.

j. The label is GRAND_TOTAL_COST

While the syntax is =SUM([COST_PRICE]) then press enter key.

NOTE:

Switch your form to Design view or Layout view and format all currency syntax boxes as currency so that the number in them will have a currency sign ($) by first clicking on the syntax box, then click the drop down arrow in the Number group under the Format tab and select Currency from the options as shown in the figure below.

If you got everything correct, your form should resemble shown in the figure below.

BUSINESS APPLICATIONS OF MS ACCESS BUILT IN FUNCTIONS TO STOCK REPORT

Stock keeping is the act of taking notes of the number of each product remaining in a store. For example, a store room may be used to store different types of products. After each day, a store keeper takes stock of the total number of each of these products remaining after each transaction for the day.

A Stock report is a report that shows the total number of each product remaining in a store room. You can easily prepare a standard stock report with the help of SUM, DSUM and IIF functions in MS Access.

Now let me show you how you can achieve it below.

PRACTICAL QUESTIONS ON STOCK REPORT

1. Below is a sales report of Chinant Wine and Bar for the month of January, 2017 designed in MS Access table.

Using the table above, calculate the following:

a. Total quantity of star in stock.

b. Total cost of each product.

c. The grand total cost of all the products.

d. The grand total quantity of all the products.

e. Total quantity of both Gulder and Malt in stock.

f. Total quantity of both Star and Gulder in stock.

SOLUTIONS TO THE STOCK REPORT QUESTIONS

As usual, first create the table above in an Access database. Save it with stock_report. Then specify the table as data source for your form (it is advisable to use the auto from method when creating your form). 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.

The solution to these questions might be confusing if you don’t understand the concept of DSUM function. To refresh your understanding of the SUM, DSUM and IIF function, revisit their definitions and descriptions above before you continue. Try to know their difference and when they should be used.

a. You were asked to calculate only the total quantity of Star. SUM function cannot help you solve such problems, but DSUM can.

In this case:

Expression is [QTY].

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

Criteria is [PRODUCT].

Specification is Star.

The label is TOTAL_QUANTITY_OF_STAR

While the syntax is =DSum("[QTY]","stock_report","[PRODUCT]='Star'") then press enter key.

Follow this step for the rest of the solutions to other questions.

b. The label is TOTAL_COST_OF_EACH_PRODUCT

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

c. The label is GRAND_TOTAL_COST_OF_ALL_PRODUCT

While the syntax is =SUM([COST_PRICE]) then press enter key.

d. The label is TOTAL_QUANTITY_OF_GULDER_AND_MALT

While the syntax is =DSum("[QTY]","stock_report","[PRODUCT]='Gulder'")+DSum("[QTY]","stock_report","[PRODUCT]='Malt'") then press enter key.

This is just a combination of two DSUM formulas with an addition sign (+).

e. The label is TOTAL_QUANTITY_OF_STAR_AND_GULDER

While the syntax is =DSum("[QTY]","stock_report","[PRODUCT]='Star'")+DSum("[QTY]","stock_report","[PRODUCT]='Gulder'") then press enter key.

Save your work and switch your form to form view to view the effect.

NOTE:

Format all currency text boxes as currency. 

Don’t try copying these syntaxes, type them by yourself. This helps to reduces sources of errors and also helps you to learn faster.

If you got everything correct, your form should resemble the one shown below.

This is the end of part 2 of this chapter . With the concept of these examples, you should be able to tackle more challenging applications in sales report and stock report. 

Recommended MS Access Textbook

Get this book (Kindle format): Designing Professional Database Management Systems Using Microsoft Access 2013 and 2016 and MySQL.

Click Here to know more about the book.

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

Now move over to Part 3: BUSINESS APPLICATIONOF MS ACCESS BUILT IN FUNCTIONS TO STOCK AND BUDGET CONTROLS.

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.