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
Click Here to know more about the book.
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.
No comments:
Post a Comment
WHAT'S ON YOUR MIND?
WE LOVE TO HEAR FROM YOU!