Academy of Economic Studies of Moldova

CHAIR OF "INFORMATION TECHNOLOGIES"

Individual study
the discipline
Economic Informatics

Coordinator
Graduate didactic, Căpăț înă Valentina

Student Panicov Mihaela

Group MKL171

Chisinau, 2017

1
Table of contents:
Part 1...............................................................................................................................4
1. Designing the Excel table..............................................................................................5
1.2. Development of the nomenclature .........................................................................................5

1.2.1. Formatting monetary values (currency)..................................................................6

1.3. The VLOOKUP search function.................................................................6-7
1.4. Simple functions that contain operators +, - , *........................................................................7-8

1.5. Logical functions..................................................................................................................8

[Link] functions....................................................................................................8-9
1.7. Statistical functions............................................................................................................9-10
1.8. Mathematical Functions ....................................................................................................... 10-12

[Link] validation............................................................................................................12
1.9.1. Using the Dget function ............................................................................................... 13
[Link].............................................................................................................................13
[Link] summaries through subtotals..................................................................................13-14
1.12. Pivot Tables..................................................................................................................15
[Link]
[Link].......................................................................................................................17
[Link].......................................................................................................................18
Part 2................................................................................................................................19
2.1. Formulating the problem....................................................................................................20
2.2. Designing the structure of the tables....................................................................................20-23
[Link] B.D. Relational
[Link] queries....................................................................................................24-26
2.3.1. Summary inquiries..............................................................................................26-30
[Link] Queries.................................................................................................30-31
[Link] inquiries.....................................................................................................31-33
2.5.1. Modification inquiries...........................................................................................33-34
[Link] questionnaire..............................................................................................34-35
[Link]...........................................................................................................................36

2
[Link].......................................................................................................................37
[Link]

3
PART 1
MICROSOFT APPLICATION
EXCEL

4
[Link]: The "Loft" café is a public establishment that serves tea and
coffee
To design a table that displays the types of coffee and tea purchased for the purpose of
to keep records of these for the year 2017. The table should display the names of the teas and
cafea,cantităț ile,data procurării,preț ul pe un kg ,ș i preț ul inclus cu TVA pentru produsele
date,figure1.

Figure 1. The table with the initial data

2. To elaborate the price list and to introduce the initial price for each type of coffee
and tea, sorting the data from A to Z, figure 2.

2.1 To display the price value accompanied by the monetary symbol, we format the data as follows
next steps:

5
We select the elements for formatting: Home Cells Format Cells Currency thus
we obtain figure 2.1.

Figure 2.1 Data formatting

3. Displayed in the initial table the unit price for each type of product. For this, I have
taken from the matrix, O2:P15, the unit price of each product. The search in this matrix is
based on the product names in column A. The search function entered in column C
this is represented in figure 3.

Figure 3. Vertical lookup function VLOOKUP.

Thus, the data from the nomenclature, with the help of the VLOOKUP function, has been transferred to the table as
initial date, figure 3.1.

6
 Figure 3.1 VLOOKUP

4. We calculate the price with VAT; for this, we enter the following formula in cell E.

Figure 4 Price with VAT

7
We find out the final price of the products for this, we enter the following in cell F3

formula

Through AutoFill, we obtain the other amounts when purchasing products, figure 4.1.

Figure 4.1. Final price

5. If the café is to have a good reputation, we determine the validity period of
purchased products (The purchase date is also the manufacturing date) with the help of the logical function If
for this we introduce the formula in cell H3

With the help of the AutoFill option, we determine the validity period for the rest.
of the products, figure 6.

Figure 5 Expiration date (valid, expired)

We find out the number of days since the purchase date of the products using the function.
for calendar dates Days360, in cell I3 we enter the formula

8
With the help of the AutoFill option, we will obtain for the other products, figure 6.

Figure 6 Stock Duration

7. We find the minimum value of the final price. We enter the formula.

We find the maximum value of the final price. We introduce the formula.

9
 Figure 7 Minimum and maximum value of the final price

7.1 We find the average value of the final price. We introduce the formula.

Figure 7.1 Average value

8. We calculate the total sum of the products using the mathematical function.
Sum, we introduce the formula

So the total amount that the cafe bears to procure the given products is
represented in figure 8

10
 Total Sum

Also, with the help of the mathematical function SUMIF, we determine what the sum of the products is.
purchased in a quantity of 100 kg, we introduce the following formula

The amount paid only for the products of 100 kg is shown in figure 9.1.

11
 Figure 8.1 Sumif
9. To see the final price for each product separately, we will validate the data according to the following
steps for cells A2:A3
Data–Data Validation, thus we obtain:

Figure 9
Iar cu ajutorul funcț ieiDget vom reprezenta pentru fiecare produs preț ul lui final ,fugura 9.1

12
 Figure 9.1
10. For filtering data, we follow the steps: Data -> Sort & Filter -> Filter (figure 11)

Figure 10

11. To calculate the subtotal of the final price of the products, we follow these steps:

Data->Outline->Subtotal(figure11.1)

13
Figure 11.1

12. With the help of PivotTable, we will represent the quantity and expiry date of the products.
to do this we will follow the steps
Insert-Tables-PivotTable and we will obtain figure 11

14
 Figure 12

And in figure 12.1 the Pivot Table is represented.

15
We will use slicers for name, quantity, and expiration date to
it determines more easily in what quantities the products are available, figure 13.

Figure 13 Slicer
14. To determine which products are in larger quantities, we will use the Type diagram.
Column, to follow the steps
Insert Charts - Insert Column Chart

16
We will obtain the following graph

Figure 14 Column Type Diagram

17
 [Link]

For situations when we work with lists, Excel tables can be very useful because of
it simplifies my work. With a single click, I have both formatting and filtering, moreover,
The tables understand very well both with pivot tables and with graphs and other functions in Excel. On
long ago, when I wrote a formula in a column, when I press ENTER the formula is
automatically multiplied across the entire column.

18
PART II
APPLICATION
MICROSOFT ACCESS

19
1. Formulating the problem:
To design the DB tables 'Phone Station'. The created database is intended for
controls the record of the payment of the subscribers for telephone services.

2. Create a new database, assigning the name phone station. I open the database and create four
tables according to the structures presented in figure 1.1-1.8

Figure 1.1 Subscribers table in Design mode

Figure 1.2 Subscribers Table in database Datasheet

20
 Figure 1.3 Table of Purchases in Design regime

Figure 3.4 The Purchasing Table in Datasheet mode

21
Figure 1.5 Installation Table in Design Mode

Figura1.6 Tabelul Instalari in regim Datasheet

22
Figure 1.7 Table of Months in Design mode

Figure 1.8 Table of Months in Datasheet Format

2.1 Relational BD Scheme

In the design of multi-table databases, Access environment for creating links between tables
(the Tools->Relationships menu) with the selection of the type of association (the Join Type button) and
ensuring the referential integrity of the data (menu Relationships->Edit Relationships)

Figure 1.9 Box Relationships

23
3. Inquiries
Designing selection queries.
Example 1Sa designs an integration that displays the list of subscribers from sector C.
1. In the Database window, click on the Queries page from the objects bar.
In the workspace, there are two modes for creating queries: Create Query in
Design view and Create query using wizard select Design View
3. As a result, the Select Query box opens in Design View and the Show Table box.
(Figure 2.1) the booth shows a table continued over three pages:

Tables - include a list of tables created in the current database;

Queries - include the list of queries already created in the current database;

Both - include a list of all tables and queries created in the current database.

Figure 2.1 Select Query Box in Design mode

On the Tables page, I select the Subscribers table by double-clicking after attaching the table to the grid.
The query closes the Show Table box.

24
4. Complete the inquiry grid according to Figure 3.2

5. Specific selection criteria for column sector 'c'

6. Click the Save button in the toolbar and enter a name for
the query Subscribers_sectorC.

Figure 2.3 Query Subscribers_sectorC in Datasheet mode

Example 2. Display information regarding the subscribers who have signed contracts with the following
numbers
"A-302" and "A-307".
Complete the inquiry grid according to figure 3.4

25
Figure 3.4 Query Subscriber_info 1 in Design mode

Figure 2.5 Query Subscribers_info 1 in Datasheet mode

3.1 Select queries that include the total row, calculated fields, selection criteria and
the grouping is done after a single field

Example 1. To establish the amount paid by the subscriber 'Cretu' so far.

26
Figure 2.6 Query Sum_ab_Cretu in Design mode

Figure 2.7 Query Sum-_ab_Cretu in Datasheet mode

Example2. What was the first day the subscriber 'Moraru' made the payment for services
remember

27
Figure 2.8 Query in Design mode

Figure 2.9 Query Sum_paid_Moraru_in Design mode

Select type queries that include in the total row, calculated fields, selection criteria and
The grouping is done based on several fields.

Example 1. To determine the total amounts paid for each month of each coupling,
abonatii :”A-301”;”A-303”,”A-306”.

28
Figure 2.10 Query Total_Sums in Design mode

Figure 2.11 Query the Total_Sums in Datasheet mode.

29
Example 2. Display information about subscribers who have paid the bill for the month of September and
each coupling.

Figure 2.12 Query Purchases_September in Design mode

Figure 2.13 Query Purchases_September in Datasheet mode

4. Parameterized queries
Example 1. Display information about a specific subscriber over a certain period of time.
Complete the interrogation grid.

On the Criteria row, enter the text in square brackets that will be displayed in the dialog box.
parameter:
Coloana Codul abonatului–[introduceti codul abonatului]
Column date -> [start of the period] And <= [end of the period]

30
Figure 2.14 Query Abonati_info in Design mode
Save the query with the name Subscribers_info and run it. As a result, it will open consecutively.
the dialog boxes of the parameter query (figure 2.15)

Figure 2.15 The Enter Parameter Value dialog boxes

5. Action inquiries
Action queries are queries that make changes at the database level.
These changes affect both the structure of the tables and the data contained in them. Therefore
We must be cautious in dealing with these types of inquiries, especially since the records have been liquidated.
, modifications cannot be restored.
To maintain data integrity, I will further perform some queries based on the copies.
the tables made previously.

31
Designing action queries
5.1 Create Table Queries (Make-Table Query)
Example: Display the subscribers who have paid an amount less than 60 for telephone services.
I use the Make Table query to create the Purchases 60 table which will include
the codes of all subscribers and the amount to be paid.

Proceed as follows:
[Link] a query based on the table Copy_Table_Payments.
2. Include in the Field row the fields Subscriber_Code and Amount. In the amount field, specify the condition.
<=60 will select the records with subscribers who have paid an amount less than 60 (figure
30.1)
3. From the Query menu, I select the Make Table Query option. In the Make Table dialog box, indicate
the name of the new table (Payments 60) and I press ok (figure 3.1)

Figure 3.1 Query Creating the Purchases Table 60 in Design mode

Finally, save the query with the name Achitari 60. To run the query, execute it twice.
click on its name.
In order to view the results, I open the Payments table 60 (figure 3.1) from the group of
objects Tables

32
Figure 3.2 Table of Acquisitions in Datasheet mode
5.2 Queries for updating data in tables (Update Query)
Example: Change the name of the sector from 'C' to 'Ciocana'.
Include in the Field row the Sector field

Select the Update Query option from the Query menu. As a result, the title of the constructor of
queries are modified in Update Query from Query. As a result, the title of the constructor of
queries are modified in Update Query, and at the bottom the Update To line appears.

Figure 3.3 Query modification of the sector from C to Ciocana in Design mode

33
Introduce in the Update To field for the Sector-Ciocana
Indicate in the Criteria row for the first sector the criterion c

Press the DataSheet View button on the toolbar to obtain the current values.
but the field Sector that is to be modified.
The actual modifications will only be made after returning to Design View and
Executing the Run command from the Query menu. As a result, a message will appear on the screen about
the total number of records to be modified. By clicking the Yes button, MS
Access will make the respective changes. To cancel the changes, we press the button.
NO.
Save the inquiry by naming it the modification of the sector from C to Ciocana.

Figure 3.4. Table Copy _Table_Subscribers in Datasheet mode

2.5.3 Queries for the deletion of records from tables (Delete Query)
Example: Terminate the subscriber with code A-306, who no longer requests telephone services.

In the field include the fields that will be displayed or for which conditions will be specified.
the selection, namely the fields Code_subscriber, Name, First_name. As a result, in the cells
respective for each field the Where option (figure 3.5)
Introduce in the Criteria row the selection criteria, in my case, for the field Subscriber_Code
I write condition A-306 (figure 3.5)

34
Figure 3.5 Query Liquidation of_subscriber_a306 in Design mode
To view the list of records to be excluded, I click the Datasheet button.
View your toolbar. As a result, obtain the window represented in figure 3.6.

Figure 3.6
Save the inquiry

Figure 3.6 (we see that A-306 is settled)

35
 6. Reports
The report is an effective tool for presenting information stored in the database by
Printing. The report gives the user complete control over the size and appearance.
each constitutive element.
The Design viewing mode is intended for the standalone creation of reports and
for dividing the report into sections, these being the individual components that
check what elements appear and how they are formed.
Report design
Example: To create a report to display the list with the information regarding
subscribe. (figure 4)

Figure 4. Report on the Subscriber List

36
 7. Forms
The form is a set of active areas made up of several types of elements.
associated controls, unassociated controls, etc. that allow the user to input effects
information.
Through forms, a user of an MS Access database can simplify
the data entry process provides an easy-to-use format for working with data and
I can add functional elements.
Form design

37
 8. Conclusion
Microsoft Access program offers a beneficial opportunity for data security in
the interior of any enterprise, in a simple and accessible way to anyone. This program holds
more advantages such as:
Independence of data from applications, so that the description of the data is separate from
their use.
The ability to apply restrictions and data security against access.
unauthorized.
Data integration that manifests through ensuring correctness at the moment of creation.
Databases can be accessed remotely via web services that are connected to a network.
from computers.
On the other hand, other programs are more expensive and we do not have trained personnel.

38