A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Track simple lab inventory



 
 
Thread Tools Display Modes
  #1  
Old May 24th, 2006, 07:11 PM posted to microsoft.public.access.tablesdbdesign
external usenet poster
 
Posts: n/a
Default Track simple lab inventory

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.
  #2  
Old May 24th, 2006, 10:26 PM posted to microsoft.public.access.tablesdbdesign
external usenet poster
 
Posts: n/a
Default Track simple lab inventory

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 he

http://office.microsoft.com/en-us/te...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).

  #3  
Old May 25th, 2006, 06:23 PM posted to microsoft.public.access.tablesdbdesign
external usenet poster
 
Posts: n/a
Default Track simple lab inventory

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.
  #4  
Old May 25th, 2006, 09:04 PM posted to microsoft.public.access.tablesdbdesign
external usenet poster
 
Posts: n/a
Default Track simple lab inventory

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.

  #5  
Old May 26th, 2006, 04:39 PM posted to microsoft.public.access.tablesdbdesign
external usenet poster
 
Posts: n/a
Default Track simple lab inventory

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/AlbertKal.../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

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Ghost Track Changes JStillings General Discussion 4 May 12th, 2006 08:09 PM
help on query for a report Bart Running & Setting Up Queries 6 January 23rd, 2006 12:59 PM
Inventory sheet to track, order & reduce quantity from master. drc536 New Users 2 October 23rd, 2005 05:36 PM
Make inventory sheet to track, order & reduce quantity from master drc536 Worksheet Functions 1 October 23rd, 2005 05:35 PM
Simple inventory System Alexthecamel New Users 3 September 1st, 2004 01:49 PM


All times are GMT +1. The time now is 06:43 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.