Windows Console in Explorer Context Menu

You can use power toys 'Open Command Window Here' for Windows XP to open a command window pointing directly at the selected folder.

EDIT

I'm not a Windows 7 user (yet) but apparently if you press the 'Shift' key and right-click on any folder, you get some extra options like "Open Command Window Here".

See here for more info on this, it demonstrates how to enable this feature without pressing shift using some registry tweaks.

The following text, when entered into a .reg file and launched, will do it:

Windows Registry Editor Version 5.00

[HKEY_CLASSES_ROOT\Directory\shell\CommandPrompt]
@="Open Command Window Here"

[HKEY_CLASSES_ROOT\Directory\shell\CommandPrompt\command]
@="cmd.exe /k pushd %L"

You can do this in the registry (normal "editing the registry" warnings apply)

1. Create a new key under HKLM\Software\Classes\Folder\Shell, name it Command Prompt. Set the default string value to whatever you want to see in the context menu when you right-click on a folder
2. Create a new key under the one you created in step 1 and call it command
3. Set the default value of the key you created in step 2 to this:
   %SystemRoot%\system32\cmd.exe /k pushd %1

Now when you right-click a folder you'll have an item that says Command Prompt that will open a cmd prompt window to that folder. You can use any valid reg string for the key name in step 1.