Finding the lowest non-zero value of multiple columns in Microsoft Access

Programming | Posted by epistasis
Jul 01 2014

Recently (for work) I wanted to find the lowest non-zero value of multiple columns in Microsoft Access. I had to trawl the Internet for the best way to do this and in the end worked with a mixture of several methods from different websites. I hope that this post will serve as a single point of reference for anybody wanting to do the same thing.

In this example I will use the following table:

003

 

1) First you need to open the VB editor:

001

2) Create a new module:

002

3) Paste in this code:

Function MinColumn(ParamArray FieldArray() As Variant)

Dim I As Integer
Dim currentVal As Variant

currentVal = FieldArray(0)

'Loop through each value in the columns
For I = 0 To UBound(FieldArray)
    'If the the column we're checking is lower than the last (and non-zero), take it
    If FieldArray(I) < currentVal And FieldArray(I) <> 0 Then
        currentVal = FieldArray(I)
    End If
    
    'If the current value is zero and we find a higher value, take it
    If currentVal = 0 And FieldArray(I) Then
        currentVal = FieldArray(I)
    End If
Next I

MinColumn = currentVal

End Function

4) Close the VB editor

5) Create this query:

SELECT EmployeeLastName,
MinColumn(EmployeeRate1, EmployeeRate2, EmployeeRate3, EmployeeRate4) AS LowestRate
FROM Employee

You should end up with the following results:

004

 

Haircut!

Other | Posted by epistasis
Jan 11 2012

I walked into work this morning with a long pony tail, and I’ve left this evening without it…

Didn’t see that one coming! Well I sort of did.. but that doesn’t quell the disbelief…

And I have proof I tell you!

Researching PIM/PKM Perfection

Personal Knowledge Management | Posted by epistasis
Nov 23 2011

Introduction

I’ve recently started using MediaWiki [1] as a ‘Personal Wiki’ to contain my thoughts and important information that I may need to recall at some point in the future (guides, note fragments, etc). I’ve added the Semantic MediaWiki [2] extension to make the content more searchable and so on. I plan to write my own extension called MediaWikiPIM which will sync Contacts & Calendar data into the wiki from my Google account. This will allow me to keep all my data archived in my wiki and search it etc.
Read the rest of this entry »

MySQL Backup & Restore Woes

FreeBSD | Posted by epistasis
Nov 03 2011

First things first… during my frantic search for answers, I read a statement similar to this:

“Anybody who’s come across this post looking for information on how to restore their databases, don’t worry – your data is probably not lost.”

In hindsight, I would likely have been much more effective if remembered this, therefore I’ve put it at the top of this post.

Background

My problem arose when I recently came around to the idea of reinstalling one of my servers. It was running FreeBSD 5.2 x86 and I wanted to upgrade to 8.2 x64. It was with great hesitance that I ended the 521 day uptime record.

Read the rest of this entry »

Have you seen this man?

Uncategorized | Posted by epistasis
Apr 03 2010
Vote Gordon!

Vote Gordon!

I was walking down Weston Road to meet my girlfriend at the train station and saw this billboard. I thought it was hilarious!

Chrome OS Cherry

Technology | Posted by epistasis
Dec 07 2009

So I downloaded Chrome OS (yes it’s incomplete – obviously) and played around with it. The idea is to be commended. It’s basically the Chrome browser… as an OS. You can pick up a version that can be written to a USB stick and then ran (live) from:

http://chromeos.hexxeh.net/index.php

All round thumbs up!

StaffsLUG

Other | Posted by epistasis
Nov 16 2009

Went to my first StaffsLUG meeting tonight… Met an interesting bunch of people..

Ate an amazing burger..

All in all, good times.

e.

Along comes G.hn…

Home Automation | Posted by epistasis
Oct 20 2009

Good news for smart home enthusiasts everywhere then I think? This week, the International Telecommunication Union (ITU) approved the standard. This seems like amazing news and  I personally can’t wait for it to work it’s way into our gadgetry. If anybody would like to leave some comments on any bright ideas they’ve had concerning the protocol then please do 🙂

“G.hn is the next generation standard for existing-wire home networking (a wired and complementary counterpart to the popular Wi-Fi wireless home networking standard). G.hn targets gigabit per second data rates[2] and operation over all three types of legacy home wires: phone wires, coaxial cables and power lines. As the majority of devices in which G.hn will be embedded (such as televisions, set-top boxes, residential gateways, personal computers or network-attached storage devices) will be AC-powered, configurations that have at least one power line networking interface are likely to become the most common. This will also facilitate integration with home control and demand side management applications for AC-powered appliances.” (Wikipedia, 2009)

FreeBSD + pf problem with window scaling

FreeBSD, Problems, Windows | Posted by epistasis
Sep 13 2009

I’ve been having problems for a while with window scaling on my network. I run a FreeBSD router utilising pf for NAT/firewall. Some computers (those with Vista, Windows 7, or recent Ubuntu releases installed) could not establish connections to Google and some other websites, and applications such as Windows Live (MSN) Messenger could not establish connections.

On Windows Vista and Windows 7, disabling window scaling (also called Receive Window Auto-Tuning) with the following command was a successful patch for the problem:

netsh interface tcp set global autotuninglevel=disabled

In Ubuntu the same patch was to modify /etc/sysctl.conf to disable window scaling. I added the following line:

net.ipv4.tcp_window_scaling: 0

This was only a temporary fix however as I did not want to have to disable the feature all the time.

To properly support TCP window scaling, pf must create a state on the initial SYN packet of connection.

If the state is created on a subsequent packet (like when the SYN is  accidentally passed without creating a state, and the state is created on the returning SYN+ACK), pf has missed the window scaling negotiation
containing the scaling factors, and will eventually stall connections. Each peer’s scaling factor is only seen in its SYN packet, and can’t be deduced later on.

Check your ruleset and verify that

a) there is a default block policy
b) all ‘pass’ rules applying to TCP have both ‘keep state’ and ‘flags S/SA’.

That was it! Vista, 7 and Ubuntu were happy again, with default settings so I was able to re-enable Windows auto-tuning by typing:

netsh interface tcp set global autotuninglevel=normal

and for Ubuntu, I was able to erase the line in /etc/sysctl.conf.

Finally…

Windows | Posted by epistasis
Aug 10 2009

Ok so I’ve just moved house and I’m currently settling into a new place…

This weekend I will be getting my hands on a ‘legit’ RTM version of Windows 7, so I’ll be evaluating that for a while now. Will probably give my opinion during next week.

Likely going to try and find myself a cheap, low-spec 64-bit machine so that I can try out sever 08 R2 on it..