B-tree or Bitmap

Recently I read a lot about databases, mostly theory but also from the design point of view. I have to admit that until recently I had no idea what a B-tree is. I mean beside that it is a kind of a data structure, but how it is build and why on average there is ...

No Duplicates in Subset

Yesterday I was helping a friend of mine with an interesting problem. There is a big table ~4MLN rows, it is wide (has many columns, lets say 30). We are interested in getting rows, which contain duplicates in 5 columns, we do not care what data are in other columns, at least not with regard ...

Adding DSN with VBA

Access is a popular choice for a database GUI. There is no need to know how to connect with a specific database from Access as long as we know how to use an ODBC connection. To make everything fully automated it is even possible to add such a connection with a simple VBA script, maybe ...

Shortcuts in Excel

Knowing Excel shortcuts is one of the features that can save you hours every week, provided you use Excel a lot. Beside very typical and well known like CTRL+C and CTRL+V there are dozens of other. There is no other way to remember all of them than practising. At my previous work I was amazed ...

Paste Faster into Excel

Excel and Access often do come along. When you have make them work combining one database with dozens of Excel sheets it is necessary to be able to read and write data fast, otherwise it becomes annoying pretty fast. There are several ways to apporach this problem. Reading Excel sheet into Access can be easily ...

Fill To The Next

Fill To The Next
Pivot tables are very useful, but later on it might get really boring filling the missing cells in the first columns. For example given a table like the one in the picture below

The Query is You

Today at Bloomberg I saw interesting interview with Marissa Mayer, the Yahoo!’s CEO. She talked about what Yahoo! was, what it is now, and what it will be. Basically it is the same thing. The only difference between now and then is the context, and main driver of changes. Yahoo! used to be a directory, ...

The Software I Use

Everybody has their favourite set of tools they use. Here is mine. Most of them are probably quite popular, but in case somebody did not know I list most of them here. Regarding the browser, on my laptop I have Internet Explorer, Firefox, Chorme, and Opera. Most of the time I use Chrome, but I ...

Speed-up VBA in Excel

There are various means of adding some speed to your VBA code. Probably not the most obvious one, but try to keep track of types of variables, define them explicitly. There is an option, which you put at the beginning of the file which will help you with it Option Explicit Moreover, there are different ...

Rounding in VBA and Excel

You never know how Microsoft may surprise you. If you think that everything is so obvious try using Round function as an Excel formula and in VBA. Typing in spreadsheet =Round(2.5, 0) will give you as a result 3. While “the same” function in VBA ? Round(2.5, 0) will result in 2. This feature  was included ...

Few Words About Indexes

Writing the previous post it came to my mind, that many people may not be aware that using custom functions in WHERE clauses may be the reason why the index is not used. There are several facts about indexes you should keep in mind: index is most often a b-tree, see one of the Princeton’s lectures ...

Regular Expressions in Excel or Access

If you do not want to restrict yourself to just like or equality in Access WHERE clauses, or you just want to be able to embed typical regular expressions in your Excel formulas, then try this short piece of VBA code. This code was my answer to one of the questions at StackOverflow. Before you ...

Hello World!

This is my first blog post, and also the first blog ever. I will try to write here mostly about technology and programming. Sometimes I will write few words about finance or photography, as those are other topics I read a lot about. From the technology recently got intereseted in Hadoop-like distributed systems, and everything ...