Access Programmer - Orlando Gonzalez

Filter Combobox based on Another Combobox

2009-05-03T13:34:00.011-04:00

How to synchronize two combo boxes in a Microsoft Access Form.

Watch the video and/or follow the step by step instructions below (this will take about 15 minutes of your time but you will get what you came for!)

Requirements

This example uses MS Access 2007 but it should also work in previous versions.
You will need six objects in your database:

One form: frmMain
Two combo boxes: The Master combo box: cboProjects and The Slave combo box: cboProjectTasks
One table: tblProjectsAndTasks
Two queries: The Master query: qryProjects and The Slave query: qryProjectTasks

STEP 1: Create the Form:

Create a new form and name it frmMain.
Save the form.

STEP 2: Create the combo boxes:

Create the Master combo box.
Name it: cboProjects
Create the Slave combo box.
Name it: cboProjectTasks
Save the form.

STEP 3: Create the data table:

Create a table and name it: tblProjectsAndTasks
Add these FieldNames (and DataTypes):

TaskID (Autonumber)
ProjectName (Text)
TaskName (Text)

Populate the table.

In this example the projects are: Alpha, Delta and Gamma.
And each project has multiple tasks, for example:

Project Alpha Task 1
Project Alpha Task 2
Project Gamma Task 3

STEP 4: Create the master query:

The Master Query should be named: qryProjects
This query will select unique project names from tblProjectsAndTasks.
It will be the data source for the master combo box.
The SQL code for this query is: SELECT DISTINCT ProjectName FROM tblProjectsAndTasks;
Alternatively, you could use the following SQL code: SELECT ProjectName FROM tblProjectsAndTasks GROUP BY ProjectName;
Either way, the output should be:

Project Alpha
Project Delta
Project Gamma

STEP 5: Create the Slave Query:

Create a new query and name it: qryProjectTasks
This query will show tasks related to the project that is selected in cboProjects.
The SQL code for this query is: SELECT DISTINCT ProjectName, TaskName FROM tblProjectsAndTasks WHERE ProjectName =[forms]![frmMain].[cboProjects].[value];
At this point, running this query will not return any results because the WHERE clause is looking for the project selected in cboProjects, which is not data bound yet!

STEP 6: Data Binding the Master Combo Box:

Give cboProjects these properties:

Row Source: qryProjects
Row Source Type: Table/Query
Column Count: 1
Bound Column: 1

STEP 7: Data Binding the Slave Combo Box:

Give cboProjectTasks these properties:

Row Source: qryProjectTasks
Row Source Type: Table/Query
Column Count: 2
Column Width: 0";1"
Bound Column: 2

STEP 8: Refreshing the Slave Combo Box:

The slave combo box needs to be refreshed after each change in the master combo box. The VBA procedure below will refresh the slave combo box.
This procedure should be tied to the On Change event of the master combo box. Private Sub cboProjects_Change() Me.cboProjectTasks.Requery End Sub

STEP 9: Clearing the Slave Combo Box:

Refreshing the combo box does not clear the current task selected.

Modify the VBA procedure to clear the combo box:

Private Sub cboProjects_Change()

Me.cboProjectTasks.Requery

Me.cboProjectTasks = ""

End Sub

STEP 10: ENJOY!

SQL APPEND TABLE vs. CREATE TABLE

2008-02-28T13:11:00.001-05:00

--TO CREATE A NEW TABLE:

SELECT *
INTO newTableName
FROM sourceTableName

--TO APPEND DATA TO AN EXISTING TABLE:

INSERT INTO existingTableName
SELECT *
FROM sourceTableName

Check for Numeric Values in a String using VBA

2005-11-02T22:45:00.000-05:00

I needed to check that the values entered into a textbox called StoreID in an Access Database where numerical and composed of 3 digits. Here is one solution: on the AfterUpdate event of the textbox, put this code:

Private Sub StoreID_AfterUpdate()
Dim intLen, intChar, i As Integer
Dim strTextBox As String

intLen = Len(StoreID) 'Len is a function that counts the amount of characters in a string
If intLen <> 3 Then ' if the length is not 3, give message box
MsgBox "The field 'Store ID' requires 3 digits from 001 to 900"
Else ' if length is 3 then assign variables and start loop for checking numerical values
intLen = Len(Me!StoreID)
'Me! always refers to the active form
strTextBox = Me!StoreID
For i = 1 To 3 'For 1 to 3
intChar = Mid(strTextBox, i, 1) 'Mid function takes middle values (expression,start value, length of selection)
If IsNumeric(intChar) = False Then
MsgBox " ' " & intChar & " ' is not a number, please enter numeric values only from 001 to 900"
Exit Sub
Else
End If
Next
End If
End Sub

Call an Access Database Procedure from another Access Database

2005-11-02T21:34:00.000-05:00

Here is an Access VBA example of how to call an external Access Database VBA procedure from within a different access database.

Sub ForeignProcedure()

Dim appAccess As Access.Application

' Create instance of Access Application object.
Set appAccess = CreateObject("Access.Application")

' Open MyDatabaseName.mdb database in Microsoft Access window.
appAccess.OpenCurrentDatabase “C:\MyDatabaseName", False
'False so as not to open in exclusive mode
' Run Sub procedure.
appAccess.Run "ForeignProcedureName"
appAccess.Quit
Set appAccess = Nothing

MsgBox "Done!"

End Sub

Call an Access Database VBA Procedure from Excel

2005-11-02T20:53:00.001-05:00

In order to call an Access vba procedure from an Excel spreadsheet, the first thing you need in Excel is a Reference for the Microsoft Access Object Library, as seen in the picture below ...

To see this screen, from within Excel, go to VBA (press ALT + F11), then select Tools from the top menu and then References

Public Sub ProcedureInAccess()

‘this VBA code is run in Excel
Dim acApp As Object
Dim db As Object
Set acApp = CreateObject("Access.Application")
acApp.OpenCurrentDatabase ("C:\MyDatabaseName.mdb")
Set db = acApp
acApp.Run "NameOfProcedureInAccess"
acApp.Quit
Set acApp = Nothing

End Sub

Delete Rows from Access Database Table Using VBA and SQL

2005-11-02T20:34:00.000-05:00

Using the DoCmd property you can delete all rows in an Access Database table using Access VBA and SQL

Sub DeleteRows()

'Deletes all rows from tblName in an Access Database

DoCmd.RunSQL "DELETE tblName.* FROM tblName;"

End Sub

Access Database Programmer Forum

2005-10-09T19:41:00.000-04:00

I recently came across a great resource for Access Database programming. It is a forum where you can post questions and receive knowledgeable answers from the forum's members. http://www.utteraccess.com/

Transaction Log for Microsoft Access Database

2005-09-13T19:34:00.000-04:00

Here is one of the most useful pieces of code I have every come accross the Internet for programming an access database. This code will track all changes made by a user, meaning it will keep track of what data was changed, who changed it and when it was changed. This functionality is usually called a transaction log or a change log. I have copied the code directly from this page:http://www.tek-tips.com/faqs.cfm?fid=291 . The code was written by Michael Red a regular contributor at Tek-tips, which is a wonderful website that I have used many times for finding answers to many programming questions.

I have learned a few things not explained in his original code:

1) Put this code in a Module.
2) Call the basLogTrans function from the BeforeUpdate event of any form that you want to track changes.
3) Pass 3 values to the basLogTrans function: FormName, Name of Field with Unique ID, and the Unique ID.
In my implementation, I use Me as the form name, "CustomerID" as the Unique ID field, and CustomerID to pass the actual customer id value. So here is how I would call the basLogTrans function:

Private Sub Form_BeforeUpdate(Cancel As Integer)

Call basLogTrans(Me, "CustomerId", CustomerId)

End Sub

4) Mike says the code will work only on a form formated as single form, but I see it also works on a datasheet form.
5) He alos says that Access Security needs to be enabled, but I'm not sure it's true. When I used his original code for CurrentUser(), I got back "Admin" as the user that was making changes. But what I really want to know is the NT User ID of whoever is making the changes, therefore, I use Environ("Username"). If you have problems with the Environ function, please see here how to enable the Environ Function properly.
6) The rest of the instructions are pretty clear, but just to be safe, remember to add a reference for DAO, bind the form to a table, but who's to say this won't work on a Select Query? I don't know, why don't you tell me later? Make 2 tables, one called tblHist and the other tblHistMemo. First create tblHist, with the fields indicated in code comments in the function called basAddHist, then make a copy of it, name it tblHistMemo and change the OldVal and NewVal fields to datatype Memo.

That's it, it should work easily.

OK, HERE IS THE ORIGINAL VBA CODE:
All this needs to be placed in a Module, and called from the BeforeUpdate event of a form.

Public Function basLogTrans(Frm As Form, MyKeyName As Variant, MyKey As Variant) As Boolean

'Michael Red 8/1/2002 Transaction log for Ms. Access BOUND forms.

'The following three functions and table definitions are
'(should be) sufficient to establish a 'poor mans' transaction
'log for Ms. Access bound forms.

'As noted in basAddHist, you will need to create the tables
'for History (general fields) and Memo History (history of
'JUST memo fields).

'Also, note that these functions are designed to work in
'conjunction with Ms. Access Bound forms where the view is
'set to single form. Further, the history will only capture
'the user if the db App is "secured".

'In the following, "MyKey" refers to the Record ID (Unique value)
'for the single record which the form is 'tied' to.

Dim MyDb As DAO.Database
Dim MyCtrl As Control
Dim MyMsg As String
Dim Hist As String

'In the Statement block below, basFlgValidRec is a routine which
'includes validation of all fields necessary to 'define' a record
'is valid. Commented out here, as I do not know what validation
'rules need to be checked/enforced.

' If (Not basFlgValidRec) Then
' basLogTrans = False 'Let User know of FAILURE!!
' Exit Sub
' End If

For Each MyCtrl In Frm.Controls
If (basActiveCtrl(MyCtrl)) Then 'Consider replacing w/ test for "ControlSource"
If ((MyCtrl.Value <> MyCtrl.OldValue) _
Or (IsNull(MyCtrl) And Not IsNull(MyCtrl.OldValue)) _
Or (Not IsNull(MyCtrl) And IsNull(MyCtrl.OldValue))) Then
If (MyCtrl.ControlType = dbMemo) Then
Hist = "tblHistMemo"
Else
Hist = "tblHist"
End If
Call basAddHist(Hist, Frm.Name, MyKey.Name, MyCtrl)
End If
End If
Next MyCtrl

basLogTrans = True 'Let User know sucess

End Function
Public Function basActiveCtrl(Ctl As Control) As Boolean
Select Case Ctl.ControlType

Case Is = acTextBox
basActiveCtrl = True

Case Is = acLabel
Case Is = acRectangle
Case Is = acLine
Case Is = acImage
Case Is = acCommandButton
Case Is = acOptionButton
Case Is = acCheckBox
basActiveCtrl = True
Case Is = acOptionGroup
Case Is = acBoundObjectFrame
Case Is = acListBox
basActiveCtrl = True
Case Is = acComboBox
basActiveCtrl = True
Case Is = acSubform
Case Is = acObjectFrame
Case Is = acPageBreak
Case Is = acPage
Case Is = acCustomControl
Case Is = acToggleButton
Case Is = acTabCtl

End Select
End Function
Public Function basAddHist(Hist As String, Frm As String, MyKeyName As String, MyCtrl As Control)

'tblHist
'FrmName Text 80 Name of the form where change Occured
'FldName Text 80 Field Name of the changed value
'dtChg Date/Time 8 Date/Time of Change (MACHINE value!!)
'OldVal Text 255 Field Value BEFORE change
'NewVal Text 255 Field Value after change
'UserId Text 50 User who Made Change
'MyKey Variant ?? KeyField as Indentified by Caller
'MyKeyName Text 80 'Key Field Contents

'tblHistMemo is the same structure except the "type" for the fields
'OldContents and NewContents are Memo (and therfore the length is "??")

Dim dbs As DAO.Database
Dim tblHistTable As DAO.Recordset

Set dbs = CurrentDb
Set tblHistTable = dbs.OpenRecordset(Hist, dbOpenDynaset)

With tblHistTable
.AddNew
!MyKey = Forms(Frm).Controls(MyKeyName)
!MyKeyName = MyKeyName
!frmName = Frm
!FldName = MyCtrl.ControlSource
!dtChg = Now()
' !UserId = Environ("Username") 'Orlando's Way
!UserId =CurrentUser() 'Mike's way, example of current user would be Admin
!OldVal = MyCtrl.OldValue
!NewVal = MyCtrl
.Update
End With

End Function

First and Last Access Function not supported in SQL Server

2005-09-01T20:43:00.000-04:00

In a Microsoft Access database, in order to return the first or last record in the result set returned by a query, you can use the First and Last Functions. The syntax is simple: First(expr) or Last(expr). If you try to use these functions in SQL Server, however, they will not work. You will get a message similar to this:

Server: Msg 195, Level 15, State 10, Line 2
'First' is not a recognized function name.

In order to repeat the desired outcome in SQL Server, simply use the MIN and MAX function.

ACCESS SQL VERSION:
SELECT
ContractorID, FIRST(Contractor)
FROM
tblContractor
WHERE
ContractorID = "ABC6783"
GROUP BY
ContractorID

(T-SQL) SQL SERVER VERSION:
SELECT
ContractorID, MIN(Contractor)
FROM
tblContractor
WHERE
ContractorID = 'ABC6783'
GROUP BY
ContractorID

Loop in VBA Easy Example

2005-08-29T19:40:00.000-04:00

There are different versions of looping in VBA. Here is an example of the Do While version.

Sub letsLoop()
Dim intCounter As Integer

intCounter = 1 'Set the counter to 1

Do While intCounter <3'Loop while value of intCounter less then 3
MsgBox intCounter 'show message box with value of intCounter
intCounter = intCounter + 1 ' increase value of intCounter by 1
Loop 'loop again

End Sub

Capture Signature and Date Stamp in Excel Worksheet

2005-08-15T19:55:00.000-04:00

With all the Sarbanes-Oxley (SOX) requirements going around lately, there has been a big push to making staff attest that they have reviewed the various Excel reports they are sent. One way to do that is to have them sign and date the report and save the report somewhere on the network. Later I will run an Excel macro that goes and looks for everyone’s signature and produces a report of who has signed and when.

Anywho, for the signature portion, I've added a sheet in the Excel reports, the sheet is called Signature. This sheet has a check box. Clicking this check box called chkSignature, populates cells C2 and C3 with the Windows User ID and the Date.

For the user's Windows User ID, we use the method Environ("username"). And for the Date we use well, the Date function.

Here is the code behind the click event of check box chkSignature:

Private Sub chkSignature_Click()

If chkSignature.Value = True Then
Sheets("Signature").Range("C2").Value = Environ("username")
Sheets("Signature").Range("C3").Value = Date
Else
Sheets("Signature").Range("C2").Value = ""
Sheets("Signature").Range("C3").Value = ""
End If

End Sub

Automatically Maximize Access Form

2005-08-10T20:17:00.000-04:00

To maximize an Access form automatically, use the "Maximize" method of the DoCmd Object.

Example, let's say I have a form called MainForm, on the "On Load" Event property of the form, I would use this sub procedure:

Sub MainForm_Load()

DoCmd.Maximize

End Sub

Find the Number of Series in Excel Chart

2005-08-08T19:20:00.000-04:00

While adding a trend line to a group of charts, I needed to know how many data series where in the Graph's Series Collection. I will add an equal amount of trend lines.

Sub countseries()

MsgBox ActiveChart.SeriesCollection.Count

End Sub

Using a Combo Box in Excel

2005-07-25T20:45:00.000-04:00

From the top menu, navigate thru: View / Toolbars / Control Toolbox

When the Control Toolbox appears, Select the Combo Box control. With your mouse, make a rectangular shape on the excel sheet.

Define the choices that will be available in the Combo Box. In this example, the names of fruits are written on Column F from row 1 thru 4.

Tell the combobox where to get it's data from. Right click on the combobox to get it's properties.

Then in the ListFillRange, enter the range of cells that hold the combox values. In this case F1:F4.

That's all there is to it, close design view and your combo box should be working fine.

Securing an Access Database

2005-07-19T19:46:00.000-04:00

A reader from Kuwait wrote the other day...

Orlando,

I was browsing for some help on the net and just saw
your name, I had a question, could you please help me.

My question is, i have developed a database, and i
want to give it to my friend, the question is, i want
to secure the Queries, Form & Reports from being
edited or re-designed, i dont want these to be visible
whenever he uses it. he should be able to print &
update database, but he should not be able to see what
queries i have set as well he should not be able to go
to the design view to modify my forms & Reports.

Could you please help.

Regards,

Oswald
Kuwait

Oswald...

There are a couple of different ways that the above can be accomplished.

1) User Level Security. But I don't recommend it because it is difficult to maintain.

2) A combination of setting the Start Up options, tweaking the properties of the Forms, and making an mde file.

You will have to play around with option number 2 to get the desired results. But here are a few pointers.

From the menu bar, go to Tools / Start Up and un-check "Display Database Window". Next time the database starts, the database window will not be available. If you want to see it, then you would go to Window / Unhide and select the database window. If you choose to not see the database window, you would have to have some form that would serve as the main menu for your database.

Another thing that you could do is hide your database objects, but this is the least effective way, because in the Database Options there is the option to View Hidden Objects. But anyway, here is how you could hide them: Right Click on the object to arrive at the Properties window and check Hidden.

Another alternative is to allow Design Changes on Forms Only in Design View. And to not allow viewing the form any other way then you intended. Forms can be viewed as FORMS, DATASHEETS, PIVOT TABLES, PIVOT CHART. So make sure that in the Forms Format Properties you unselect the view types you do not want the user to use.

Finally, after you have implemented a combination of the tips above, make the database an MDE file. Go to Tools / Database Utilities / Make MDE file.

How to Filter Access Subforms based on a control from a Main Form

2005-07-10T19:22:00.000-04:00

If you are reading this then you have probably noticed that filtering a Subform based on a control from the Main form is a pain in the neck.

Here's one way to filter the subform by changing the underlying form.

Sub frameTaskStatus_Click()
Dim intStat As Integer
intStat = frameTaskStatus.Value
' 1 = all
' -1= closed
' 0 = open
' 2 = new open frmCreateNewRequest

Select Case intStat
Case -1
Me!sbfrmMainRequests.SourceObject = "subfrmRequestsFiltered"
Case 0
Me!sbfrmMainRequests.SourceObject = "subfrmRequestsFiltered"
Case 1
Me!sbfrmMainRequests.SourceObject = "subfrmRequestsAll"
Case 2
Me!sbfrmMainRequests.SourceObject = "subfrmRequestsNew"
End Select

End Sub

Cases -1 and 0 (closed and open, respectively) make subfrmRequestsFiltered the underlying subform. This subform's datasource is a query that applies a filter also based on the value selected in frameTaskStatus.

Case 1 makes subfrmRequestAll the underlying subform in the Main form. subfrmRequestAll is also based on a query but it is not filtered.

Case 2 makes subfrmRequestsNew the underlying subform in the Main form. subfrmRequestsNew is a data entry only form.

How to Determine the Version or Edition of SQL Server

2005-07-06T19:16:00.000-04:00

Open Query Analyzer, connect to any database on your server and type one of the following:

SELECT SERVERPROPERTY('ProductLevel')
SELECT @@VERSION
SELECT SERVERPROPERTY('ProductVersion')

If you are not sure whether you are running an edition of the SQL Server 2000 database engine or MSDE 2000, execute the following query:
SELECT SERVERPROPERTY('Edition')

How to run an Access Macro from Access VBA

2005-06-28T18:28:00.000-04:00

DoCmd.RunMacro "macroName"

A word of Advice for my International Programming Brothers

2005-06-21T19:13:00.000-04:00

Collation is the way in which data will be sorted in SQL Server.

When building SQL Server databases for American clients, be sure to define the database collation as SQL_Latin1_General_CP1_CI_AS, which means Latin characters, case insensitive, accent sensitive. This is the default setting for Windows Desktops in America. If you are developing in another country, your installation of Windows will probably have a different default language setting. If when you created the SQL Server database, you chose “default” for the collation settings, the database will have been created with a different collation setting, a non-American collation setting.

If you are developing a multi-language capable database, an international database, define your table fields as nvarchar whenever possible. Nvarchar ia a UNICODE character, which means it uses two bytes per character, as opposed to ASCII characters which are one byte in length and thus limits the number of possible ASCII characters

Access: How to Refresh Form Data or Data on a Control

2005-06-16T18:26:00.000-04:00

Simple:

use the .Requery Method

Forms!FormName.ControlName.Requery

or if you want to refresh all controls in the form, then

Forms!FormName.Requery

User Friendly Prompt for Requesting Required Information in Access Form

2005-06-13T19:24:00.000-04:00

I have a form which has a SAVE RECORD button, which runs a Save Record Macro. The underlying form's table has a Required field. When the Required field is not populated and the SAVE RECORD button is clicked, a popup message prompts the user for the Required field. The problem is that the popup message prompts for the field name as defined in the table and in some cases this might be confusing to the user.

In order to make a user friendly popup message, you could add a condition to the Save Record Macro which checks whether the Required field control is populated or not with: is null(Required Field). If it is true, exit the macro, else continue.

How to Run an Access Macro from Excel VBA

2005-06-05T22:21:00.000-04:00

In excel you would need the following Sub procedure:

Sub RunMacroInAccess()

Dim Acc as Object
Set Acc = CreateObject("Access.Application")
Acc.OpenCurrentDatabase("C:\My Documents\AccessDatabase.mdb")
Acc.DoCmd.RunMacro "AccessMacroName"

End Sub

Run an Excel Macro from an Access Database

2005-06-02T19:34:00.000-04:00

If you need to run an Excel Macro from within an Access database, place this vba code on the click event of a command button called cmdExcel_Macro. There is no need to add an Excel Reference Library in Access.

Private Sub cmdExcel_Macro_Click()

Dim excelApp As Object ' Declare Excel Application as Object
Dim workbook As Object ' Declare Excel Workbook as Object
Set excelApp = CreateObject("Excel.Application")
Set workbook = excelApp.Workbooks.Open("C:\Temp\FileName.xls") ' Full path of Excel book that has Macro
excelApp.Run "ExcelMacroName" ' Name of the Excel Macro to run
workbook.Close False
excelApp.Quit
Set excelApp = Nothing

End Sub

Loop Through Charts in Excel Worksheet

2005-05-23T19:54:00.000-04:00

Here’s a quick piece of code that will generate message boxes for every chart in the active worksheet with the name and top position of the chart

Sub LoopThruCharts()

For i = 1 To ActiveSheet.ChartObjects.Count
ActiveSheet.ChartObjects(i).Select
MsgBox ActiveSheet.ChartObjects(i).Name
MsgBox ActiveChart.Parent.Top
Next i

End Sub

Detect and Remove Spyware

2005-05-22T11:01:00.000-04:00

Run the free version of this program called Ad-aware on your computer and I bet you will be surprised by the amount of spyware or tracking cookies that have been installed on your computer without your knowledge. I ran this product after I started noticing that whenever I Googled something, pop-ups would appear on my screen advertising products or services related to my search terms. I thought first of all, Google does not have pop-ups, and second, how the heck is it that these pop-up screens seem to know exactly what I'm searching for.

Anyway, I have used this product on different types of operating systems and it has never created any problems.

You can find the program at this site: www.lavasoftusa.com

Using Shortcut Keys in Windows XP

2005-05-21T14:46:00.000-04:00

When I'm typing, I hate reaching over for my mouse; it "breaks the flow of things".

Solution: Create a shortcut key for frequently used programs or websites.

Basically you right click on the file for which you want to create a shortcut key in order to access the file's properties window.

Set the cursor (mouse) in the shortcut key textbox and press any letter. If for example you press the letter "A", you will see that the textbox value has changed to Ctrl + Alt + A. In order to launch the file, typically an *.exe file, you would then press 3 keys at the same time: Ctrl + Alt + A.

The same can be done to launch a website; in this case you would first add the website to your favorites, then right click on the favorites entry and follow the process as described above.

Instant Messaging Aggregator

2005-05-20T19:27:00.000-04:00

If you use multiple instant-messaging services, such as AIM, ICQ, MSN, and Yahoo! you might want to aggregate them into one tool. My favorite is called:Trillian. You can download it for free at www.ceruleanstudios.com

Hide System Tables on SQL Server

2005-05-19T21:03:00.000-04:00

If you see tables on your SQL Server and have no idea where they came from, you are probably looking at the system tables, which are used by SQL Server. To hide them, you need to right click on the right click on the server (using Enterprise Manager), then Edit SQL Server Registration Properties and Uncheck Show System Databases and System Objects.

No need to update links on Excel Graphs

2005-05-16T20:53:00.000-04:00

If you have an Excel Workbook with Graphs and you email it to someone, as soon as the other person opens the workbook, by default, Excel will ask if it should update the links. If the other person does not have access to the data you used to create the graph, meaning if the other person is not mapped to your computer or to the same network shared folder where the data resides, then the other person must decline to update the links.

If you copy and paste your Excel Graph into a new workbook, the links will carry over. However, if you press the SHIFT and CTRL key at the same time you click on Edit on the top menu bar, you will see a new option which allows copying the graph as a picture--no linked data.

SQL SERVER POLL SERVER

2005-05-14T21:53:00.000-04:00

The other day I got an angry email from our IT Department asking why I was constantly pinging their SQL Servers. Turns out I was unknowingly Polling all SQL Servers registered in the Enterprise Manager. How? Well, I inherited a computer from a co-worker that left the company. Her computer has SQL Server installed and by default, Enterprise Manager Polls any registered SQL Servers every 10 seconds to see what services are available and inquire about the state of the server. The funny thing is that I remember turning this feature off on her computer a long time ago. But now that I'm logging on to her computer using my own Windows User Id, the settings have reverted to default. Here's what the screen looks like, you can find it in Enterprise Manager under TOOLS / OPTIONS:

Security Warning: Unsafe expressions are not blocked.

2005-05-12T20:19:00.000-04:00

Ok this happens every time I move to a new computer and try to open an Access database, I get these annoying pop-ups saying my computer might break if I open the database.

Here’s what they look like:

At this screen, I say NO!

And then I get this screen, asking if I really want to open the file, of course I do, so I do as MS recommends and go to http://www.windowsupdate.com/ to see if I have the latest Microsoft Jet 4.0 Service Pack, and of course I already have it installed, but Microsoft makes me check anyway—whatever.

At this screen, I click YES, because… YES I really want to open the file!
And then I get this screen:
Which basically says that the file I’m trying to open might harm my computer!

I click OPEN of course because I created the database in the first place and I know it isn’t going to harm my computer.

And finally, after 3 annoying pop-ups, the database opens.

To stop getting these pop-ups, you can do the following:

Go to Tools / Macro / Security and set the level to LOW:

BUT YOU BETTER HAVE SOME GOOD UP-TO-DATE ANTIVIRUS SOFTWARE INSTALLED AND CONFIGURED TO CHECK MACROS. OTHERWISE, YOU KNOW WHAT? YOUR COMPUTER MIGHT JUST BREAK!

Get the column letter of the active cell in Excel

2005-05-01T21:42:00.000-04:00

Sub GetColumnLetter()
Dim strCol As String

strCol = Mid(ActiveCell.Address, 2, (InStr(2, ActiveCell.Address, "$")) - 2)
MsgBox strCol

End Sub

DAO Recordset Clone Find First String

2005-04-19T20:23:00.000-04:00

If you need to find the first record in a recordset that matches a string value, you will need to use double quotes.
The procedure below is triggered by a command button called cmdFind_PONumber.
The name of the form is irrelevant, that is why we use " Me. " to designate the currently active form.
The form has a text box called txtFind where the search criteria is entered.

Example:

Private Sub cmdFind_PONumber_Click()

Dim strPONum As String
Dim db As DAO.Database
Dim rstClone As DAO.Recordset

Set db = CurrentDb
Set rstClone = Me.RecordsetClone
strPONum = Me.txtFind

' PONumber is the name of the field in the underlying table
' strPONum comes from the active form (Me.) and a text box called txtFind

rstClone.FindFirst "PONumber = """ & strPONum & """"
If rstClone.NoMatch Then
MsgBox "PO Number NOT Found!"
Else
MsgBox "PO Number Found!"
End If

End Sub

Add Conditions to your Access Macros

2005-04-18T20:16:00.000-04:00

This simple one step macro will show a message box only when the value in a specified text box is null or empty.

Open a new macro or use an existing one.
Go to View / Conditions
A new column called "Condition" should have appeared on the left side of your screen.

For simplicity's sake, for the Action part of the macro, I have a MsgBox Action.

For the condition part, I am going to say:
[Forms]![Put your Form Name here].[Put your Text Box name here] Is Null

Link Access to SQL Server

2005-04-16T10:39:00.000-04:00

Instead of copying and pasting data from SQL Server into your Access tables; or instead of exporting tables from SQL Server to an Access database; or instead of importing data from SQL Server into Access; instead, why not link a table in Access to SQL Server?

Here’s how:
Step 01 – Create an ODBC connection for the SQL Server.
In Windows 2000, go to your control panel, in Administrative Tools go to Data Sources (ODBC).
Once the ODBC Data Source Administrator window opens, click on the System Tab.
Add / Select SQL Server (the very last one on the list)
On the next window, name the connection, describe it, and select the server.
Select next and configure how you will authenticate to the server.
Next, select the database you want to connect to.
Finish.
Test Data Source. If Test Successful, OK. If test not successful, go back and tweak your settings in the ODBC Data Source Administrator window.

Step 02 – Link the Access database to the SQL Server using the ODBC connection you just created.
Open Access, from the top menu, select Insert Tables, a pop up window appears, Select Link Table, on the next window, change the Files of Type to ODBC Databases ().
In the Select Data Source window select the Machine Data Source tab; select the name of the ODBC connection you created in Step 01, and finally select the table or view you want.

Trouble Shooting
If you do not see the table or view you are looking for, make sure that the username defined in SQL Server and which is being used by the ODBC connection has permissions to view the table.

If immediately after linking the table, Access wants to assign a unique identifier, you can select one, or simply ignore it by selecting OK.

Access Programmer

2005-04-15T22:45:00.000-04:00

My new website is up, still a work in progress, check it out … Access Programmer

Access VBA DoCmd Object to open Form

2005-04-14T19:03:00.000-04:00

The methods of the DoCmd object in Microsoft Access can be used to run actions from Visual Basic. Examples of said actions are: closing windows, opening forms and setting the value of a control.

For example to open a form call "Customers" in a normal view, as opposed to a design view or a table view, use this code:

DoCmd.OpenForm "Customers", acNormal

To open the same form but show only records for a customer called Acme Inc (the underlying table or query must have a field called [CompanyName]), use this code:

DoCmd.OpenForm "Customers", , ,"[CompanyName] = 'Acme Inc'"

#NUM Error when linking Excel Spreadsheet to Access Table

2005-03-24T20:14:00.000-05:00

This happens because there is a combination of numbers and letters in one of the columns of the Excel file you want to link. This will always create a numeric field overflow error. Access uses the first 8 rows of the spreadsheet to determine the data type of the column in the linked table. Say for example that your first 8 rows in column A of your spreadsheet have the following data:
1
2
3
4
5
6
7
8
9A

If you link this dataset to an Access table, value 9A will produce a #NUM Error.

Here is some vba code that will fix this problem. The code will insert an apostrophe before the value in any cell that you have selected and in order to make the apostrophe invisible, the code will activate the next cell down the column. When the code is done running on the cells you have selected, the upper left hand corner of the cell will turn green. This means that Excel thinks that this cell has a value that is an error. Specifically, the error message says:
"The number in this cell is formatted as text or preceded by an apostrophe." Clicking on ignore error will do just fine. You can apply this “ignore error” fix to all the cells you have currently selected. If the green indicator doesn’t bother you, leave it alone, it’s harmless.

Let me know if the code helped you. Because I found some code in Microsoft’s website that was supposed to fix this problem but it didn’t. Their code deleted the first character of every cell I had selected.

Here’s my solution:
Select the cells that you want to fix and run this macro:

Sub ConvertToAlphanumeric()

Dim cell As Object

For Each cell In Selection
cell.Value = "'" & cell.Value
ActiveCell.Offset(1, 0).Activate
Next

MsgBox "done!"

End Sub

How to Create a Personal Workbook for Easy Access to VBA Macros

2005-03-23T20:46:00.000-05:00

If you have a set of macros / vba code that you would like to have readily available every time you open Excel, put them in the personal workbook. The personal workbook will automatically open but remain hidden every time you start Excel. The macros in this workbook will always be available.

Here's how:
First, record a dummy macro. This step will create the Personal Workbook for you.
>>> Tools / Macro / Record New Macro... / Store Macro in: "Personal Macro Workbook" / OK / and STOP. Your personal workbook has been created.

Now to actually go in and input vba code, press ALT + F11 and expand the PERSONAL.XLS Project. Or unhide the personal workbook first: Window / Unhide / Personal.xls and then edit the dummy macro you just created.

Loop Trough Cells in Excel and Change the Value of an Adjacent Cell

2005-03-22T19:21:00.000-05:00

There is an Excel spreadsheet with two columns. Column A contains customer names and column B contains the number of invoices billed. Whenever the cell in column B is empty, I want to input a Zero. If the cell in column B is not empty, then I do not want to modify the existing value.
The loop will stop when it reaches an empty cell in column A.

Sub LoopThruCells()
[A1].Select
intRow = 0
Do While ActiveCell.Offset(intRow, 0).Value <> ""
If ActiveCell.Offset(intRow, 1).Value = "" Then
ActiveCell.Offset(intRow, 1).Value = 0
Else
ActiveCell.Offset(intRow, 1).Value = ActiveCell.Offset(intRow, 1).Value
End If
intRow = intRow + 1
Loop
End Sub

Converting Null values in Access

2005-03-16T20:35:00.000-05:00

Use the Nz Function in Access to convert a null value to whatever you desire. This function comes in handy when you are doing divisions. You know you can't divide by 0, so wrap your denominator with the Nz function like this:
Syntax: Nz([Field Name], [valueifnull])

Tips for working with Complex Access Databases used for Data Audits

2005-03-14T20:04:00.000-05:00

Most of the development I do these days involves creating Access databases to help audit data, this involves importing data from a human resources system and from PeopleSoft, comparing the data against both systems, and finally exporting the data as EDI files for invoicing. The access databases used for these procedures generally have 30 or more queries. With this amount of queries it is hard to come back 3 months later and remember the purpose each query and the queries’ dependencies. So I have become extra careful in the names I give to the queries. Here are a couple of tips.

1) If the queries run in sequential order, precede the query name with a number such as 01, 02, 03. This will help identify when the query is run in the data audit process. My preference is to name the queries like this:
STEP-01-Import_Excel_File
STEP-02-Make_tbl_Excel_PeopleSoft_Comparison
STEP-03-Export_Results_To_Excel
With this naming convention, I can come back later and sort by the query name to get a quick idea of a query’s dependencies without having to open each query in design view. This has been invaluable in troubleshooting and improving the audit solutions.

2) If you inherit a database that uses a lot of consecutive queries and the names have already been defined and you don’t want to change the names, use the query’s Description field in the Properties Window to give the query an ordinal value. You can then sort by the query’s description field.

3) Always use the Description field in the Properties Window of the query. This can be accessed by right-clicking on the query. If you already use a naming convention similar to tip # 1, then use this field to put comments that tell you something significant or unique about the query. For example if the query connects to an external source such as another Access database, SQL Server or Excel file, here would be the perfect place to put the ODBC definition for future reference.

4) If the query creates a table, then in the description field or even in the query’s name, give the name of the table it creates.
For example: STEP-02-Make_tbl_PeopleSoft_Comparison.

5) Do not rely heavily on queries that query other queries, especially if the underlying query performs complex calculations. For example, say you need to come up with the unbilled percentage of invoices unbilled in PeopleSoft by Customer name. You probably could do this with a single query, but my advice is to first create a summary table that has all the numbers required to perform a percentage calculation by customer name. Then create a second query that performs the percentage calculation based on the summary table. This will make future troubleshooting easier and will help when someone questions your final percentage results.

Sarbanes Oxley Compliance tip for Access Database

2005-03-13T19:15:00.000-05:00

I created an Access database for an audit procedure to compare data from PeopleSoft and our Human Resources Information System (HRIS). Based on this weekly audit process, a table is created; the table lists timecards that are unbilled or missing from PeopleSoft. And every week our Billing Accountants are supposed to review this table and update the status of the timecard. So to comply with Sarbanes Oxley, I created a form that logs the Billing Accountant’s Windows User ID when he or she changes the timecard’s status.

On the change event of the “Status” combo box, there is VBA code that updates two text boxes on the form. The first text box is called txtChangedBy and the second text box is called txtChangedDate. These two text boxes are linked to an underlying table and map to appropriate table fields. Whenever the Billing Accountant changes the timecard’s status, his Windows User ID and his computer’s system time are logged. And there you have it, a Sarbanes-Oxley compliant Access database.

Private Sub Billing_Status_Change()

Me!txtChangedBy = Environ("USERNAME")
Me!txtChangedDate = Now()

End Sub

**Please note that for the Environ function to work, Excel’s macro security level must be set to low. This means you better have some good anti-virus protection.

Loop through worksheets in Excel

2005-03-10T12:31:00.000-05:00

Sub LoopThruSheets()
Dim wsSheet As Worksheet
Dim strName As String
On Error Resume Next
For Each wsSheet In Worksheets

' ***START CODE FOR WHATEVER OPERATION YOU NEED. ***
strName = wsSheet.name
MsgBox name
'*** END CODE FOR WHATEVER OPERATION YOU NEED. ***

Next wsSheet
On Error GoTo 0
End Sub

Testing your Access Program

2005-03-09T17:59:00.000-05:00

A good way to test the VBA code behind your forms is to input values of the following three categories:

1) Expected Values -- these are the values in the range you ask for. If you ask for numbers between 1 and 10, the expected values would be 2 through 9.

2) Boundary Condition Values -- these are the values that lie in the boundaries of your range. The boundary condition values would be 1 and 10.

3) Out-of-Bounds values -- these are the values that fall anywhere outside the range of 1 through 10. For example, 0, "true", "false", (empty), !, @, &, *, etc.

How to determine who is logged on to an Access database

2005-03-02T20:43:00.000-05:00

Found some more useful code for finding out who is currently using an Access database. The code worked perfectly on Access 2003.

http://support.microsoft.com/default.aspx?scid=kb;en-us;285822

Access 2003 How to Automatically log out idle users

2005-03-01T20:39:00.000-05:00

Found very useful code for automatically logging out idle users in Access. It worked like a charm on Access 2003.

http://www.tek-tips.com/faqs.cfm?fid=1432

Know the Current Working Directory in Access

2005-02-23T19:58:00.000-05:00

In an unbound text box on an Access form, type:
=ENVIRON(“PATH”)

Note: the ENVIRON function in Access 2003 is blocked by default. To unblock it, set the Security Level for Macros to Low.

Domain Functions in Access Forms

2005-02-22T13:19:00.000-05:00

Some useful functions for getting quick information from tables and queries are the Domain Functions.

Say you wanted to know the earliest value in field [ORDER DATE] in table [ORDERS].
In an unbound text box in a user form, type the following:
=DMIN(“[ORDER DATE]” , “[ORDERS]”)

For the latest date, type:
=DMAX(“[ORDER DATE]” , “[ORDERS]”)

For a quick count of total records in table ORDERS, type:
=DCOUNT(“[Any Field Name in table ORDERS]” , “[ORDERS]”)

For a sum of all rows in a column, type:
=DSUM(“[Any numerical field in table ORDERS]” , “[ORDERS]”)

For the average of all rows in a column, type:
=DAVG(“[Any numerical field in table ORDERS]” , “[ORDERS]”)

Link Access to Excel

2005-02-19T10:35:00.000-05:00

A most useful feature is the ability to Link between Excel and Access.

In order to link an Access Query to an Excel spreadsheet, an ODBC connection must first be defined:
Start / Settings / Administrative Tools / Data Sources (ODBC)
System DSN Tab /Add / Choose Microsoft Access Driver (*.mdb)
Name and Describe your data source
Click Select, choose your Access Database, Click OK until the ODBC window closes.
Your ODBC connection is defined.

Now from within Excel:
From the top menu bar, choose: Data / Get External Data / New Database Query
In the Choose Data Source window, select the ODBC recently created and click OK.
Now all the tables and Queries available in the database will appear.
Make your selection and drill down to the fields you want to bring back.
Navigate the next screens by choosing any filters or sorting options.

Next time you want to refresh the data from within Excel, right click on any of the data fields and choose Refresh Data. Alternatively, you can configure the data to be refreshed automatically upon opening Excel. These configurations are found by right clicking in any of the linked fields in Excel and choosing Data Range Properties.

A word to the wise:
If linking many queries in many spreadsheets, name the queries in Access so the will appear on top when sorting by query names. And name your spreadsheets identically to the queries they link. Since the Choose Data Source window in Excel is so small, this naming convention will make maintenance easier.

Known problems:
These linked Queries sometimes start acting up and don't refresh appropriately.
Solution: Delete the rows or Sheet and link the query again--no need for creating new ODBC.

Numeric Field Overflow when importing or linking Excel to Access

2005-02-18T22:25:00.000-05:00

When linking or importing from Excel into Access, the first few rows of a column are used to determine the Access column data type. Often times, this assumption causes a Numeric Overflow Error.

Example, the first 10 rows of the field "Project ID" contain only numbers. The remaining rows contain a mix of numbers and letters. Access will assume that the field is numeric and will error out as soon as it tries to import the first set of alphanumeric records.

One solution, in Excel, precede the "Project ID" field with an apostrophe: '
For an automated Excel VBA solution, see this post: Numeric Field Overflow error when linking Access to Excel

Another solution, sort the Excel file so that the alphanumeric records are in the first rows.

Get the User Name in Access

2005-02-17T22:20:00.000-05:00

To know the Windows user id currently logged in, in a text box on an Access form, type =ENVIRON("USERNAME") .

This will work immediately in Access 2000. But if using Access XP, then by default it will error out because the macro security level is set to Medium. To change this, go to the top menu, choose: Tools / Macro / Security / Low.

Access--Stop Asking Me Questions!

2005-01-29T10:58:00.000-05:00

Say you have a macro in Access that runs several Delete, Make Table and Append Queries. By default, Access will ask for a confirmation before it runs the above query types.

If you don't want to be bothered, use the "SetWarnings" Action in the first row of your macro. Remember to turn it back on at the last row.

Finally, just so you will know for sure that your macro ran to completion, use the "MsgBox" action at the last row.

Find Duplicate Records in Excel

2005-01-29T10:48:00.000-05:00

Got Dups in Excel?

Say you have a list of invoices in Column A, and you need to know how many times an invoice is listed.

In column B, use the Count formula....

=COUNT(A1,A1:A8)
The first parameter says which cell to count, and the second parameter delineates the range.

Find Duplicate Records in Access or SQL Server Table

2005-01-29T10:39:00.000-05:00

Got dups in an Access or SQL Server table?

To find out if you have duplicates of something, for example Invoices....

SELECT
Table1.Invoice, Count(Table1.Invoice) AS CountOfInvoice
FROM Table1
GROUP BY Table1.Invoice
HAVING (((Count(Table1.Invoice))>1));

Speed up your Excel VBA by not looking at it

2005-01-16T13:39:00.000-05:00

I have code that automatically creates excel graphs. After a few months, the amount of graphs that it needed to create grew from 10 to 50 and the execution time significantly slowed down.

I realized that if you minimize the application while it is running, it speeds up dramatically. Alternatively, you could use this vba function: Application.ScreenUpdating = False, which will stop updating the screen as the code is being executed.

Just remember to turn it back on at the end of your code: Application.ScreenUpdating = True

ActiveSheet.Paste stops working when upgrading from Excel 2000 to 2003

2005-01-16T13:09:00.000-05:00

So I have some vba code that copies and pastes non-adjacent cells from one sheet to another and it stoped working when Excel was upgraded from 2000 to 2003. I searched the Internet for a solution and found a few people with the same problem but nobody seemed to have a definite solution.

The code would halt at: "ActiveSheet.Paste".

The solution was to use: PasteSpecial xlPasteValues, SkipBlanks.

What is Six Sigma ?

2004-12-31T09:08:00.000-05:00

Six Sigma is a problem solving methodology that uses your company's human assets, data, measurements, and statistics to identify the vital few factors that can decrease waste and defects while increasing customer satisfaction, profit and shareholder value.

A sigma is a term used in statistics to represent standard deviation, an indicator of the degree of variation in a set of measurements or a process.

A sigma represents 691,462.5 defects per million opportunities (DPMO), which translates to a percentage of nondefective outputs of only 30.854%.

Six Sigma represents 3.4 DPMO, which translates to a percentage of nondefective outputs of 99.9997%--close to perfection.

The Six Sigma methodology uses statistical tools to identify the vital few factors that matter most for improving the quality of process and generating bottom-line results. It consists of four or five phases:

Define the projects, the goals, and the deliverables to customers (internal and external).

Measure the current performance of the process.

Analyze and determine the root cause(s) of the defects.

Improve the process to eliminate defects.

Control the performance of the process.

Probability of Defects at Different Sigma Levels

2004-12-31T09:00:00.000-05:00

What is a Variation

2004-12-31T08:30:00.000-05:00

A variation is any quantifiable difference between a specified measurement or standard and the deviation from such measurement or standard in the output of a process. Variation in outputs can result from many causes in the functioning and management of processes. An important goal of process improvement is to reduce variation outputs.

What are the Vital Few Factors

2004-12-31T08:00:00.000-05:00

The "Vital Few Factors" are factors that directly explain the cause-and-effect relationship of the process output being measured in relation to the inputs that drive the process. Typically, data shows that there are six or fewer factors for any process that most affect the quality of outputs in any process, even if there are hundreds of steps in which a defect could occur--the "vital few". When you isolate these factors, you know what basic adjustments you need to make to most effectively and reliably improve the outputs of the process.

What is Process Capability

2004-12-31T07:30:00.000-05:00

Process capability is a statistical measure of inherent variation for a given event in a stable process. It's usually defined as the process width (normal variation) divided by six sigma and quantified using capability index (Cp). More generally, it's the ability of the process to achieve certain results, based on performance testing. Process capability answers the question, What can your process deliver?

What is a Six Sigma Champion

2004-12-31T07:00:00.000-05:00

A six sigma champion is a senior-level manager who promotes the Six Sigma methodology throughout the company and especially in specific functional groups. The champion understands the discipline and tools of Six Sigma, selects projects, establishes measurable objectives, serves as coach and mentor, removes barriers, and dedicates resources in support of black belts. A champion "owns" the process--monitoring projects and measuring the savings realized.

What is a Six Sigma Black Belt

2004-12-31T06:30:00.000-05:00

A six sigma black belt is a full-time change agent trained in the methodology to solve product and process defects project-by-project with financially beneficial results. A Black Belt does Six Sigma analyses and works with others (often teams) to put improvements in place.

What is Critical to Quality CTQ

2004-12-31T06:00:00.000-05:00

Critical-to-quality (CTQ) are elements of a process that significantly affect the output of that process. Identifying these elements is vital to figuring out how to make improvements that can dramatically reduce costs and enhance quality.

What is a Cycle Time

2004-12-31T05:30:00.000-05:00

"Cycle Time" is the time it takes to complete a process from beginning to end, consisting of work time and wait time. It is the case that, for many processes, wait time is longer than work time.

What is Cost of Poor Quality COPQ

2004-12-31T05:00:00.000-05:00

Cost of Poor Quality (COPQ) is the total labor, materials, and overhead costs attributed to imperfections in the processes that deliver products or services that don't meet specifications or expectations. These costs would include inspection, rework, duplicate work, scrapping rejects, replacements and refunds, complaints, loss of customers, and damage to reputation.

These are costs that would disappear if there were no quality problems. An important goal of Six Sigma management is to reduce or even eliminate the COPQ, which for traditionally managed organizations has been estimated at between 20% and 40% of budget.

What is Benchmarking

2004-12-31T04:30:00.000-05:00

Benchmarking is a method for comparing a process, using standard or best practices as a basis, and then identifying ways to improve the process.

Some Statistical Terms used in Six Sigma

2004-12-31T04:00:00.000-05:00

Mean: Average (more specifically called the arithmetic mean), the sum of a series of values divided by the number of values.

Median: Midpoint in a series of values.

Mode: Value that occurs most often in a series of values.

Range: Difference between the highest value and the lowest value in a series, the spread between the maximum and the minimum.

Standard Deviation: Average difference between any value in a series of values and the mean of all the values in that series. This statistic is a measure of the variation in a distribution of values.

What is a Specification Limit

2004-12-31T03:30:00.000-05:00

A specification limit is one of two values (lower and upper) that indicate the boundaries of acceptable or tolerated values for a process.

What is a Control Limit

2004-12-31T03:00:00.001-05:00

A control limit is one of two values (lower and upper) that indicate the inherent limits of a process.

What is a Process Width

2004-12-31T03:00:00.000-05:00

A process width is the spread of values +/-3 sigma from the mean--process width, also know as normal variation.

What is Process Mapping

2004-12-31T02:30:00.000-05:00

Process mapping means creating flowcharts of the steps in a process--operations, decision points, delays, movements, handoffs, rework loops, and controls or inspections. A process map is an illustrated description of how a process works.

Dollar Types

2004-12-30T11:59:00.000-05:00

Hard dollars: Savings that are tangible--exact, quantifiable cost savings, such as reduced hours, reduced inventory levels, etc.

Soft dollars: Savings that are intangible--expenses that you avoid, such as not increasing hours, inventory, or physical workspace.

What is a Baseline

2004-12-30T00:59:00.000-05:00

A baseline is a standard for comparisons, a reference for measuring progress in improving a process, usually to differentiate between a current state and a future state.

What is Gap Analysis

2004-12-30T00:30:00.000-05:00

Gap analysis is a technique used to compare a current state and a target future state.

What is an Implementation Partner

2004-12-30T00:00:00.000-05:00

An implementation partner is an outside expert engaged in introducing, training, and supporting your six sigma initiative.

Six Sigma SIPOC Meeting #1

2004-12-20T20:01:00.000-05:00

OK let me deviate from the regular technical tips and instead talk about our new Six Sigma Project, the goal of which is to reduce the number of billing errors and thus minimize potential write-offs. Today marked our first team meeting. Our objective was to map out the SIPOC (Supplier Input Process Output Customer). Basically this means that we mapped out our current billing process as is. Then we flagged Processes that have a validation procedure associated to them. Then for each of the Processes we flagged, we went back and identified the respective Supplier, the Input, and the Customer. As an example, let’s consider the process of “creating an EDI billing file”. The supplier for this process turns out to be PeopleSoft, the Input turned out to be a Query in PeopleSoft and the Customer turned out to be our Systems Group. The supplier is where we get the Input for the Process, the Input is the Query that is run in PeopleSoft, which produces the data that is funneled into the process called “creating an EDI billing file” and our Customer, is the Systems Group because we send the file to them and they take care of delivering the file to the “real customer”—the one that is really going to pay the bills.

Six Sigma FMEA Meeting #2

2004-12-19T20:03:00.000-05:00

We had our second Six Sigma meeting today and focused on FMEA (Failure Mode and Effects Analysis). This meeting directly built on our SIPOC diagram, more specifically on the parts of the diagram where we already have a verification process in place. So basically what we did was to quantify the severity and the occurrence of failure in each of our existing verification processes. Each of these failure modes was graded from 1 to 10, 1 being the lowest and 10 the highest. For example, a severity mode of 1 meant that when the problem occurred, it did not highly affect the billing process. To grade the occurrence of the problem we had a chart with categories, such as category 1, a category 1 meant that the problem occurred anywhere from 1 to 20 times within every 20,000 invoices billed, a category of 2 meant that the problem occurred anywhere from 20 to 100 times every 20,000 to 50,000 invoices billed, and so on until the highest category of 10. We also measured a third component, the ability to detect when a problem occurred. The ability to detect when each of our existing verification processes failed was also graded on a scale of 1 to 10. Where a grade of 1 meant that when the problem occurred, it would be immediately known. A grade of 10 meant that if the problem occurred, we would never know about it. Ok that’s all; stay tuned for meeting #3.

Six Sigma The 5 Whys Meeting #3

2004-12-18T21:14:00.000-05:00

Today we isolated processes where failures where likely to occur and asked ourselves why the problems occurred—5 times.

There is no magic in the number five, it’s really just about identifying root causes of problems, of getting past the superficially causes.

For example, let’s say that there is a problem with the amount of time a customer has to wait to get through a telephone representative at a company during lunch hours. Let’s ask ourselves WHY?

1) WHY? Because most telephone representatives are out to lunch and the call-center is understaffed at this particular time of day.

2) WHY? The call-center is understaffed at this particular time of day because the Shift Supervisor has not created a flexible schedule.

3) WHY? The Shift Supervisor has not created a flexible schedule because upper management has not asked him to.

4) WHY? Managers have not told to create a flexible schedule to accommodate for lunch-hours because the company has not trained them to ask this of their Shift Supervisors.

The root cause of this problem is that upper management has not received the proper training. Of course you could take it a step further and say that it is not in the company’s culture of doing things.

Six Sigma Inter-relational Diagrams Meeting #4

2004-12-17T20:31:00.000-05:00

Now that we have identified our root causes of failures in our processes, we are going to see if these root causes are related to each other. We did this by using a Visio diagram, where each of the root causes was represented by a box. For simplicity’s sake, let’s say there were 3 root causes: Root Cause #1, #2 and #3.

We asked ourselves is root cause #1 related to #2, if yes, draw an arrow from Root Cause #1 to #2, else do nothing. Then we asked is root cause #1 related to root cause #3, if yes, draw an arrow, else do nothing.

Then we ask the same but from the perspective of root cause #2. We ask is root cause #2 related to #1 and #3, if so, draw appropriate arrows, otherwise do nothing.

The purpose of this exercise was to identify which root cause is related to the others the most.

We were left with a very convoluted Visio diagram. In hindsight, maybe we should have used an Excel Matrix or used Layers in Visio. Hopefully though, I’ll be able to write a macro for Visio that will count the number of outgoing arrow connectors.

Six Sigma Five Whys Meeting #5

2004-12-16T19:18:00.000-05:00

After defining the inter-relational diagrams we knew which root causes related most to each other.

As a side note, it turns out that we would have been better served by plotting the root cause relationships in an Excel matrix instead of using Visio.

We then proceeded to asking ourselves Why the Root Causes occurred. We asked ourselves “Why” five times to try to arrive at the essence of the problem.

Anastasia's Birthday Party Pic #1

2004-11-01T23:59:00.000-05:00

Anastasia's Birthday Party Pic #2

2004-11-01T23:49:00.000-05:00

Anastasia's Birthday Party Pic #3

2004-11-01T23:48:00.000-05:00

Anastasia's Birthday Party Pic #4

2004-11-01T23:47:00.000-05:00

Anastasia's Birthday Party Pic #5

2004-11-01T23:30:00.000-05:00

Anastasia's Birthday Party Pic #6

2004-11-01T23:21:00.000-05:00

Anastasia's Birthday Party Pic #7

2004-11-01T22:04:00.000-05:00

Anastasia's Birthday Party Pic #8

2004-11-01T21:00:00.000-05:00

Anastasia's Birthday Party Pic #9

2004-11-01T20:59:00.000-05:00

Anastasia's Birthday Party Pic #10

2004-11-01T20:50:00.000-05:00

Anastasia's Birthday Party Pic #11

2004-11-01T19:16:00.000-05:00

Anastasia's Birthday Party Pic #12

2004-11-01T19:00:00.000-05:00

Anastasia's Birthday Party Pic #13

2004-11-01T17:00:00.000-05:00

Anastasia's Birthday Party Pic #14

2004-11-01T16:22:00.000-05:00

Anastasia's Birthday Party Pic #15

2004-11-01T16:20:00.000-05:00

Anastasia's Birthday Party Pic #16

2004-11-01T15:00:00.000-05:00

Anastasia's Birthday Party Pic #17

2004-11-01T14:27:00.000-05:00

Anastasia's Birthday Party Pic #18

2004-11-01T14:20:00.000-05:00