Lab Management Program

[Greg Wilson]

I want to either find a simple and inexpensive commercial lab management
program or to develop one myself using Excel. In the latter case, I am
interested in any insight or tip anyone has to offer, particularly as to the
general architecture (i.e. basic strategy). You don't have to be a programmer
to offer insight in this area, just have experience or insight as to overall
strategy. I am an experienced (amateur) VBA programmer. Information follows:

Background:
This has to do with lab tests conducted on geotechnical samples (soils) and
construction materials (concrete and asphalt mainly) and possibly, in the
future, environmental samples (contaminated soils mainly). Testing is
currently done only on a sporatic basis.

Needs:
1) To easily create electronic work orders for the lab tests. The WO forms
need to be specialized. The one-size-fits-all approach is impractical.
2) To have access to the work orders by all computers.
3) To easily change the "Action Status" of test samples - i.e. whether the
status is "Hold", "Go ahead", "Stop Work", "Completed" etc.
4) To track whether samples are still being held or have been disposed.
5) To track whether tests have been billed.
6) To select the appropriate lab worksheet for each of the tests and to
print it with all the correct information already entered (Job number,
project name etc.).
7) To create a list of samples that exceed their "Hold to" date and can be
thrown.
6) To track sample age (received date to current date) in order to bill for
storage for samples that exceed a given grace period.
7) To password protect some fields

For such issues as Action Status, whether samples have been thrown, billed
etc., my current thoughts are to use a technique that uses the
SelectionChange event to toggle a checkmark in cells (the letter "a" in
Marlett) instead of using actual check box controls. Otherwise, the
accumulation of controls would produce a huge file size and maintenance
nightmare.

Management is reluctant to make the change and getting a consensus on
anything is difficult. This needs to be either slick or simple and
inexpensive if a commercial program is the option.

All responses much appreciated.

Greg

 

[Tim Williams]

Seems like a pretty big analysis question.

How many samples/tests (per month or year) ?
How many different types of analysis ?
Do you also need to be able to track test results ?
What would you consider "inexpensive" (1k/10k/100k) ?

The need to be able to access the system from >1 PC and the types of
information being tracked seem to point towards a database back-end (maybe
with an Excel UI).
Access may be enough for the DB if your capacity doesn't need to be too
large and there aren't too many concurrent users.

Tim.

 

[Greg Wilson]

Thanks Tim for responding.

How many samples/tests (per month or year) ?

Very roughly 3 or 4 tests per day. This can vary dramatically however.

How many different types of analysis ?

Very many. The complexities of analysis including reporting are already
handled by other programs. However, these other programs are test specific.
They don't manage the lab. The program described is mainly to serve as a a
sample record including tests to be done with a few other "bells and
whistles", in particular whether tests have been billed as well as tracking
samples so they don't pile up or get disposed of prematurely. Tracking how
long samples have been held and charging a storage fee discourages arbitrary
saving of samples which is currently a serious problem.

Do you also need to be able to track test results ?

No. This is done by existing programs. Test results are mailed and copies go
to files. We might want to expand the program to indicate whether formal
reports are required and if they have been sent.

What would you consider "inexpensive" (1k/10k/100k) ?

Management is very capricious on this issue. Depends if it's managements
idea or a slaves (read me). If it's a slaves idea, 1k max. Else 10k mabe but
not likely. So 1k max is likely it.

The need to be able to access the system from >1 PC and the types of
information being tracked seem to point towards a database back-end (maybe
with an Excel UI).

I was intending to do this exclusively with Excel. I don't have any
programming experience with Access. If we average, say, 4 tests per day, then
this would amount to roughly 900 tests per year. I think we should start a
new record for each year.

Access may be enough for the DB if your capacity doesn't need to be too
large and there aren't too many concurrent users.

As I said, I'm an amateur programmer. I do my studies and experimenting
exclusivley at home where there isn't an intranet. So I'm not sure about the
file sharing problem. I don't expect it to be left open too long and usually
it's the same people creating the WO's and tracking things.

Thanks again Tim for taking the time.

Best regards,
Greg

 

[Tim Williams]

In that case sounds a lot simpler, particularly if simultaneous use is
not a problem.
Just need to break it down into what "things" you are dealing with...

samples
customers
analyses
tests (sample+analysis)

I would try to keep all of your data together - once you start
splitting up your data you'll find it gets much more difficult to
track what's at what stage in the system. You could decide to have
some kind of "archive" cutoff for samples for which all tests are run
and bill sent at least "X" months ago.

Even for this samll a system a database would be useful, but you can
probably make do in Excel.

For reporting and work orders you can just format some worksheets to
serve as templates.

Tim.

 

[Greg Wilson]

Thanks Nick. I'll check out your suggestion. Hopefully I'll get the hang of
it. If I don't get up to speed in time, at least I'll get some needed
experience here.

Regards,
Greg

 

[NickHK]

Greg,
I'd go with Tim in saying Access would be a better data storage than Excel.
How you view the data is a different question.
In Access, check out File>New>Databases tab and select one of those options.
Access will produce the database structure for you, complete with forms,
queries and reports.
Whilst the output will probably not be immediately applicable to you, it
should give you a good enough idea of what will be required.

NickHK

 

[Roy Wagner]

Greg,

I usually tell people (regarding excel) if you can think of it, you can
probably do it. It's usually true. I'm sure you can do it. As the programmer
and the expert in the field, you are probably in the best position to decide
how it should look and function. The group can help with bugs and so forth as
you progress.

As long as you don't need more than 256 facets for any one test/sample, it
sounds like you can set up a table on one sheet to track the bulk of things,
with each row representing one test. It sounds like you'll need a sheet
containing the various things that could appear on a work order, and then
perhaps generate the work order on another sheet using VB based on criteria
in the test/sample sheet.

As far as bells and whistles, I came up with one that works like having a
set of colored LED's to highlight problems. Very simple. A lower case L in
the cell, formatted to wingdings and bumped up to 18 point or so can be set
green by default and conditional formatted to turn red when the samples have
stagnated beyond tolerable limits. You can easily spot the storage and
billing problems at a glance. I stole the idea from a printed advertisement,
and excelized it.

For security, you could simply lock cells and have a button to
secure/unsecure the secure areas. This could even involve whether or not what
was in those cells is visible or not, when secured. Another trick when I want
to hide something, even text, is to build it into a formula, format it white
on white, etc. and then hide the formula. It's there, but can't be seen, even
when selecting. I also have used a rolling code to block sheet access,
whereas the workbook has a =now() hidden away on the sheet and the code is
derived by manipulating things relative to the date so it is not obvious,
changes every day (in my case, but could be more frequent), yet can still be
calculated in my head.

My personal feature project is in the automotive field, a workbook that has
evolved over 4 years with probably hundreds of hours in the VB editor
tweaking and adding things. It actually generates a technical report in word,
based on data from the workbook, among numerous other things.

It sounds like an interesting project.

Roy

 

[Greg Wilson]

Thanks again Tim,

I was thinking of using a custom toolbar for the most part. To create a WO,
you click a button and it brings up a UF. The UF has a MultiPage and each
page corresponds to a specialized WO type. Say, the first page is for
Geotechnical, the next for concrete, another for asphalt and another for
concrete manufactured product etc.

Hopefully the name field can be automatically filled in somehow. I know I
can key off of the user name (Application.UserName) for a C-drive based
program, substituting a prefered name.

The person enters the job and tast numbers (I can probably figure out how to
default to last job number entry by this person), project name, client etc.
Then they enter the sample list in a column of text boxes and select from a
combobox the test type. The test type selection is then automatically entered
adjacent the sample. The "Throw out" date automatically defaults to "After
tests complete". They need to override this to specify a hold period. In such
case, storage charges may be applied to the job number.

After the WO is complete they click the Apply button. The requisite number
of rows (sample number dependant) is then added to the top of the ws and the
new entry is added in the form of a shaded block of data separated by an
empty row. I want to keep new entries on top.

Invisioned is that I'll use the SelectionChange event to simulate check
boxes (toggling the letter "a" in Marlett font). We could thus be able to
simply click cells to toggle checkmarks and thus indicate whether the status
is "Hold", "Proceed", "Stop Work", "Work Complete" etc. Do similar for the
sample status "Available" or "Disposed". Same for whether a test has been
billed etc. Using actual check boxes would be problematic with approx. 900
tests per year.

Before conducting a test you need to have the appropriate lab worksheet. To
print a lab worksheet (e.g. Sieve Analysis ws), you would select the
appropriate type from a dropdown control on the toolbar. The correct WO would
be identified by the position of the active cell and the heading info would
be automatically filled in during the print.

<For reporting and work orders you can just format some worksheets to
<serve as templates.
I was thinking of keeping the templates in a different wb to minimize file
size. The wb would be activated, the data transferred and lab worksheet
priinted. Changes would not be saved.

I also want to be able to create a list of samples to be thrown. A macro
would do this by searching for expired "Hold Till" dates. Similar for
calculating storage charges.

In any event, the above is my concept to date. I usually get it flawed or
ill-conceived in some way. Hoping for constructive criticism.

Regards,
Greg

 

[Greg Wilson]

Thanks Roy for your response. I was not expecting the quality/quantity of
responses I've received tonight.

I like your idea about the lowercase L in Wingdings combined with
conditional formatting. I had already mentioned simulating check boxes using
"a" formatted as Marlett. I think I can use a mix of both techniques - i.e.
checkboxes and option buttons as well as a large dot to indicate a billed
test for example.

I was also thinking of using conditional formatting to key off of a
checkmark, i.e. if a "Stop Work" status has been set for the sample, to
format the row red. But it hadn't occurred to me that it can highlight tests
where the samples have not been thrown and whose "Hold Till" dates have
expired. I was thinking of finding these with a macro. I forgot that
conditional formatting could highlight them automatically. The macro may
prove unnecessary.

Great response Roy. All the best.

Greg

 

[Vic Eldridge]

Hi Greg,

Just in case you didn't already know, this breed of software actually comes
with it's own acronym - LIMS (Laboratory Information Management System). Try
searching the web for LIMS and you'll find lots of ready built solutions.
Typically very expensive though.


Regards,
Vic Eldridge

 

[Greg Wilson]

Thanks Vic. I'll check it out. Found out we already have a system in one of
our other offices. Whether we are licenced to use it is to be checked out.

Regards,
Greg