Access--Stop Asking Me Questions!

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

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

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

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

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 ?

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

What is a Variation

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

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

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

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

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

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

"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

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

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

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

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

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

What is a Process Width

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