Syndicate content

column

Can I create an INDEX to accelerate a SELECT MIN(IF(...)) in MySQL?

I have a table with a few columns, two of which are integers, one of which can be NULL in which case the other integer is to be used in my query.

CREATE TABLE my_table (col1 INTEGER NOT NULL, col2 INTEGER);

If `col2` is NULL, then use `col1`. As is, there is how my query looks like:

SELECT MIN(IF(col2, col2, col1)) AS smallest FROM my_table LIMIT 1;

In other words, I need to get the row with the smallest integer, but if one column is NULL, I want to use the other column value.

On a very small sample, the query is still very fast, but the real version is not unlikely to have thousands ...

Text import wizard in Excel 2010 not coming up with .csv file

As I was working on a table extracted from a PDF file, I wanted to load that table in Excel. By default it would put all the fields in column A. Not good. So I put that in a text file, added a pipe at the end of each time and then join lines really quick (with vim, it's J to join lines.)

Now I had a nice .csv file, with fields separated by a pipe (|) character.

123|Something|More info|This "worked" before?!

Now, time to load that file in Excel. I tried that and it gave me everything cut in "strange" places but especially, no way to select the column separator. Why ...

Freeze Pan — How do you lock a row and/or column in a long Excel spreadsheet?

I have been wondering how to lock a few rows and/or columns on a side in Excel. I have seen that being done many times, but did not know how to do it myself.

In the Windows menu (Before Office 2007) and in the View menu (Since Office 2007) you will find an option named Freeze Panes. You have three selections:

  1. Freeze the selected top rows
  2. Freeze the selected left columns
  3. Freeze the selected top rows and left columns

If you just need one row and/or column, place your cursor in the A1 cell.

[toc hidden:1]

UPDATE using multiple tables

Today I wanted to copy a column from one table to another. One way is to go through the table and update the items one by one. The other way is to use the UPDATE order a copy the data as required.

The UPDATE command does NOT support such a feature by default and thus each database has its own syntax when it comes to how to make that work.

PostgreSQL uses an extra FROM between the SET and WHERE orders.

[toc hide:1]

MS-Access: INSERT INTO syntax error

Today I was creating a report and created a table with a column named 'Group'. It looked like it worked just fine so I moved on with it.

Then, at the point I wanted to insert data with a simple INSERT INTO ... statement, it broke. The statement would generate a syntax error. Yes. The simple answer is that GROUP is viewed as a keyword and thus when used as a field name it needs to be escaped (i.e. written between backward quotes: `...`). Not liking the need to escape a field name each time I use it, I just renamed the field which is even better.

The bad thing about GetRows()

In MS-Access you can create a Recordset using the OpenRecordset on a database.

Once you have that record set, you can get the rows with the GetRows() function.

It has one advantage: once the function returns the data remains consistent (it is in an array). The really bad part is... what is returned is an array. This means all the fields are now numbered instead of named.

Hiding columns in a Datasheet form

I have been looking for a way to hide some columns in a datasheet form.

The main reason to hide columns is to include data that you need whenever the user clicks somewhere, but that data is too technical to be shown. For instance, if you work with QODBC you have the ListID and TxnID to keep around but both look like GUID and thus are much better hidden from an every day user view.

The reason why I'm bringing this up is simple: whenever you have a field in a form that you want to hide, you click on it, go to the Format properties of that control and select Visible: No, I use that to ...

Editing of a MS-Access table

Whenever you want the end user to edit a table, you create a form that shows a set of cells. These are defined in your form Details area. Up to here, nothing special about that.

The fact is you cannot easilly access rows individually. That is, the TextBox, ComboBox, etc. in each row is considered the same whatever the row. So if you change the background color of a TextBox, all the TextBoxes of that column will be highlighted, not just the current row.

Calculate a Sum in MS-Access Datasheet: Focus and Recordset problems

Simple Sum

When you create a form in MS-Access and include a sub-form with a table in it, you may want to compute the sum of a field. This is done simply by creating a TextBox and writing the following in that box content:

=Sum([Column-name])

Where "Column-name" is (obviously) the name of the column you want to sum up.

This is neat, but it breaks if the specified column has an unsupported type. Very strange, but for me it breaks with a currency column! That feels a bit weak to say the least. According to my testing and what I have found on the net, it should work with any ...

Per Row Highlighting in MS-Access Forms

Today, I found out that the current row in a MS-Access form is not so current.

Somehow, it can extract the data from the current row, but trying to change some parameters on the current box actually affects the entire column. This is because a ContinuousForm includes only one box per column and that box is repeated for each row.

Syndicate content Syndicate content

Diverse Realty

Diverse Realty Team

Want a New Home?
Want to Sell Your House?

Call Alex at
+1 (916)
220 6482

Alexis Wilke, Realtor
Salesperson
Lic. # 02024063

Cory Marcus, Broker
Lic. # 01079165

     

Terms of Site Index

Find the page/content you are looking for with our index.