FAO Pat Hartman & anyone else who can help! (Stock Problem) (1 Viewer)

[redblaze]

i dont know if u recall, but you helped me with sorting the size of products problem out. but now another problem has arisen... keeping track of stock.

i have the 2 tables --

tblproduct
productID
sizegroupID
ect...

tblSize
SizegroupID
sizetype
sizegroup

how can i keep track of the stock of each size individually? if i put stock in the size table it will not work. i cant put Stock in the product table as it would not be the stock for each size of that particular product...i hope this is making sence to people! how can i make it work so that each size of each product has its owen number in stock????

thanks in advance.

 

[The_Doc_Man]

I recall that discussion. You were reluctant to separate each item by size, instead preferring to keep product descriptions with an encoded field that showed product size availability.

This exact problem is why we suggested splitting your product descriptions apart by sizes as well as descriptions.

The only way to keep individual track of stock by sizes is if the stock quantity is unique by sizes.

I.e. if you have Tweety-Bird t-shirts in S, M, L, and XL, then you need four entries in your stock table.

In your case, I think the following concept might help you reach where you want to go.

Take that table that had descriptive data in it. You might need to rename it because it is no longer really your stock. Maybe you could call it your ProductCatalog table or something. (Something shorter if you type as well as I do.)

Make a stock table that has

tblStock
-- StockID (autonumber might be good enough)
-- ProductID (foreign key linked to PK of ProductCatalog)
-- Size code Info
-- Quantity

NOTE: There is a slight "abomination" here, since quantity is technically a computed field formed as the sum of stock shipments received - stock items sold. However, I've never been one to really complain about things like this. It makes reports run faster and it also makes stock status queries easier to run. But be aware that as a long-term goal, you really want to do away with the quantity field when you reach that level of comfort with your DB.

Now, your transactions are:

1. Stock Add - (you got a shipment in or accepted a return/exchange)
-- StockID (link to ProductCatalog to see size, etc.)
-- Quantity
-- Action code (= shipment, return/exchange)
-- TransDate
-- QChange (= Quantity)

2. Stock Remove - (sale or shrinkage)
-- StockID (link to ProductCatalog to see size, etc.)
-- Quantity
-- Action code (= sale, shrinkage)
-- TransDate
-- QChange ( = -1 * Quantity)

Look similar? They could be in the same table. (That's not an accident, either.)

Your stock on any given date is the sum of all transaction QChange fields where the TransDate <= date of interest. (Which could be today if you want current inventory.)

See where this is going?

You can add other fields to these transactions to link them to tables that hold appropriate details, but this is the basic method of doing a running inventory. That's why I suggested that once you could manage the coding requirements, you would want to lose the "static" quantify field in the stock table.

 

[redblaze]

i think i understand what u mean...
the only reason i would prefer the other way is because i have well over 400 products in the database and with each haveing on average 4 possable sizes the total number of records comes to around 1600. and because i have a sample picture (OLE object) for each product, [both the front and back of each product] i would rather have items of all sizes as one product. is there no other way of structuring the whole database that could tie in with keeping track of the stock like that? if it helps i could send you the database as it is right now. thanks.

 

[Pat Hartman]

I also remember the discussion and we did point this problem out to you (a reference link would have been helpful so we could review the previous post without searching for it).

Your product list is a higher level of information than your inventory. Use the structure I recommended earlier to keep your product list (including the reference to the picture). Inventory MUST be kept at a SKU (stock keeping unit) level. Therefore, you're going to end up with 1600 SKU's. So you need a second table. It's pk is the same as the product table PLUS size. If you sell products that only come in a single size, use a separate size group to represent that.

Use the product table to produce your catalog. Use the Inventory table to process orders.

 

[The_Doc_Man]

You said

and because i have a sample picture (OLE object) for each product, [both the front and back of each product] i would rather have items of all sizes as one product.

You miss the point. You can have the same exact product table. But the SKU and size are in a DIFFERENT table that possibly links back to this table with a product code. Maybe the SKU is in your existing table, but your INVENTORY list then has SKU as one field, Size Code as another field, and the rest of the information you need for tracking. (Personally, I would vote for SKU being unique per size and having a product key that is the same for all products of the same design but different sizes. But that's me.)

You are absolutely right to be concerned that you might end up with more entries. You surely want to conserve size where possible. Disk space surely isn't infinite, nor is your patience at going back and tweaking things.

But where you missed the point is that you can take advantage of the commonalities between items that differ only in sizes. This is a PERFECT example of a one-to-many relationship, with the ONE side being the description and the MANY side referring to the many sizes you offer.

So what you do is split the size info into a table that will have more entries (can't really avoid that), but point back to the product description table that DOESN'T need to have any more entries until you add new products.

Access can do that for you in its sleep, more or less.

Now, if you need to look up something, you can build queries that JOIN your product and inventory tables together, linked by your product description codes.

I.e. if product table contains

-productID (Prime Key)
-descriptive data, pictures, etc.

and if inventory table contains

-SKU (PK)
-productID (Foreign Key)
-size code

Then you put your pretty pictures in the product table. You only need to create a VERY SHORT entry for your sizing information. Where you go from here is up to you.