Inventory Transactions Form Design

[Salty Mariner]

I have a basic question here. I am working on a simple inventory application for use on a ship I work on. I have a form setup to display and edit all of the Inventory items. I am now down to how I want to deal with inventory transactions.

I have created aggregate queries for stock-in and stock-out, as well as a third query to calculate the stock on-hand. These are functioning as expected.

I am trying to figure out the best way to handle this on a form. The way I see it I have two paths I can go down.

One is through the use of two subforms One for stock-in, and another for stock-out.

The second option is unbound textboxes and handle this all through VBA recordset manipulation.



I also need to deal with Inventory adjustments. I figure that these would basically just be a stock-in or stock-out with fields in a transactions table to note whether an adjustment occurred and whether it was applied as an overage, or an underage.

I'm still in the early stages here. I'm that some of the veterans out here can speak to this from experience.

Any advice? It's my first time trying to do inventories "the right way".

[Micron]

Where I worked, the CMMS did everything. On the stores side, you could look at all transactions or base a search on trans type. Either way it was just one form on the screen. InvTransaction had several possible values but if you went that way you'd probably only need Inv+ and Inv-. Or you could add something for inventory take adjustments (like Adj+ and Adj-). All numeric values were +ve integers in the same field,IIRC. Or you could use another common approach and use negative numbers for stock out and losses and +ve numbers for stock received and gains, but again, in one field.

Maybe this will help
http://allenbrowne.com/AppInventory.html

[Salty Mariner]

Hi Micron,

Thanks for the reply here is my table and relationship structure.

As I stated I have 3 aggregate queries which work wonderfully and do what I want them to do. My main form is pictured below.

I originally started out thinking I was going to use a single form for everything but ran into issues using liked combo boxes on the continuous form. So, I abandoned that Idea and just decided to go with a continuous form for viewing and record selection and handle item edits in the footer. Also, too many tables and I get recordset update problems. So my thought was to just break transactions off into small pop-up transactions form and handle them there. That is where I am at present.

Allen Browne took a different route than I did. I know there is more way to skin a cat which is why I thought I'd bounce some ideas around here My thought was that Item information would be separated from the transactions. The items once entered are not going to change much but you will have many transactions and I can just preform simple aggregate math functions in queries. At least that is my logic.

[ArviLaanemets]

Is your database dedicated for a single vessel only? Because otherwise your DB structure don't work, as there is no link between items and vessels! But when this is for single vessel only, then why is there tblVessel at all - you can enter the vessel info simply as labels into any form or report?

In case the DB is used for multiple vessels:
1. Add a ID field to vessels table (vessel name is probably quite long text string, which makes it not very good primary key);
2. As your tblTransactions is obviously meant for general storage, you need another transactions table for all vessels. Like
tblVesselTransactions: VesselTransactID, VesselID, ItemID, VTType, Qty;
3. And you need a table where transaction types are defined, along with transaction direction (1 for in, -1 for out). There is no need for separate tables for incoming and outcoming transactions (and the same applies for general store too).

[Salty Mariner]

Is your database dedicated for a single vessel only? Because otherwise your DB structure don't work, as there is no link between items and vessels! But when this is for single vessel only, then why is there tblVessel at all - you can enter the vessel info simply as labels into any form or report?

In case the DB is used for multiple vessels:
1. Add a ID field to vessels table (vessel name is probably quite long text string, which makes it not very good primary key);
2. As your tblTransactions is obviously meant for general storage, you need another transactions table for all vessels. Like
tblVesselTransactions: VesselTransactID, VesselID, ItemID, VTType, Qty;
3. And you need a table where transaction types are defined, along with transaction direction (1 for in, -1 for out). There is no need for separate tables for incoming and outcoming transactions (and the same applies for general store too).

It will be used as a standalone DB on a single vessel, not part of a fleetwide system. My intention in tblVessel was just to have some information that I could pull up if needed, say for instance the vessel name for a report. I can just use a simple DLookup if needed and I didn't want to hard code this formation incase I share with other vessels. It's use will be very limited.

Anyway, I have already started on he transactions form. I decided to try 2 small subforms in add-mode for stock-in and stock-out. They are identical and sit on top of each other, I hide/show them based on selections in an option group. I will create some unbound textboxes for performing the inventory adjustments which will be disabled unless the user has selected inventory adjustment as an option. This adjustment value will be applied as either a stock-in or a stock-out and annotated when closing the form.

on you last remark what I did was create 2 simple aggregate query's one that creates aggregate totals for stock-in, and one for stock-out. I use a 3rd query to bring in the previous 2 aggregate queries and subtract stock-out from stock-in. It works great and does not require a single line of code. This saves my users from having to think about positive vs negative numbers.

My intention is to keep this as simple as possible with probably only around 2 forms so that (hopefully) it will require little maintenance once deployed.

Thank you for your input.

[Micron]

If simple is the goal, then one table for transactions (stock in-out) would be simpler. That would allow for one form. With 2 tables, you need 2 forms, 2 queries. Another approach could be to dbl click on item line and open a new form to its transaction history.

[ArviLaanemets]

on you last remark what I did was create 2 simple aggregate query's one that creates aggregate totals for stock-in, and one for stock-out. I use a 3rd query to bring in the previous 2 aggregate queries and subtract stock-out from stock-in. It works great and does not require a single line of code. This saves my users from having to think about positive vs negative numbers.


My intention is to keep this as simple as possible with probably only around 2 forms so that (hopefully) it will require little maintenance once deployed.

My advice about vessel table is, designate it properly anyway. Keep the table with info about all vessels in in separate DB, and copy the info from there into specific vessel DB, or copy all vessels info into every vessels databases, and have in every vessel's DB a SetUp table where the vessel is determined, and this ID of this vessel is added into other tables of this DB where needed. When you do this, and don't use possibilities it gives, nothing bad happens. But when you in future have a need to consolidate databases of different vessels, having every vessel a certain ID will help enormously.

Now about transactions table/form:
Let's assume you have transations table (I added VesselID too - when you don't use it, you can simply skip it):
tblTransactions: TransactionID, VesselID, ItemID, TType, TDate, TQty
(Quantityes in all entries are entered as positive numbers!)
tblTransactionTypes: TType, TDirection, TypeText (tDirection is 1 for all incoming transaction types, and -1 for all outgoing transaction types. NB! With his schema, you must have separate types for incoming entries, and for outgoing entries! Or users have to cope with positive and negative values!)

To calculate e.g. incoming quantities for certain time period, all you need is a single query - like:

SELECT [tran.VesselID,] tran.ItemID,  it.ItemName, it.PartNo, SUM(ttyp.TDirection*tran.TQty) AS IncomingQty
FROM (tblTransactions tran INNER JOIN tblTransactionTypes ttyp ON tran.TType = ttyp.TType) INNER JOIN tblItems it ON tran.ItemID = it.ItemID
WHERE ttyp.TDirection = 1 AND tran.TDate BETWEEN StartDate AND EndDate
GROUP BY [tran.VesselID,] tran.ItemID, it.ItemName, it.PartNo

NB! A single query!
For outgoing quantities for certain time period, all is same except you use -1 in WHERE clause.

To calculate saldo of all items at certain date:

SELECT [tran.VesselID,] tran.ItemID,  it.ItemName, it.PartNo, SUM(ttyp.TDirection*tran.TQty) AS ItemSaldo
FROM (tblTransactions tran INNER JOIN tblTransactionTypes ttyp ON tran.TType = ttyp.TType) INNER JOIN tblItems it ON tran.ItemID = it.ItemID
WHERE tran.TDate <= SaldoDate
GROUP BY [tran.VesselID,] tran.ItemID, it.ItemName, it.PartNo

Again a single query! And of-course all this is managed in single form!

[Salty Mariner]

Interesting take Avril. I'll make a copy of my DB and play around with some of your ideas. That was a tact I had not considered. I guess here are many ways to skin a cat.

Thank you for your thoughtful response.

[dblife]

From what I can see from your table layout you are a good way there.
A couple of notes.
Probably best not to have an In and Out table each.
Have it as Transactions and have a field for In or Out or even better just have the value going into the field as + or - . Add or subtract the latest transaction value.
Say you have 10 fuses when the db is set up.
Each time you add (restock) the fuses, a new record in that field might be '10'. If someone books a fuse out, a new record for that item would be -1.
When you query for the current balance, you calculate on a running total basis. Databases do this really well, even over thousands of transactions. If you filter one product and calculate it will always work it out for you.
This approach also allows you to keep track of the value of the part the user took by recording that at the same time (when the product's purchase costs changes over time)

The other point I would make is suppliers are usually a many to many relationship, you have it as a one to many.
A junction table to handle the m2m relationship will allow you to normalise the the handling of one supplier supplying more than one item or changing the supplier to someone else to buy a product from.
ie, you buy a fuse from supplier A for 12 months and the purchasing team want to start using supplier B.
You don't change your supplier in the parts table, you ADD the supplier in the junction table so you can keep track of who supplies you and what they charged for each item.
Its a subtle thing but it helps with normalisation (an important database design theory to read up on if you haven't already)

[June7]

Since you aren't tracking Sales and Purchases as part of inventory management, I agree one Stock In/Out transactions table would be simpler. Looks to me like 3 tables could become one.

With one form for transactions, user would have to enter transaction type or know to enter Qty out as negative. Or could still have two forms - each would automatically enter the appropriate transaction type when record is initiated (set DefaultValue for a textbox). User selects "In" or "Out" from a menu form and code opens data entry form - either one of two forms or one form for both and sets DefaultValue property. All depends on your work process.