Hello, we will continue today with a series of tips on MS Access to Telecom and IT. Although I do not like to publish tips followed on the same topic, a large number of readers asked, so let's wake.
Note: All telecomHall articles are originally written in Portuguese. Following we translate to English and Spanish. As our time is short, maybe you find some typos (sometimes we just use the automatic translator, with only a final and 'quick' review). We apologize and we have an understanding of our effort. If you want to contribute translating / correcting of these languages, or even creating and publishing your tutorials, please contact us: contact.
In addition, knowing and learning more about MS Access we are increasingly prepared to create powerful suites like the Hunter SDK.
Let's follow the same line, featuring an actual Telecom, and then as a solution in MS Access.
So let's cut to the chase.
From a table with data unfriendly, get another one with the data in proper format - desired.
In this case, we assume that we have in our network two tables: one with performance information (traffic) in a standard format, and other, information network (physical data). The latter however, an unconventional format, though possible.
Do not worry, the goal here today is just to illustrate the procedure, so the tables are extremely simple - just data.
Table Performance x Table Network
Suppose then, the two following tables, with data from your network.
Note that both tables have data for the same sectors (3 sectors of the BTS GAAA1, and the two sectors of the BTS GBBB1).
The first table has performance data - traffic - for the five sectors, where each row represents a sector.
The second is network data, but has only one line for each BTS, and the columns with the following data for the respective sectors.
So, as you would for the final table below?
Well, this is the example we'll find out today how to do it very simply in MS Access.
Note: try this 'transformation' using any other program such as Excel. You'll see it's not as simple as it sounds!
For starters - do not worry, you'll see it's not so complicated when we know what to do.
Then, in an empty database from MS Access, we add the two tables - using CTRL + C / CTRL + V as seen in previous turoriais. Note: Remember that when we glue tables this way, they bear the name of the source Excel guide. Thus, rename tables respectively for tbl_Performance and tbl_Network.
Okay, now create a query right?
Yes, in parts. Let's create a query in Access that will work for us. But today, we will not use the standard queries. Let us know - or remember for those who use the Hunter - a different kind of query: the query like UNION.
This is a very powerful type of query - for who knows how to use. Allows, among other things, that we transform our data in different dimensions - as in our case today, where we have a table with data in vertical and one horizontal with data.
The only 'detail' is that this type of query has no graphical representation, ie, we can not do everything through the user interface of MS Access. And yes, we have to write SQL.
But read on, I guarantee it will be worth it.
Well, if I were doing this, I would write the final SQL query.
But as we have different levels of readers, let's share.
First, we create a query (Query1) with data from our table of performance.
And let's see how the SQL query that simple. To do this, access the menu: Design -> View -> SQL View.
And we have the SQL (formatted in a row, just to make it easier to explain).
Ie: Select the fields 'sitename', 'TRX_cell1', 'TRX_cell2' and 'TRX_cell3' tbl_Network table.
But if we execute this query, we see that we have the same data table. And that is not useful for anything!
Then go back in SQL, and delete the fields 'TRX_cell2' and 'TRX_cell3' (you can do this in GUI if desired):
And rerun the query:
It's getting better, right?
:: We have a table with the data of the first sectors (cell1) of each BTS.
So now let's just insert a field - cell - and assign the value '1 '. Again, you can do directly in SQL, or the graphical interface. SQL with the addition of a field 'cell' with value '1 'looks like this:
Note: It is better to do in SQL, because the final steps can only be done there.
Running the query, we have:
We are almost there for the first sector. Only now can give a more generic name to the 'TRX_cell1' - because the information sector can be seen in a cell field.
Then, modify the SQL to make this generic name - 'TRX_cell' - to the 'TRX_cell1' query.
And running, we have the result ready for a sector of all BTS.
Well, so far we have not done anything extraordinary, and this query, then, it can be done through the GUI without any problems.
But now it gets interesting.
A short pause. Let's try to imagine what it must do to join that query data from two industry?
From the current SQL query, we need to put everything together (UNION ALL) again within the 'sitename' and now the 'TRX_cell2' of the same table where we are selecting the data.
Note: The names of the UNION ALL query fields are defined by the names defined in the first line. In other lines, no longer need to put the name 'cell' or the name 'TRX'.
Thus, the final query is like this:
You can type, use the text area to copy and paste and then make the necessary changes, or use a Notepad and paste the entire query here. And in very complex cases, more specific, you can even make a template in Excel and paste here. But realize that it's not complicated.
Just then pasting many lines you want to join all (UNION ALL) the indicated fields.
The query now shows the data from sectors 1 and 2, accordingly.
And just as we join the data from sector 2, sector 3 to make (and how many more sectors or columns exist in the original table).
Note finally two things: the table is not 'ordered' - we have the first data of the first sectors after the data of the second sectors, etc.. But this is no problem, the corresponding values are, see for yourself.
Also, when we do not have data for a particular column, it is empty. This can be easily circumvented by specifying a field critéiro 'different' from empty. But this is not necessary in our example, as we will soon log.
So come on, and finally get our final table.
Note: See the tutorial was somewhat extended, because it explains every detail. But day by day, you'll soon be creating the type UNION ALL queries like any other query!
Then save this query - for example as qry_Network_UNION.
Now create a second query qry_FINAL. Drag the table and query tbl_Performance qry_Network_UNION for it.
Connect the fields sitename and cell and drag the desired fields to the fields of the query output.
Ready. Run the query and see the expected result!
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 more a hint to use the Access Software, Microsoft, with applications in IT and Telecom.
However, the scope is not limited to those areas only. Learning to use, and knowing the best practices allow you to easily create solutions really powerful.
That's our goal, always looking for and we will continue to present the best content, always being practical and going straight to the point - only to be seen what matters.
Thanks for the company, and until our next meeting!