Holutech Computer & Technology Ltd.

Holutech Computer & Technology Ltd. Host: Iwayinbo Oluwaseyi A.k.A (Donprince)

09/11/2017

Advanced Database – Structured Query Language (SQL)
Structured Query Language (SQL) is used to manage data within a database.

It uses relational algebra to create commands that retrieve or maintain the data.

Some examples of commands are CREATE – to create tables, RENAME – to rename tables, SELECT – to select data records, INSERT – to insert data records and DELETE – to delete data records.




2 SQL Con
To see examples of SQL code, open the database ‘Company Employees’ (created in module 1 or available for download).

Right click on one of the queries, and select ‘Edit in SQL mode’

This brings up a new window displaying the SQL code for that query, with the commands highlighted in blue text.

The main use for SQL is in querying databases to retrieve information.

All queries created in LibreOffice Base use SQL, even ones created in design mode.

The average user won’t need to use SQL code, it’s mainly used when dealing with highly complex queries or to write entire systems.

It’s useful to know about it, but you don’t need deep understanding for this module.

04/10/2017

Understanding Databases – Modify Reports
Change arrangement of data fields and headings in a report




7 Change arrangement of data fields
Click ‘Reports’ from the Database area of the main window and right click the report you want to change in the Reports area.

Click ‘Edit’ from the drop down menu.

8 Change Fields
To move fields and labels, simply click and drag individually to move them around the report page.

Alternatively, use the ‘Select’ arrow icon to drag a selection of fields and move them as a group.
Use the alignment icons in the Report Controls toolbar to modify alignment.

To edit the page layout, drag the bars between Header, Detail and Footer up and down.

9 Edit Appearance
To edit the appearance or position of text fields, click the field to highlight it. Then click the Properties icon, or right click and select properties.

The properties window allows you to change position, font, alignment, formatting, etc.

10 Edit Footers and headers
To edit headers and footers in a report, click the Label Field icon on the Report Controls toolbar.

Then click and drag inside the ‘Page Header’ area of the report to create a text box.

Click the Properties icon and enter desired text into the Label box.

To run the report, click the ‘Execute Report’ icon on the toolbar.

11 Text Output
The report will be generated as a text document.

The text box entered into the Page Header section will be repeated at the top of each page.

We could also add text to the Page Footer section to repeat text or fields at the bottom of each page.

Post loading........
11/07/2016

Post loading........

25/04/2016

Advanced Spreadsheets – Naming Cells

Name cell ranges, delete names for cell ranges.

To name a cell range, click on ‘Insert’ from the main menu bar. From the resulting menu, click on ‘Names’ and from the sub-menu which gets generated, click on ‘Define’. This will open the ‘Define Name’ dialog box.

1Previous

In this dialog box, under ‘Name’, give the name of the range and from the ‘Select’ icon given in front of ‘Range’, select the cell range which you want to be included in this range. After this, click on ‘Add’. This will add the named cell range to the spreadsheet.

To delete the cell range, again click on ‘Insert’ from the main menu bar. From the resulting menu, click on ‘Names’ and from the resulting sub-menu, click on ‘Manage’. This will open the ‘Manage Names’ dialog box. This dialog displays a list of all the named cell ranges in the sheet. Simply select the name which you want to delete and click on ‘Delete’.

After this, click on ‘OK’. The named cell range will get deleted.

Use named cell ranges in a function.

We can use a named cell range in a function. To do that, just put the name of the cell range in the argument of a function in ‘Function Wizard’, instead of selecting the cells.

As an example, here we have created a named cell range called ‘Roll’, which contains the roll numbers as have been highlighted in the illustration below. Then while using the ‘SUM’ function, instead of selecting the cells, we have just put the name of the range as the argument. This is how we use named cell ranges in functions.

25/04/2016

Welcome back!!!

26/02/2016

Advanced Spreadsheets – Database Functions

Use database functions: dsum, dmin, dmax, dcount, daverage.

In the same ‘Function Wizard’, under the ‘Database’ category, we have some database functions. One of these functions is ‘DSUM’. This function adds all the cells of a data range where the contents match the search criteria. This function takes 3 arguments, ‘Database’, ‘Database field’, and ‘Search criteria’. ‘Database’ gives the range of cells containing the data. ‘Database field’ gives the database column which needs to be used for the search criteria. ‘Search criteria’ defines the cell range containing the search criteria.

1Previous

Let us take an example; let the ‘Database’ contains some fields as have been shown below.

FruitWeightCostProfitApple564Pear483Pineapple821Mango293

Also, let the ‘Search criteria’ be defined as given below.

FruitWeightCostProfit=Apple=Pear

So, when these inputs are used, and the ‘Database field’ is set as ‘Profit, the result will come out to be ‘7’. This is because the entire database is evaluated and it is found that only two rows satisfy the criteria, row 2 and row 3. So after this the sum of the values of ‘Profit’ column is taken for these two rows, which comes out to be 4+3 = 7. This is how ‘DSUM’ works.

We also have the ‘DMIN’ and ‘DMAX’ functions in the same category. ‘DMIN’ returns the minimum of all the cells of a data range where the contents correspond to the search criteria. ‘DMAX’ does the same thing but it returns the maximum value. Both these functions use the same three arguments which have been described above.

Let us take an example, using the same data; we will get the result of ‘DMIN’ to be ‘3’ and for ‘DMAX’ to be ‘4’.
‘DCOUNT’ counts the cells of a data range whose contents match the search criteria. This function also uses the same three arguments and the working of this function is also exactly similar to the ‘DSUM’ or the ‘DMIN’ or the ‘DMAX’ functions.

‘DAVERAGE’ returns the average value of all the cells of a data range whose contents match the search criteria. This function also uses the same three arguments and the working of this function is also exactly similar to the ‘DSUM’ or the ‘DMIN’ or the ‘DMAX’ functions.

11/12/2015

For those people in Lagos" Repair, Unlock And Flash all kinds of Phone' Such as; BlackBerry, IPhone, Nokia, Samsung, Apple, Tecno, Sony Ericsson, Itel, And so on....... Address: No; 4B Otigba Street, Awolowo way, Computer Village, Ikeja Lagos. Contact Oluwaseyi A.k.A Holutech-Coms on 08104589347.

11/12/2015

For those people in Lagos" Repair And Flash all kinds of Phone' Such as; BlackBerry, IPhone, Nokia, Samsung, Apple, Tecno, Sony Ericsson, Itel, And so on....... Address: No; 4B Otigba Street, Awolowo way, Computer Village, Ikeja Lagos. Contact Oluwaseyi A.k.A Holutech-Coms on 08104589347,

02/11/2015

← Spreadsheet Formats – Alignment and
Border Effects
Edit Spreadsheet Chart Title →
← Spreadsheet Formats – Alignment and
Border Effects

02/11/2015

Worksheets Functions →
← Worksheets Functions →
← Older Comments
← Older Comments
Formulas and Functions
Arithmetic Formulas
Recognize good practice in formula creation: refer to cell references rather than
type numbers into formulas.
We have been entering either text or numbers into the cells till now, but in case
the data in a cell is dependent on the value of data of other cells, we use formulas.
Formulas use numbers and variables to get the required values. These variables are
the cell references of the cells from where we need to get the data to be used in
the formulas.
Whenever a formula is created in Calc, one thing should always be taken into
consideration. We should always try and use cell references in the formulas
instead of directly using numbers. This helps in decreasing the efforts in changing
the formulas every time a value needs to be changed. If we keep such values directly
in a formula, we’ll have to change the formula each time the value is changed. But
if we keep such values in a separate cell and use the cell reference in the formula,
then we’ll have to simply change the value in one cell and all the formulas will be
updated. This saves time and effort.
Create formulas using cell references and arithmetic operators (addition,
subtraction, multiplication, division).
There are four types of arithmetic operators; addition, subtraction, division and
multiplication. These return numerical results.
Any formula must always begin with an ‘=’ symbol. The formulas can be entered
by either using the function wizard or by typing directly into the cell or the input
line.
‘=b4+b6’ gives the sum of the values stored in the cells ‘B4’ and ‘B6’. ‘B4’ and ‘B6’
are the cell references here and ‘+’ is the arithmetic operator.
To create this formula, double click on the cell where this formula needs to be
entered. Then type in the formula in the cell and press ‘Enter’ from the keyboard.
This will showcase the result of the formula in that cell.
The subtraction, multiplication and division operators can be used in the same
way.
Identify and understand standard error values associated with using formulas:
?, /0!, !.
While using formulas, it is very common to get errors. These can occur due to a
variety of reasons, but the important part is to identify these, so that we can
correct them. Error messages or values are the simplest tools which help us in
identifying these errors. Some of the most common error-values associated with
using formulas are given below,
? : This error value is displayed in place of the error code Err:525. This
code signifies that no valid reference exists for the argument. This means that if we
provide some argument to the function incorrectly, say ‘b’ in place of ‘b6’ or any
such mistake, then this error value will get displayed.
/0! : This error value is displayed in place of the error code Err:532. This
signifies division by zero. This means that if we write a formula of the type, ‘=b4/b6’
and b6 here contains 0, then this error value will get displayed as division by zero
is not a proper mathematical operation.
! : This error value is displayed in place of the error code Err:524. This
signifies that the column, row, or sheet for the referenced cell is missing. This
means that if we are referring a sheet in a formula or a function, which has been
deleted, then this error value will get displayed.
Understand and use relative, absolute cell referencing in formulas.
Referencing is the way by which we refer to the location of any cell in Calc. There
are two types of references, absolute and relative.
In relative referencing, when we use a formula to refer to two or more cells, and
then when we copy this formula to a new location, then the new formula does not
refer to the same cells. It refers to new cells which have the same relative position
to the formula as was the case with the original referencing.
To understand in a better way, let us take an example. As shown below, we have
put the formula ‘=b4+b6’ in the cell ‘b9’. Thus the answer of this calculation, 11, is
being displayed in this cell. When we copy the formula and paste it in ‘c9’, then
the result will be 10 and not 11. This is because, the formula copied will have new
references ‘c4’ and ‘c6’ as these have the same relative locations to ‘c9’, as was the
case with ‘b4’, ‘b6’ and ‘b9’. Thus the sum of the values stored in ‘c6’ and ‘c9’ will
get displayed, which is 10. This is how relative referencing works.
In absolute referencing, the references do not change with the formula and the
same reference is copied when the formula is copied to another cell. This is
achieved by writing the formula as, ‘=b4+$b$6’. This will change the ‘b4’ reference
when the formula is copied, but the ‘b6’ reference will remain as it is. This has
been shown in the diagram given below.
Comments
Formulas and Functions — 188 Comments
Atakora Bright on November 2, 2015 at 11:37 am said:
VERY INTRESTING
Reply ↓
David odhiambo on October 31, 2015 at 6:12 pm said:
Thank you for such anice lesson
Reply ↓
Rangeeta Radhika Prasad on October 31, 2015 at 2:41 am said:
Very helpful lesson.
Thank you sir for this lesson
Reply ↓
Evans Makori on October 29, 2015 at 5:36 am said:
A helpful topic and thanks.
Reply ↓
Leave a Reply
Your email address will not be published. Required fields are marked *
Name *
Email *
Website
Comment
Custom Search
Select Language ▼
Find That Lesson With Google Search
Search
Are You a Google User?
Press the +1 Button
New Courses
Subscribe in a reader
Latest Lessons
Understanding Word Processing Quiz
Mail Merge – Outputs
Mail Merge – Insert Data Fields
Mail Merge – Preparation
Word Processing – Preview and Print
Available Courses
Customising Your PC (5)
IT Training (12)
Learn Computer Skills (25)
Basic Computer Skills (12)
Common Tasks (8)
Intermediate Computer Skills (5)
Microsoft Certified Professional (79)
Disaster Recovery (5)
Hardware (5)
Installations (1)
Interoperability (3)
Introduction To Networking (25)
Managing Disks (9)
Windows Quickstart (8)
Windows XP Server 2003 (8)
Local Security (6)
Local Users and Groups (4)
Monitoring and Optimisation (5)
NTFS (4)
Printing (3)
Remote Desktop and Terminal Services (6)
Shared Folders (5)
TCP/IP (7)
Microsoft Certified Systems Engineer (86)
Active Directory Overview (8)
Active Directory Sites And Replication (5)
Active Directory Trusts (2)
Active Directory Users and Groups (4)
Certificate Services (6)
Deploying Applications (3)
DFS (5)
DHCP (5)
DNS (8)
Group Policy (5)
IIS (5)
IPsec (3)
Remote Access (6)
RIS (7)
Routing (6)
SUS (3)
WINS (6)
Microsoft Certified Technology Specialist (129)
Windows Server 2008 Active Directory, Configuring (78)
Active Directory Federation Services (3)
Active Directory Rights Management (3)
Active Directory Sites And Replication (3)
Active Directory Trusts (2)
Administration (5)
Authentication (5)
Certificate Services (9)
Computers (4)
Directory Business Continuity (5)
Domain Controllers (4)
Group Policy (4)
Groups (6)
Installation (5)
Integrating Domain Name System (6)
Lightweight Directory Services (2)
Users (9)
Windows Server 2008 Network Infrastructure, Configuring (59)
Configuring IP Routing (4)
Configuring Name Resolution (8)
Configuring Windows Firewall and Network Access Protection (4)
Connect To A Network (6)
Creating a DHCP Infrastructure (5)
Managing Files (11)
Managing Printers (3)
Managing Software Updates (4)
Monitoring Computers (4)
Protecting Network traffic with IPSec (3)
TCP/IP (7)
Office Software (44)
LibreOffice (42)
Calc (19)
Writer (22)
Mail Merge (3)
Uncategorized (1)
Website Design (22)
Creating websites with HTML (9)
CSS (6)
Extras (3)
What is the internet (4)
Windows Maintenance (9)
Wordpress (12)
Managing Worksheets Spreadsheet Formats – Alignment and
Border Effects
Spreadsheet Cells Functions
Edit Spreadsheet Chart Title

02/11/2015

Worksheets
Switch between worksheets.
To switch between different open worksheets, we just need to click on the
appropriate sheet name from the bottom of the Calc window, as has been shown in
the below given illustration.
When we click on a particular sheet name, that sheet will get opened. This is how
we can switch between different open worksheets.
Insert a new worksheet, delete a worksheet.
To insert a new worksheet in Calc, you can simply click on the ‘Add Sheet’ button.
This button has been shown in the diagram below. This button simply creates a
new sheet with the default name. After the creation of the sheet with the default
name, double click on the sheet name to open the ‘Rename Sheet’ dialog. Enter the
new name for the sheet in this dialog and click on ‘OK’. The name of the sheet
will get updated.
Another method to insert a new worksheet is by double-clicking on the white area
in front of the ‘Add Sheet’ button, as has been shown in the diagram. Double-
clicking here will open the ‘Insert Sheet’ dialog.
Enter the name for the sheet here, and change the other options as per the
requirements and click on ‘OK’. This will create a new worksheet.
To delete a worksheet, simply right-click on the sheet name and click on the
‘Delete Sheet’ button from the resulting menu.
Recognize good practice in naming worksheets: use meaningful worksheet names
rather than accept default names.
Whenever new worksheets are created, then Calc provides default names for these
worksheets. These names are generic in nature and provide no information about
the type of sheet or the type of data contained in that sheet.
So rather than accepting these default names, we should use meaningful names for
our spreadsheets which convey some information about the type or nature of the
data contained in the spreadsheets.
Copy, move, and rename a worksheet within a
spreadsheet.
To move a sheet to a different position within the same spread sheet, click on the
sheet tab and drag it to its new position before releasing the mouse button.
To copy a sheet within the same spread sheet, hold down the ‘Ctrl’ key and then
click on the sheet tab and drag it to its new position before releasing the mouse
button. This will create a copy of the existing worksheet in the new position.
To rename a spreadsheet, double click on the sheet name to open the ‘Rename
Sheet’ dialog. Enter the new name for the sheet in this dialog and click on ‘OK’.
The name of the sheet will get updated.
Comments
Worksheets — 119 Comments
Atakora Bright on November 2, 2015 at 11:11 am said:
VERY ENJOYABLE
Reply ↓
Ephrage Mutsvanga on October 28, 2015 at 1:56 pm said:
Thank you so much I really appreciate.
Reply ↓
bashir bonshay on October 27, 2015 at 2:50 am said:
thank you very much for your which help us and we are thankfully to you
Reply ↓
yosof ahmed on October 24, 2015 at 7:29 pm said:
Thank you very much for all the lessons.The tool is very helpful for me and
well explained.Thanks once more
Reply ↓
ashiq on October 22, 2015 at 7:58 am said:
thanks, fruitful
Reply ↓
susheel tiwari on October 21, 2015 at 9:17 am said:
Thank you very much for all the lessons.The tool is very helpful for me and
well explained.Thanks once more
Reply ↓
Dackshon on October 20, 2015 at 10:29 am said:
May GOD bless you for the good lesson.
Reply ↓
Philip Imoke on October 15, 2015 at 2:11 am said:
Thank you very much for all the lessons
I want to learn complete lesson about the spread sheet(office excel).
Please kindly email to me
Reply ↓
eyong on October 14, 2015 at 3:48 pm said:
am greatful
Reply ↓
ohammed Kamara on October 12, 2015 at 1:52 pm said:
Thanks very much for this wonderful lesson. I must admit you have made my
work easier….. I ‘m grateful.
Reply ↓
david wanjohi muiruri on October 11, 2015 at 9:49 am said:
The tool is helpful and well explained.
Reply ↓
david wanjohi muiruri on October 11, 2015 at 9:46 am said:
quite helpful.
Reply ↓
lufefe majola on October 11, 2015 at 5:13 am said:
Thanx dat’s great
Reply ↓
Jarius Sarplah on October 6, 2015 at 6:15 pm said:
I’m interested in this lesson. Thanks for the presentation.
Reply ↓
ALLY LIPENA on September 30, 2015 at 10:28 pm said:
Thanks so much
Reply ↓
Abu Jalloh on September 29, 2015 at 8:37 pm said:
Thanks a lot for this wonderful job. May God bless you and your Institution
for this tremendous job done. Am really enjoying it. I have not been checking
but from now on I will always participate. Thanks once more
Reply ↓
syed on September 23, 2015 at 2:24 pm said:
impressed me.I want to keep a copy IN my LAPTOP to go for practice.
Thanks. God bless you.
Reply ↓
ERASTUS on September 22, 2015 at 7:59 am said:
SHOULD I PRINT OUT MY SPREADSHEET WITH THE PAGE
NUMBER OR SHOULD I INSERT THE PAGE NUMBER TO MY
SPREADSHEET?
Reply ↓
Don Nelson on September 21, 2015 at 6:52 am said:
Thanks so much for improving my IT knowledge at nk cost. Only the
Almighty will reward you.
Reply ↓
Leave a Reply
Your email address will not be published. Required fields are marked *
Name *
Email *
Website
Comment
Custom Search
Select Language ▼
Find That Lesson With Google Search
Search
Are You a Google User?
Press the +1 Button
New Courses
Subscribe in a reader
Latest Lessons
Understanding Word Processing Quiz
Mail Merge – Outputs
Mail Merge – Insert Data Fields
Mail Merge – Preparation
Word Processing – Preview and Print
Available Courses
Customising Your PC (5)
IT Training (12)
Learn Computer Skills (25)
Basic Computer Skills (12)
Common Tasks (8)
Intermediate Computer Skills (5)
Microsoft Certified Professional (79)
Disaster Recovery (5)
Hardware (5)
Installations (1)
Interoperability (3)
Introduction To Networking (25)
Managing Disks (9)
Windows Quickstart (8)
Windows XP Server 2003 (8)
Local Security (6)
Local Users and Groups (4)
Monitoring and Optimisation (5)
NTFS (4)
Printing (3)
Remote Desktop and Terminal Services (6)
Shared Folders (5)
TCP/IP (7)
Microsoft Certified Systems Engineer (86)
Active Directory Overview (8)
Active Directory Sites And Replication (5)
Active Directory Trusts (2)
Active Directory Users and Groups (4)
Certificate Services (6)
Deploying Applications (3)
DFS (5)
DHCP (5)
DNS (8)
Group Policy (5)
IIS (5)
IPsec (3)
Remote Access (6)
RIS (7)
Routing (6)
SUS (3)
WINS (6)
Microsoft Certified Technology Specialist (129)
Windows Server 2008 Active Directory, Configuring (78)
Active Directory Federation Services (3)
Active Directory Rights Management (3)
Active Directory Sites And Replication (3)
Active Directory Trusts (2)
Administration (5)
Authentication (5)
Certificate Services (9)
Computers (4)
Directory Business Continuity (5)
Domain Controllers (4)
Group Policy (4)
Groups (6)
Installation (5)
Integrating Domain Name System (6)
Lightweight Directory Services (2)
Users (9)
Windows Server 2008 Network Infrastructure, Configuring (59)
Configuring IP Routing (4)
Configuring Name Resolution (8)
Configuring Windows Firewall and Network Access Protection (4)
Connect To A Network (6)
Creating a DHCP Infrastructure (5)
Managing Files (11)
Managing Printers (3)
Managing Software Updates (4)
Monitoring Computers (4)
Protecting Network traffic with IPSec (3)
TCP/IP (7)
Office Software (44)
LibreOffice (42)
Calc (19)
Writer (22)
Mail Merge (3)
Uncategorized (1)
Website Design (22)
Creating websites with HTML (9)
CSS (6)
Extras (3)
What is the internet (4)
Windows Maintenance (9)
Wordpress (12)
Managing Worksheets Formulas and Functions
Spreadsheet Cells Spreadsheet Formats – Alignment and
Border Effects
Prepare Outputs

Address

No_5 Ore Meji Street, Isolo Lagos
Isolo

Telephone

07069397784

Website

Alerts

Be the first to know and let us send you an email when Holutech Computer & Technology Ltd. posts news and promotions. Your email address will not be used for any other purpose, and you can unsubscribe at any time.

Shortcuts

Share

Category