The need for a database and a spreadsheet…

The need for a database and a spreadsheet…

·

10 min read

Photo by Campaign Creators on Unsplash

Using a database management system will help you to manage your data efficiently and allows your users to perform multiple tasks with ease. To make sure you understand what a database does, you will need to understand that this type of Software is a management piece that will store, organizes, and manages a large amount of information within a single Software Application like Microsoft Access.

Of course, if you are using new technologies and you’re a programmer, you will use MongoDB, SQL, MySQL, Oracle, etc. However, all different Softwares will do the same thing store, organize and manage a large amount of data.

Using a spreadsheet, you will find that this tool is essential for businesses to manage their accounting department or to store valuable information from their users or from their business itself.

Note: Spreadsheet programs provide the means for keeping an inventory, statistical data modeling, and computing data. To store large amounts of raw data, it is best to use a database. This is especially true in circumstances where two or more users share the data.

This is how it looks when you use Microsoft Access:

This is how it looks when you use a Spreadsheet:

This is how it looks when you use MongoDB:

Also, I would like to mention that people that are using Microsoft Access is people that are not a real programmers. As Matthew MacDonald mentioned in his article called Microsoft Access: The Database Software That Won’t Die.

The Access audience is a special crowd that’s rarely targeted these days: technical people who aren’t serious coders. They may know their way around an Office macro, but they don’t have a formal programming background. Matthew MacDonald.

Then, Why is it too important to know this information?

When you start running your own business, you will need to start understanding that you will need to store data. Where your data will be the main structure of your business. To be more clear, I will need to give you the following example:

  1. You need more data to find new customers

  2. You need to structure your financial information

  3. You will build an application that will provide access to millions of people

  4. You have an E-commerce site

These 4 examples are common and of course, we can select more if we want. however, this is just a way that I can give you an understanding of why we need a database.

What type of database should I use?

Microsoft Access

If you are not a programmer, and you can follow instructions from documentation, I will suggest you use Microsoft Access. This tool will provide you easy instruction and how you can run your queries and you will have a menu that will provide you more information.

As you can see in the following example: If you want to create a simple select query, you will need to check the following link https://support.office.com/en-us/article/create-a-simple-select-query-de8b1c8d-14e9-4b25-8e22-70888d54de59. Then, you will find that you will need to use the Query Wizard.

  1. On the Create tab, in the Queries group, click Query Wizard.

[Microsoft + Open Source](https://cdn.hashnode.com/res/hashnode/image/upload/v1627018516875/3XMKLtewL.html)Microsoft + Open Source

  1. In the New Query dialog box, click Simple Query Wizard, and then click OK.

  2. Next, you add fields. You can add up to 255 fields from as many as 32 tables or queries. For each field, perform these two steps: Under Tables/Queries, click the table or query that contains the field. Under Available Fields, double-click the field to add it to the Selected Fields list. If you want to add all fields to your query, click the button with the double right arrows (>>). When you have added all the fields that you want, click Next.

[Microsoft + Open Source](https://cdn.hashnode.com/res/hashnode/image/upload/v1627018521257/N4TSoss1y.html)Microsoft + Open Source

  1. If you did not add any number fields (fields that contain numeric data), skip ahead to step 9. If you added any number fields, the wizard asks whether you want the query to return details or summary data.

Do one of the following: If you want to see individual records, click Detail, and then click Next. Skip ahead to step 9. If you want to see summarized numeric data, such as averages, click Summary, and then click Summary Options.

[Microsoft + Open Source](https://cdn.hashnode.com/res/hashnode/image/upload/v1627018526619/MrzoAYDuX.html)Microsoft + Open Source

  1. In the Summary Options dialog box, specify which fields you want to summarize, and how you want to summarize the data. The only a number of fields are listed.

For each number field, choose one of the following functions: Sum The query returns the sum of all the values of the field. Avg The query returns the average of the values of the field. Min The query returns the smallest value of the field. Max The query returns the largest value of the field.

[Microsoft + Open Source](https://medium.com/@OpenAtMicrosoft)Microsoft + Open Source

  1. If you want the query results to include a count of the records in a data source, select the appropriate Count records in the data source name checkbox.

  2. Click OK to close the Summary Options dialog box.

  3. If you did not add a date/time field to the query, skip ahead to step 9. If you added a date-time field to the query, the Query Wizard asks you how you would like to group the date values. For example, suppose you added a number field (“Price”) and a date/time field (“Transaction_Time”) to your query, and then specified in the Summary Options dialog box that you want to see the average value of the number field “Price”. Because you included a date/time field, you could calculate summary values for each unique date/time value, for each day, for each month, for each quarter, or for each year.

[Microsoft + Open Source](https://medium.com/@OpenAtMicrosoft)Microsoft + Open Source

Select the time period that you want to use to group the date/time values, and then click Next.

  1. On the last page of the wizard, give the query a title, specify whether you want to open or modify the query, and then click Finish.

If you choose to open the query, the query displays the selected data in Datasheet view. If you choose to modify the query, the query opens in Design view.

Oracle

This tool is one of the most common database language used in the tech industry since you can create databases, tables, records, etc. Oracle database is the collection of data treated as a unit. The purpose of a database is to store and retrieve related information. The key point here is their database server.

One example here is by using the SQL statement. The following example is to learn how to use self-joins.

SELECT e1.last_name||’ works for ‘||e2.last_name “Employees and Their Managers” FROM hr.employees e1, hr.employees e2 WHERE e1.manager_id = e2.employee_id AND e1.last_name LIKE ‘R%’ ORDER BY e1.last_name

Using Self Joins: Example The following query uses a self join to return the name of each employee along with the name of the employee’s manager. A WHERE clause is added to shorten the output.

You can find this tool here.

Domo

If you would like to manage your spreadsheets and your databases where you would like to make reports to understand your data, you will need to use the User Interface of Domo since this tool It is a mobile, cloud-based, social, community-driven business intelligence platform with the power to transform the way you manage your business. Domo can help you view data in one place, democratize across your organization, socialize important goals, and create accountability.

You can find this tool by clicking here.

MongoDB

This tool is a structure of a single object which makes it more clear. They don’t make complex joins, They have the deep query-ability. They support dynamic queries on documents using a document-based query language that’s nearly as powerful as SQL.

MongoDB has grown from being just a JSON data store to become the most popular NoSQL database solution with efficient data manipulation and administration capabilities.

One short example is the find() method:

Syntax — The basic syntax of find() method is as follows:

>db.COLLECTION_NAME.find()

find() method will display all the documents in a non-structured way.

Example

Assume we have created a collection named mycol as −

> use sampleDB
switched to db sampleDB
> db.createCollection("mycol")
{ "ok" : 1 }
>

And inserted 3 documents in it using the insert() method as shown below −

> db.mycol.insert([
    {
        title: "MongoDB Overview",
        description: "MongoDB is no SQL database",
        by: "tutorials point",
        url: "http://www.tutorialspoint.com",
        tags: ["mongodb", "database", "NoSQL"],
        likes: 100
    },
    {
        title: "NoSQL Database",
        description: "NoSQL database doesn't have tables",
        by: "tutorials point",
        url: "http://www.tutorialspoint.com",
        tags: ["mongodb", "database", "NoSQL"],
        likes: 20,
        comments: [
            {
                user:"user1",
                message: "My first comment",
                dateCreated: new Date(2013,11,10,2,35),
                like: 0
            }
        ]
    }
])

Following method retrieves all the documents in the collection −

> db.mycol.find()
{ "_id" : ObjectId("5dd4e2cc0821d3b44607534c"), "title" : "MongoDB Overview", "description" : "MongoDB is no SQL database", "by" : "tutorials point", "url" : "http://www.tutorialspoint.com", "tags" : [ "mongodb", "database", "NoSQL" ], "likes" : 100 }
{ "_id" : ObjectId("5dd4e2cc0821d3b44607534d"), "title" : "NoSQL Database", "description" : "NoSQL database doesn't have tables", "by" : "tutorials point", "url" : "http://www.tutorialspoint.com", "tags" : [ "mongodb", "database", "NoSQL" ], "likes" : 20, "comments" : [ { "user" : "user1", "message" : "My first comment", "dateCreated" : ISODate("2013-12-09T21:05:00Z"), "like" : 0 } ] }
>

You can find MongoDB here.

Python and Spreadsheets

Now, I would like to mention that if you know how to program in python, you can make much easier your life since you can get more information, and you can make an analysis of the data that you are getting. Plus, you can send this information to be store in a spreadsheet with a script.

One of my favorite libraries to use is openpyxl since this is the library that allows me to use python to read and modify Excel spreadsheet files. As an example, you might have a task of transferring data to one file to another, you have to extract data from the web and you will need to start saving those results into a spreadsheet or you have to look through hundreds of spreadsheets.

To start working with openpyxl, you will need to install it through your IDE or through your python command line:

>>> **import openpyxl**

If you would like to open excel documents with this python library, you will need to use the following script:

>>> **import openpyxl**
>>> **wb = openpyxl.load_workbook('example.xlsx')**
>>> **type(wb)**
<class 'openpyxl.workbook.workbook.Workbook'>

You can get sheets from the workbook by the following code:

>>> **import openpyxl**
>>> **wb = openpyxl.load_workbook('example.xlsx')**
>>> **wb.get_sheet_names()**
['Sheet1', 'Sheet2', 'Sheet3']
>>> **sheet = wb.get_sheet_by_name('Sheet3')**
>>> **sheet**
<Worksheet "Sheet3">
>>> **type(sheet)** <class 'openpyxl.worksheet.worksheet.Worksheet'>
>>> **sheet.title**
'Sheet3'
>>> **anotherSheet = wb.active**
>>> **anotherSheet**
<Worksheet "Sheet1">

You can get cells from the sheets:

>>> **import openpyxl**
>>> **wb = openpyxl.load_workbook('example.xlsx')**
>>> **sheet = wb.get_sheet_by_name('Sheet1')**
>>> **sheet['A1']**
<Cell Sheet1.A1>
>>> **sheet['A1'].value**
datetime.datetime(2015, 4, 5, 13, 34, 2)
>>> **c = sheet['B1']**
>>> **c.value**
'Apples'
>>> **'Row ' + str(c.row) + ', Column ' + c.column + ' is ' + c.value**
'Row 1, Column B is Apples'
>>> **'Cell ' + c.coordinate + ' is ' + c.value**
'Cell B1 is Apples'
>>> **sheet['C1'].value**
73

You can convert column letters and numbers:

>>> **import openpyxl**
>>> **from openpyxl.cell import get_column_letter, column_index_from_string**
>>> **get_column_letter(1)**
'A'
>>> **get_column_letter(2**)
'B'
>>> **get_column_letter(27)**
'AA'
>>> **get_column_letter(900)**
'AHP'
>>> **wb = openpyxl.load_workbook('example.xlsx')**
>>> **sheet = wb.get_sheet_by_name('Sheet1')**
>>> **get_column_letter(sheet.max_column)**
'C'
>>> **column_index_from_string('A')**
1
>>> **column_index_from_string('AA')**
27

In conclusion

In conclusion, you will notice that I give you an overview of the tools that you can use, and the tools that are available to you to start running your database and to start creating, editing or manipulating your data. To choose the right tool, you will need to take a look at the amount of data that you will use, and you will need to see what is the use and how frequently you will be using your data to be able to decide what tool to use.

However, I would like to mention that databases and spreadsheets are important in the business world since this is the way that you can view and manage hundreds of data.