Let's start a series of tutorials for Microsoft Access, as it is widely used in the Hunter SDK.
A factor common in beginners in Access is that most of the time there is a kind of 'barrier', as if Access was an extremely complicated, a software only for the famous DBAs (database administrators). Even those who use Excel, even advanced, find it hard to get to work with Access, and simply stop using it.
But let's start with an example: Imagine that you drive every day to your job by a long way, and wake up early every day to arrive on time. And then, suppose somebody shows you a new road, much shorter, and also safe, which allows you to also go to your business. What are you going to do?
Of course, you choose the fastest route, or rather the best way to do the task of going to work. Of course you always have the long road there, if you want to use. But at least you know that there is a new road!
More or less this is the analogy that applies when we discovered a software that can help us do better or faster our work.
See however, returning to the example of the way to work, although it is longer, you have full confidence, knows all the curves and hollows. In the new way, you still need to familiarize yourself.
And this also happens with Access. Once you practice a few times, and become familiar with it, get to know your potential, then the limit will be your needs and creativity.
Note: of course, Access does not replace anything. It's just another tool you can use in your set of tools you already use on a daily basis.
So here we go...
Microsoft Acces is part of the Office Suite, from Microsoft. You've probably used Word, and also Excel and PowerPoint.
For more information, please visit the Microsoft Web page: We will not stay here too getting the basics, explaining tables, relationships, etc.. As always, let's cut to the chase. This is not a course in Access, but Access tips.
As the Hunter SDK is based largely on Microsoft Access with VBA, Hunter tutorials contains detailed explanations whenever a new action (create table, create query ...) is required. So even if you do not have interest in using the Hunter SDK, we recommend reading these tutorials, which contains great tips for using Access.
Let's go to the point?
Our series of tips for 'Program for Telecom' always presents practical situations, using examples. In this case, the files can be downloaded for free.
Let's start today with a tip that involves distance calculations from a table with the coordinates of sites.
Find Sites closer to each site in a List
To make it easier to understand, see the following figure, with eight sites from A to H. What site (point) is closest to 'A' site?
Sure, it's hard to say. And if you know the Latitude / Longitude, help?
Not yet, right?
So you can say: Give this table in Excel, and I answer.
Okay, so let's do. For convenience, we use the formula for calculating the approximate distance between coordinates.
Note: The precise calculation of the distance between points with different coordinates must be done using various factors such as the radius of the earth, among others. However, the trigonometric approximation in most cases is more than enough. Even because the difference between the result of the full calculation and approximate calculation is minimal, much lower tolerance for example that we own on the accuracy of GPS were used to obtain the coordinate of each site.
In the Hunter Sectors GE Network tutorial, we learned how to calculate the coordinates of a point from a certain distance (in this case is the distance sector plotted on Google Earth or Mapinfo).
Today we have 2 points (with coordinates, and we need to calculate the distance). In our approach, we use the mean radius of earth = 6371.1 km.
For example, to calculate the distance between Site A and Site B, we have:
In Excel, the formula for calculating the distance between two points of coordinates is as we see below.
We can then use this formula in our spreadsheet, remembering to set the mean radius of Earth, and the coordinates of site 'A' - just varying the coordinates of the Final Site.
Okay. And now someone asks: 'And what is the site closest to the Site F?'.
We can continue doing in Excel, give a little more work, and quite unnecessary replication. But let's enjoy and see how to do this in Access?
First, insert this table in Access. (There are several ways, as seen in the Hunter tutorials, the fastest is to select all data in Excel spreadsheet, and paste into a blank database in Access. It's that simple).
Okay, we already have the table in the database: 'telecomhall_tips' - the name comes from the name of the worksheet tab attached. We can change, but it's unnecessary.
First, let's create a query based on that table with the name 'qry_Sites'. This query shows us the list of sites, and it is with this query that will work - instead of the table.
We then created another query 'qry_Sites_Coords_ini'. Contains the coordinates of all sites, with the suffix '_ini'.
And do the same for 'fin' (New queries and fields).
Maybe you're still not aware where we want to go, maybe a little lost. But see that until now, we are working with simple queries, our data recordsets.
When working with recordset, it is as if we had available for each query, a snapshot of the data on a large screen. And you can create queries based on queries - including groupings and calculations (maximum, minimum, sum, etc. ...). We can even use functions in calculated fields - just as we do a formula in Excel. And yet we can create our own functions.
The great secret of the Access database is using SQL. What Access does is make it easy to create SQL queries - through its User Interface.
Note: of course there are differences between an Access database and a database MSSQL or Oracle. But that's not the case now, and Access is enough for anyone working with Telecom.
Using SQL, Access recordsets allows us to create cross-reference tables or queries type UNION, among other, more advanced. Only this is not our subject today, in fact, we are out of focus - let's go back to our example.
So we have two recordsets:
Here, we use a trick: When you create a query that involves more than one table and / or queries, they should be linked by some relationship or joint, otherwise the data will be repeated - for each record in a table / query, we'll have all other data in the another table / query.
Easy: it's not easy to understand. Especially if you are not familiar with Access. But we will continue, and perhaps with the result, it is easier to see.
Then, create a query 'qry_Sites_Coords_Distance'. And we simply add our two queries. Without making any kind of tie between them.
Let's cut to the chase: run the query and see the result.
Note that for each row of the first query (for example in the first table we have a record with name = A) we have all the other lines of another query (A ... H). And on the same line of each, the included calculated field 'Distance'.
And then, this table (query) does not help us a lot?
We will continue a little longer. Now based on our current query, we create a new query 'qry_Sites_Coords_Distance_Min'. In this query we have:
name_ini: the name of each site grouped (only one A, only one B. ..);
MinDistance: calculated field with the shortest distance (each name_ini - A, B, C. ..);
And one condition: Where name_ini is different of name_fin (in the same record / line). That's because when it occurs, for example name_ini = A and name_fin = A, we have distance = 0. That is, it serves to filter excluding the records of our recordset that does not interest us.
Note: This filter criterion away could have already been applied in the previous query, and here we would not need more of this clause 'where'.
Running the query, we have our work available to a recordset.
Finally, we create our final query 'qry_Sites_Coords_Distance_Closest_FINAL'.
Well, there is the expected result. For each site (name_ini A, B, C. ..) have the closest site (name_fin) and its respective distance. For example, the H site is the closest to the site, to 1807 meters!
What if I have more sites?
Then you might ask: But how is the performance? What if I have many records (sites), will it be slow - as macros in Excel are slow with large data?
The answer is No! Access is much more powerful than Excel, especially when it comes to process more records at once.
Take the test yourself, place (much) more records in the original table, and run the same query again.
Hopefully with this tutorial you can see the Access otherwise - not as a complex tool, but as a powerful aid to your daily work.
To download files from this tutorial, click here.
Note: The Hunter SDK is a set of tools designed and demonstrated in telecomHall - including Macros and Codes. The Hunter SDK is only sent to telecomHall Donors, as a thank you for the contribution they made and recognition of our dedicated effort. Click here if you want to know more.
This was a brief introduction to using Microsoft Access software in Telecom real world.
Today we saw how by creating simple queries (though it may seem complex to those who are having the first contact) we can find the site closest to each site in a listing.
Thanks for your company, and until our next meeting!