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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|
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 |