Track simple lab inventory

[Guest]

I am building a simple database to keep track of reagents made (many made-in
house), the kits they are assembled into and to whom the kits are sent.
Reagents will be given a batch code and there may be as many as one or as
many as 100 to a batch. So no reordering of stock; each new batch is given a
unique batch code, i.e. a new entry in a production table.

In addition to the tables 'Kits Sent' and 'Kit Contents' which are in a
one-to-many relationship linked by a Kit Barcode, each reagent or group of
reagents will need their own table to store information unique to that type
of reagent and with specific information about the batch.

The locations and inventory of the reagents should then be stored in a
separate table (called Inventory) or stored in the aforementioned tables? I
ask this with the following in mind:
When the technician who assembles the kits is filling out a kits form (based
on tables Kits Sent and Kit Contents), and adds a reagent to the kit I would
like Access to automatically subtract this quantity from the quantity on hand.

Ideally, the solution to my problem would leave me with a field in each of
the "reagent production tables" or the "single inventory table" showing the
quantity of a batch left on hand. This way the technician could filter
records from the table(s) with "Not 0" to find out what reagents are made and
how many there are at any given time.

What is the best way to do this?

Thanks,
help is GREATLY appreciated.

 

[Guest]

The locations and inventory of the reagents should then be stored in a

separate table (called Inventory) or stored in the aforementioned tables? I
ask this with the following in mind:
When the technician who assembles the kits is filling out a kits form (based
on tables Kits Sent and Kit Contents), and adds a reagent to the kit I would
like Access to automatically subtract this quantity from the quantity on hand.

Ideally, the solution to my problem would leave me with a field in each of
the "reagent production tables" or the "single inventory table" showing the
quantity of a batch left on hand. This way the technician could filter
records from the table(s) with "Not 0" to find out what reagents are made and
how many there are at any given time.

What is the best way to do this?

I'm basing my suggestions below on a template which Microsoft makes
available, Inventory Management Database, which you might want to look at.
It can be downloaded for free from here:

http://office.microsoft.com/en-us/templates/TC010184581033.aspx?CategoryID=CT011366681033

You create batches of reagents, which have a unique batch code. You want to
keep track of how many are created, how many have been dispensed to kits, and
how many have been destroyed (accidents happen, and sometimes things break).
The simplest way to do this is keep a running record of the transactions that
occur with your batches of reagents.

tbl_Transactions
TransactionID (PK)
ReagentBatchCode (FK) (I will assume that you have a table that keeps track
of your reagent batch codes as a primary key)
TransactionDate
TransactionDescription (just a text field to describe what the transaction
is doing)
UnitsCreated (integer field)
UnitsDispensed (integer field)
UnitsDestroyed (integer field)

When you make a new reagent, and have assigned a batch code to it, you can
enter it into this table with a number in UnitsCreated field. When you
create some kits, you enter that by using the UnitsDispensed field. If you
somehow destroy some reagents, you enter that amount in the UnitsDestroyed
field.

To view what you have, create form/subform that adds in UnitsCreated, and
subtracts UnitsDispensed and UnitsDestroyed. You will want to filter out any
reagents where that number has become zero, because that means it is all gone
(of course).

 

[Guest]

Hmmmm. What you say intriques me. Are you essentially saying to create a
calculated bound control on a form to do all the adding and subtracting? So
the techs would have to open this form everytime they create, dispense or
destroy any reagents? If my assumptions are correct, this would not be so
efficient.

Or do you mean to have these calculations occur in a form or query when the
tech wants to know how much of any reagent is available on hand? This would
be better.

Using a Transactions table like you suggested, I envision the "production
tech", in the production/spec. form, filling in a 'Units Made' object in a
subform (tbl_Transactions.UnitsMade). The "assembly" tech, in the Kits Made
form, would then have a 'UnitsDispensed' object in a subform
(tbl_Transactions.UnitsDispensed). The field UnitsDestroyed would be
defaulted to 0 in the Field Properties and so thru a query or form, units
remaining could be tallied.

Do please elaborate. And since I do not have administrator rights I have
yet to download the template. I will look at it on a laptop I do have admin
rights to. But I have not had much luck with such templates b/c they are
usually too much geared towards business situations.

Thanks for helping.

 

[Guest]

The template includes a form/subforms for products. The main form has two
numbers that are brought in from a subform: One for Units On Hand, and the
other for Units On Order. The formulas for this transfer is

=[ProductsSubform].Form!UnitsOnHand
and
=[ProductsSubform].Form!UnitsOnOrder

These come from the ProductsSubform, which has these formulas

UnitsOnOrder
=sum(nz([UnitsOrdered])-nz([UnitsReceived]))
and
UnitsOnHand
=sum(nz([UnitsReceived])-nz([UnitsShrinkage])-nz([UnitsSold]))

As you update the subform, the amounts in the main form change. The main
form is based on a particular product, which would be your reagent batch.

One of the problems with the template I linked to, is that it doesn't
include sending the product to a customer, which would have been equivalent
to creating a kit. It is good for illustrating how you can keep a running
total of inventory. However, as it does track units sold, you could possibly
just keep track of kit numbers as part of the ledger for each reagent batch,
selling the reagent to a particular kit. Not an excellent solution, but it
would be fairly quick. Would need to change the labels on the forms to match
your nomenclature.

 

[Guest]

Our last "conversation" got me thinking and searching some more. I came
across some advice from Albert Kallal and downloaded an example inventory
database that uses a query to track inventory.

http://www.members.shaw.ca/AlbertKallal/msaccess/msaccess.html

I figure something like this would be best and simplest. I work with a lot
of technophobes so a query that updates itself each time they open it would
be best. This way they see all the inventory at a glance and can see what we
are low on. The field, UnitsMade, would be in the production table,
UnitsSent would be in the Kit Contents table and an additional Lost&Tossed
field would be in an Inventory/Storage table (Pkey Batchcode or description)
and these would be filled out thru various subforms.

The only thing I can't figure out is how to filter the results so that the
batchcodes that have been reduced to zero don't show up in the results. At
the worst, users would have to filter the results in the datasheet view.
They will know or do know how to do this at least.

What do you think? Am I overlooking some pitfalls?

Thanks for letting me pick your brain,
Access Greenhorn