Selectable – The QueryCell Blog

These three banners all ran on a website targeted squarely at computer geeks.

It seems that banner ads designed to look like part of the site itself are more effective, even with technically savvy audiences.

Is there something ethically wrong with running banner ads designed to look like part of the website ?  I’m not sure.

Banner 1 – Designed to look good

Creative Name
Impressions Delivered
Clicks Recorded
Click Rate
Rounded

Banner1.gif
37225
46
0.123572868
0.13

Banner 2 – Designed fit in seamlessly with the website
… Continue Reading

Hi

Just a quick post today.

So what is the main difference between Microsoft Excel and Relational Databases such as MS Access, SQL Server, Oracle, DB2, Postgresql, Mysql, Firebird etc ?

The main difference is that Excel focuses on individual data items, cells, a databases focus on collections of data items, rows.

Think about how you edit data within Excel, you select the cell you are interested in and modify the value. In a database data is edited (updated in database parlance) by using an Update Statement. Update statements operate on all rows that match the specified criteria.

Of course you … Continue Reading

The recently released QueryCell V1.5 adds a visual query builder.

QueryCell Visual Query Builder

Being a long time SQL user and happy writing code I resisted adding a visual builder to QueryCell for a long time.  I was wrong to resist.

Users of QueryCell repeatedly told me they wanted a visual builder so they could create select statements, joins and aggregates without having to type it all up manually.

I think the new visual query builder has struck a good balance between the power of composing SQL directly and the convenience of … Continue Reading

Suppose you wanted to know whether a cell contained one of four permissible values :

NO SALE
GIFT
SL-XXXX

Where XXXX is an integer value. ‘SL-9839′ for example.

In Excel you would be able to use a formula such as

=OR(ISNUMBER(MATCH(“GIFT”,A7,0)),ISNUMBER(MATCH(“NO SALE”,A7,0)),ISNUMBER(MATCH(“SL-$???”,A7,0)))

A7 – The cell whose value you wish to check
Match :
The Match function searches for a value in an array and returns the relative position of that item.
If no match is found it will return a #N/A error.
Match( value, array, match_type )
value is the value to search for in the array.
array is a range of cells that contains the value that you are … Continue Reading

Question

I have a table of data containing an employee name and the date they were hired, how can I get Excel to calculate the length of employment of each employee based on the date they were hired ?

Answer :

Well suppose your excel table looks like this :

Employee_Name
Hired_date

J Smith
10/07/1980

A Jones
6/03/2008

R Shah
12/12/2007

B Chen
15/02/2009

I Holmes
1/01/2010

** Note : **
We are going to be using the database DATEDIFF function. The column containing the date value must be named
something ending in _DATE in order for QueryCell to realise it … Continue Reading

Question

I sell widgets, I have a table of orders which includes the number of widgets for that order.

I would like to generate a discount percentage for each order based on the rule :

1 – If the order is for 10 or less widgets the discount is 10%
2 – For every 5 additional wigets I want the discount to go up by a percent.

So an order of 10 widgets gets a 10% discount, an order of 15 widgets gets an 11% discount, an order of 20 widgets gets
a discount of 12% etc…

Answer :

Well suppose … Continue Reading

Continuing on from the last post we are going to look at the StackOverflow data to see which SQL related questions are garnering the most interest.

Firstly we will look at the top five SQL related questions the questions based on the number of comments they have received.

Question
Num Comments
Selectable Blog Comment

I have SQL SERVER database that have a huge proliferation of stored procedures. In my Oracle databases it’s not a problem because Oracle supports packages.

Even if SQL Server didn’t support the cool features of packages like State and encapsulation, what do programmers do to get around this … Continue Reading

There are plenty of communities and forums that can help with your SQL and Excel problems.

ExcelForum , Mr Excel and OzGrid are a couple of examples of the best Excel communities around, but there are many out there.

One of the most recent and popular communities to spring up is StackOverflow

StackOverflow is a general question and answer site for technical programming problems.

Since StackOverflow opens up it’s data for public analysis we can use it to get an insight into the nature of Excel and SQL users as part of the overall programming / technical community.

To do this … Continue Reading

Allan and Wilson books is going to be presenting information about their new titles to distributors and book stores all around the country.  Read how PowerMerge and QueryCell help them do it quicker, easier and with less mistakes.

My name is Jenny Malhouse and I’m the general IT manager at Allan and Wilson books.  When I say IT Manager I mean manager, developer, administrator, innovator and dogsbody.  Since we are a small ( but growing ) company everyone has to pitch in where they can. I find myself handling most of the computer issues that crop up.

Over a few months our … Continue Reading

I’m going to take a look at the professions and university degrees of national leaders and see if anything interesting comes out.
Notes :
I looked at the leader of a country on the 19th day of the 9th month of the 9th year of every decade.

The countries I included were United States, United Kingdom, China, Australia, France and India.

For India I thought it reasonable to include prime ministers since independance, starting with
Nehru in 1949.

The Chinese history was by far the most confusing. There have been presidents,
Chairmen of the Communist Party, General Secretaries and … Continue Reading