Exam

1. Exam

2. Total max 40 points

Task 1 MS Excel: ВПР(VLOOKUP) and simple
operations
Task 2 MS Excel: filtration
Task 3 MS Access: DB design
Task 4 MS Access: query constructor
Task 5 MS Access: SQL
Task 6 VBA: conditional task
7
7
7
5
6
8

3. Total problem

1. Duration – 3 astronomical hours.
2. Read task to the end.
3. Don’t write answer to the task in panic.
4. Don’t write answer to somebody’s other task!
5. Don’t use anything on exam, including
calculators on the mobile phone.
6. Do exact that task requires!
7. Don’t distort task.

4. Task 1

5. Task 1: solution

ВПР = VLOOKUP
ЕСЛИ = IF
СУММ = SUM

6. Should remember and know:

1. Functions ВПР and ЕСЛИ.
2. Basic ideas to work in Excel:
adressing, functions.

7. Common problems

1. СУМ, SUMM – such functions don’t exist!
/, \, :
2. Don’t mess
symbols!
3. SUM(G3 +G4+G5+G6) – unfortunately ,
will work correctly, but no points for this.
4. If you can not:
- get correctly percentage from some number;
- use signs > and < correctly;
you will be punished !

8. Task 2

9. Task 2: solution

10. Should remember and know:

1. Logical operations.
2. Interface of extended filter.
3. Usage of AND and OR operators in extended
filter.

11. Common problems

1. Use existing comparison symbols!
2. Write criteria according to the task!

12. Task 3

13. Task 3: scheme

14. Task 3: datatypes

15. Should remember and know:

1. Database normalization ideas.
2. Types of relations.
3. Types of data.

16. Common problems

1. Don’t forget data types and their properties!
2. Link compatible datatypes and similar
fields!
3. Calculated fields are not required to be in table!
4. Don’t mess structure of the table
with its content.
5. If you know that solution limits the task –
please specify this.
6. Link 1-1.
7. Solve the problem from task exactly.

17. Task 4

18. Task 4: solution

Pay to broker: [Purchase-Sale]![Cost]*0,01

19. Should remember and know:

1. Query constructor.
2. Data selection operators Like, Between …
and … and logical ones.
3. Calculated fields!

20. Common problems

1. Don’t forget about correspondence between query
and database scheme (previous task).
2. Don’t miss checkpoints to print fields of query.
3. Don’t forget to add more fields to query
to make it readable.
4. Don’t mess parameter and query condition!
5. Don’t forget about correspondence between scheme
in query and selected fields of tables.
6. Betmen – is not a Between operator!
7. Compare dates with Between, compare numerical
values with … And …

21. Task 5

22. Task 5: solution

SELECT Apartments.[Area,m2], [Purchase-Sale].Cost,
[Purchase-Sale].OperationTypeID, Condition.Condition,
[Purchase-Sale]![Cost]*0.01 AS [Pay to broker]
FROM Condition INNER JOIN (Apartments INNER JOIN
[Purchase-Sale] ON Apartments.ApartmentID = [PurchaseSale].ApartmentID) ON Condition.ConditionID =
Apartments.ConditionID
WHERE (((Apartments.[Area,m2])>50) AND (([PurchaseSale].Cost)>100000) AND
((Condition.Condition)="satisfactory"));

23. Should remember and know:

1. SELECT operator structure.
2. SQL common syntax.
3. Union of tables with INNER JOIN or WHERE.
4. Selection of data with WHERE and logical
operations.
5. Calculated fields!

24. Common problems

1. Previous task with query using constructor
should be done.
2. SQL syntax should be preserved.
3. Don’t forget to union tables!
4. Don’t forget about explanation!

25. Task 6

26. Task 6: form design

27. Task 6: code

28. Should remember and know:

1. Ability to cerate math for the task.
2. Remember main controls of interface
and their properties (Button, TextBox).
3. Ability to link event with code.
4. Ability to get values from form,
calculate corresponding values,
print results on a form.

29. Common problems

1. Form design should be present with
clear names for every control!
2. Write code as correctly as you can.
3. Don’t write things you do not know exactly.
4. Check math formulas!