Creating an inventory system for serialized products (2 Viewers)

[jmark@stayintouch.us]

I have a well developed inventory system for our company that buys and sells roughly 4000 products. Currently, none of them are serialized. So, to report the inventory value each month (or on demand), I use queries to determine how many of each product we have in each of our 30 possible locations and calculate an average cost and an average value of only the quantity that we have on hand. This can be highly inaccurate since sometimes a single product can be sold for $10 or for $10,000 for the same type of product, and the averages are not accurate.

I need to figure out how to serialize each product with it own sku tag, so the users can pick specific items to sell based on the cost attached to them.

Does anyone have any experience with this? Im looking for general concept guidance and not specific coding. I could send you my DB, but it will certainly be overwhelmingly complicated and probably cause you more confusion that help.

Thanks!
JM

 

[Pat Hartman]

Each serialized item must have a uniqueID (autonumber). The row also contains the manufacturer's product code and serial number and the quantity is always 1 for a serialized item. If you post your table design for products and inventory, we will offer suggestions for how to change it.

 

[jmark@stayintouch.us]

thank you for your fast reply. i will think about what you have said and maybe try playing with some forms and code. I may reach out to you again shortly if that is ok. Thanks again! JM

 

[Pat Hartman]

As I said, post your existing tables for this part of the schema. Don't make us guess what you have now.

 

[gemma-the-husky]

My theory is that vendors selling serialised goods do not know (or even care) which serial number a buyer has until the buyer registers the purchase. eg TV's and electronics, or white goods.

to take your example, how you can you possibly have the same item being sold for $10 or $10,000? If you are selling used watches, for example, you need to inventory each watch separately. I would have thought.

 

[jmark@stayintouch.us]

Hi Pat.

I finally got my db scaled down and thinned down enough to show the intent of my question. I hope. I took out hundreds of reports, queries and forms, but I think the basic Inventory functionality is working properly for you to possibly help me. Currently I keep track of ALL items we sell, as far a inventory, by just a simple overall count of all the "Purchases" minus all the "Sales". We have "Inventory Adjustments" that we make each month after a Physical Inventory count is done at the end of each month. We have data that goes back to 2016, but i have the queries set up to calculate the Average Cost and Average Current Value only over the past 12 months, to get a more accurate guess as to the value of the inventory. These averages are then simply multiplied by the actual quantity on hand, (which is the total Purchases minus the total Sales as I mentioned, over ALL time) to get the current inventory Cost and Value as is shown on the single inventory Report i am including.

The issues is that some items we buy and sell, can have vastly different values (that may not be known until after they are bought and go through our QC process). For example. We buy coins. At the time of sale we may not know the real value of the coin until we send it in for grading. we may pay $10. Send it for grading. Find that it is worth $10,000 and now we need to recategorize that one coin to a serialized category so we can be sure not to sell that specific coin along with the other hundreds of similar coins that are only worth $20.

I need guidance on how to set up a serialized product, in general, while keeping within the current framework of our existing db structure as much as possible.

If you are interested in helping on this, please let me know.

 

[Pat Hartman]

My theory is that vendors selling serialised goods do not know (or even care) which serial number a buyer has until the buyer registers the purchase.

That isn't actually true. They don't care if they sell #1 or #2 as long as both are the same build. They will want to sell oldest first. But the choice of which to actually put in the box is likely to be made by the person who picks the order. But, whatever is picked is logged as sold on order x. I'm guessing if you tried to return an item with a different serial number, someone at the vendor's return processing site would care. Maybe not if the item was low value but anything more than a few hundred dollars, I'm guessing that you better be returning the right serial number or be prepared for questioning. In the end, most companies treat the customer as "always right" and so will take back the unmatched product.

@jmark@stayintouch.us I'll download the files and have a quick look.

 

[CJ_London]

now crossposted here

Creating a Serialized Inventory system in access

Sorry. This was crossposted. It is removed from here.

www.accessforums.net

not the first time

 

[Pat Hartman]

OK, I know why you had hundreds of objects. You have a bunch of repeating groups in your tables. The ones that are mutually exclusive will be easy to get rid of along with the other objects that were necessary because of the incorrect table design. For example, forms like Bank Balances have to be hard coded because you have used repeating groups in the tables instead of properly normalizing them. I'm sure this symptom ripples through the entire application. Even worse is if you add a store, you have to modify the Transactions table to add new columns plus change every report/form/query that uses the table to include the new location.

I'm guessing that the actual inventory is in Line Items and the Transactions are events that modify the inventory but I really can't spend hours/days/weeks in this rabbit hole trying to figure out how your inventory currently works. It makes sense that bulk items are not serialized but since coins are always graded, it seems like at a minimum, you would have to have them broken down into grades for each type of coin as well as year and mint. I see some of that but not all.

Perhaps someone with a better grasp of the details of the subject matter will be able to help.

You've done another very annoying thing. You have prefixed all your column names. This makes opening tables/queries to look at data very frustrating. since I can't see any actual column names. All I see is the prefix. This isn't wrong but it sure makes it too annoying for words to work with if you haven't memorized all the columns and what they contain.

In general, you will need to get rid of most of the repeating groups because otherwise instead of modifying a couple of objects to handle serial numbers, you will need to change hundreds. Then you need to add the serial number to the inventory table. Items with a serial number always have a quantity of 1. Items without a serial number are sold in bulk or without the ability to select a specific coin.

 

[gemma-the-husky]

That isn't actually true. They don't care if they sell #1 or #2 as long as both are the same build. They will want to sell oldest first. But the choice of which to actually put in the box is likely to be made by the person who picks the order. But, whatever is picked is logged as sold on order x. I'm guessing if you tried to return an item with a different serial number, someone at the vendor's return processing site would care. Maybe not if the item was low value but anything more than a few hundred dollars, I'm guessing that you better be returning the right serial number or be prepared for questioning. In the end, most companies treat the customer as "always right" and so will take back the unmatched product.

@jmark@stayintouch.us I'll download the files and have a quick look.

I was thinking of say a washing machine or refrigerator manufacturer who make hundreds or thousands of machines. They might conceivably know which serial nos were sent to Walmart, say, but they surely can't tell the serial number from the outer box. So until the customer registers his purchase, they won't have a record of where and when each serial no was actually sold, and as the customer needs a proof of purchase to get warranty work carried out, it won't matter. I expect the same applies to wristwatches, say. I would actually be surprised if they even know which batch of serial numbers were shipped to a customer.

That's just my personal thoughts, and I might be wrong, but once you start wanting to track serial numbers, you no longer have homogeneous inventory, and it becomes much more difficult.

It's also why I struggle to understand how the OP can have the same SKU selling for £10 or £10000. But even if he can, if it's the same product what difference does the serial number make. And if it's not the same product, it should have a different SKU surely

 

[Gasman]

now crossposted here

Creating a Serialized Inventory system in access

Sorry. This was crossposted. It is removed from here.

www.accessforums.net

not the first time

Crossposted here as well

UtterAccess.com

www.utteraccess.com

 

[Pat Hartman]

@gemma-the-husky The inventory in question is coins. Bulk metal is worth its metal value on the spot market but coins vary tremendously in value depending on their condition and rarity and possibly even provenance.

 

[gemma-the-husky]

@gemma-the-husky The inventory in question is coins. Bulk metal is worth its metal value on the spot market but coins vary tremendously in value depending on their condition and rarity and possibly even provenance.

Well I presume coins of different quality would be referenced differently. If it's a used coin dealer though, I presume each coin would be referenced with a unique number, and placed in a labelled envelope of some sort. So maybe you would have "sovereign", and then subfilter by year and condition, but each coin would be individually stored for identification.

(I had more than this but accidentally cancelled the edit, so I'll give up here.)

However, it is an interesting and perhaps tricky real world problem to deal with, as much for the documentation and cataloguing aspects. I often wonder about this when I visit my local Cex shop (sells used dvds, blurays, games, and electronics, etc in the UK)

 

[Pat Hartman]

Well I presume coins of different quality would be referenced differently.

They are but not individually unless they are valuable. Otherwise the OP would already have a serialized inventory.

 

[jmark@stayintouch.us]

OK, I know why you had hundreds of objects. You have a bunch of repeating groups in your tables. The ones that are mutually exclusive will be easy to get rid of along with the other objects that were necessary because of the incorrect table design. For example, forms like Bank Balances have to be hard coded because you have used repeating groups in the tables instead of properly normalizing them. I'm sure this symptom ripples through the entire application. Even worse is if you add a store, you have to modify the Transactions table to add new columns plus change every report/form/query that uses the table to include the new location.

I'm guessing that the actual inventory is in Line Items and the Transactions are events that modify the inventory but I really can't spend hours/days/weeks in this rabbit hole trying to figure out how your inventory currently works. It makes sense that bulk items are not serialized but since coins are always graded, it seems like at a minimum, you would have to have them broken down into grades for each type of coin as well as year and mint. I see some of that but not all.

Perhaps someone with a better grasp of the details of the subject matter will be able to help.

You've done another very annoying thing. You have prefixed all your column names. This makes opening tables/queries to look at data very frustrating. since I can't see any actual column names. All I see is the prefix. This isn't wrong but it sure makes it too annoying for words to work with if you haven't memorized all the columns and what they contain.
View attachment 105090

In general, you will need to get rid of most of the repeating groups because otherwise instead of modifying a couple of objects to handle serial numbers, you will need to change hundreds. Then you need to add the serial number to the inventory table. Items with a serial number always have a quantity of 1. Items without a serial number are sold in bulk or without the ability to select a specific coin.

Pat, I appreciate your comments. The annoying Column Naming has been a problem for me for a long time. I am self taught, 15 years ago and tried to be organized in the beginning and now its just too much to change all the column names. You are also correct about the other flaws in my programming. I am not a professional and I am just trying to make things work as well as I am capable. There are a lot of other issues with my coding, but I am hoping that will not prevent someone who is familiar with creating a serialized product inventory system in Access from giving me some general concepts as to best practices for tables and relationships for that.

You are correct about how the inventory is in the line items and the transactions form is the tool to add, delete and edit it. I am looking for guidance about how the general structure of a serialized inventory might be set up, so I can try to incorporate this new type of inventory management into my existing db.

Thanks again for any help.

 

[Pat Hartman]

See what you can do with what I suggested in #9. If you do not add the serial number to the items table, then pretty much EVERYTHING has to change and you end up doing everything twice. Or in your case, hundreds of times because of all the related objects you will need to change. Once for non-serialized items and again for serialized items. I also mentioned this in other posts to you.

The only extra logic is when you buy/sell items. Serialized items always, only have a quantity of 1. When you sell it, it is gone. For items kept by quantity, you sell 1 and you have 27 left.

now its just too much to change all the column names.

You are depressing yourself into not fixing your foundation. You have been living with it for a long time but you don't understand how much extra effort it takes to manage a poorly designed schema. I'm pretty sure you don't look forward to your programming tasks. I wouldn't either. But it is a little like hoarding. Cleaning out the debris is going to make you feel ever so much better

Do you understand what I meant by mutually exclusive? Mutually exclusive means that only one of the group will contain a value. For example the seven instances of Check_Number in Transactions. Only ONE of them will ever have a value on any given row. Therefore, you would replace them with Two columns. One bound to a combo that lets you select the store and the second will contain the check number. That means that whether you have 7 locations or thousands, you only ever need two columns. One to pick the store, the other to enter the value. Do you think Macy's could support this structure? How many columns would they need? How many other objects have to change if they open/close a store? Instead of separate fields for cash, check, paypal, you should have one field with a type code of Cash, Check, CC, OnlinePayment (paypal is only one of many). Then you have a field for the check number, and one for the CC number (if you choose to store it) or the ID for the online payment method. You also have these broken down by store and by In or out. You have 59!!! fields in this multi-level repeating group and you should have closer to 5 or may be 6 to handle ALL types of transactions for ALL stores. I'm guessing that just fixing these two repeating groups will eliminate a large part of your excess objects, plus narrow down the width of the forms substantially.

I remarked on how you are keeping the spot prices of metals in some other thread. Now I see that not only are you storing the values incorrectly in their primary table but you are also duplicating the values in every single row of the transaction table. So you have 10 columns for the price of various metals, 9 of which have nothing to do with the coin in question, plus a date field. In a relational database, we don't repeat the same data over and over again, we join to the table that contains it and pick it up at that time. In this situation. isn't a coin only one primary metal? I know that many are alloys but the alloy wouldn't vary for the same coin issue. Instead of keeping the 11 fields in each transaction record, you should have one row per metal per date, maybe bid/ask prices if they work like stocks). Then the Transaction includes the date of the transaction and the ID of the row in the metal table for the correct metal at the price you used.

You can attack the repeating groups one at a time. The ones that are mutually exclusive are pretty easy to resolve but you also need to replace all the related objects. Instead of having one related object for each repeat in the group, you will have ONE query/report/form/whatever that might take a variable. ALWAYS make backups before embarking on a structural change. During the conversion process, just leave the old columns in the table. Maybe just move them all to the right edge to get them out of the way and add replacement columns. You have to change the column name anyway, so there is no reason to reuse a column that contains data at this time. When you think you have fixed the problem, rename the old columns with leading x's. Then test, test, and test again. After a couple of weeks, you can make a final backup and delete the replaced columns.

Also, we can't see the answers you are getting on the other forums and so unless you repost them here, we are essentially wasting our time.

 

[jmark@stayintouch.us]

Pat, I totally understand and agree about the naming. but there is just no option to change that at this time. We are a very small company. I work 15 hours a day, 6-7 days a week so its not like I can just fix anything. The banking does in fact need to to accommodate entries in multiple stores as money is moved around as needed. I'm not worried about the banking issues. Nor the spot price redundancy. That is not related at all to what I need help with. I appreciate your trying to suggest improvements for everything, but what i need is specific guidance on how to best create a BASIC serialized inventory aspect to this db. Can you help with this?

ps. i was told that cross posting is not good so i removed the posting from the other forum and this is the only one it is current in.

 

[Pat Hartman]

Naming isn't where you need to start. That isn't what I said. Naming is just an annoyance. It is the repeating groups that are the problem. The mutually exclusive ones are actually easy to get rid of and with them will go lots of unneeded duplicate objects.

The banking does in fact need to to accommodate entries in multiple stores as money is moved around as needed.

The movement of money is controlled by transactions. A transaction would always have a from/to component. If you are using that transactions table to move money around, it is even worse than it looks. Since there is nothing there that indicates a given row is a transfer of money from one place to another.

So far, you have elected to ignore everything I have told you is wrong with the application. Even though I'm telling you that your design flaws are causing you to create multiple duplicate objects. And are the reason that implementing a serialized inventory will be way more difficult than it needs to be. You are the subject matter expert for coins (and I'm not sure how you've managed this long without being able to identify specific coins). I am the subject matter for application and database design. Don't worry about ignoring all of my advice. It is after all "free".

but what i need is specific guidance on how to best create a BASIC serialized inventory aspect to this db. Can you help with this?

I did. Several times. Add the serial number to the item table. THAT's IT. It doesn't get any easier than that. Anything else will require huge changes to the application.

To eliminate potential errors, add a new group level for each type of coin that you want to serialize. That way there will be no confusion. Add a button to "pull" a coin from a "bucket" to a serialized type code. The saves the new row and then adds a transaction to decrement the quantity of the "bucket" coin. But of course, you don't have to do any of that if you don't need the control. You seem to be the only one doing data entry. Just don't make any mistakes as you make both sides of the transaction.

 

[jmark@stayintouch.us]

Ok. thanks for your help. Enjoy your week! JM