Rob's Rants

This is a bit embarrassing — mostly because of how long it took me to find the simple answer.

Anyway — so I can remember it and for anyone else having the problem, here’s what happened and how it was solved.

Problem

I needed to execute a query in an Access database which included a subset of user IDs in a table, by excluding the user IDs contained in another table.

Essentially, I was looking at login events in a table which captured all the users who logged into the system over a period of time. The data table contained two columns: one with the user name and another with the timestamp of the login. The problem is that mixed into the logins are numerous test accounts and employees that I don’t want to report. I keep a table of these test and employee IDs, and I wanted to extract only the actual users from the first table while excluding the test and employee accounts in the second table.

How To Do It

Essentially, we are creating a query to find unmatched records, and it can be built the same way that the unmatched records wizard does it. Here’s an example.

So, to make the exclusion happen, we need to build a query. Problem is, the standard query builder in Access isn’t up to the task. For this, we will need to write the SQL ourselves. Essentially we write a left join between the two tables equating the elements with the username, and find any rows where the value in the right table is NULL.

The syntax looks like this:

SELECT Login_Data.UserName, Login_Data.LoginEvent FROM Login_Data
LEFT JOIN Test_Accounts ON Login_Data.UserName = Test_Accounts.Name
WHERE Test_Accounts.Name IS NULL

Two quick notes about useful stuff I’ve found this week.

1. Lorem Ipsum text in Word
2. Running programs as another user in Vista

First, I needed a placeholder in a document and I usually use Lorem Ipsum text (see http://www.lipsum.com/ for an explanation and a text generator). I had forgotten, but thanks to Google and the authors at How-to Geek remembered, that Word 2007 has a built in Lorem Ipsum generator. It is simply inserted like an Excel function with the syntax: =lorem([paragraphs],[sentences]). Note that both parameters are optional so a simple =lorem() gives you 3 paragraphs of 2 sentences each. Sweet

The second issue was that I was running as one user on my laptop and I needed to run a program as another user. In XP and prior, this was simple — there is a “run as” command in the context (right-click) menu for any program icon. But that was removed in Vista (probably too convenient). A quick search reminded me of the ‘runas’ shell command which you can run from the command line. That worked for me in this instance. But I wanted something more convenient. My searches turned up this article on Groovypost which explains one way to get your content menu command back. Double sweet.

Just thought you should know.