Creating an inventory system for serialized products

[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

 

[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

 

[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.

 

[CJ_London]

now crossposted here

Microsoft Access Forums

Discussion forum for Microsoft Access users.

www.accessforums.net

not the first time

 

[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

Microsoft Access Forums

Discussion forum for Microsoft Access users.

www.accessforums.net

not the first time

Crossposted here as well

UtterAccess.com

www.utteraccess.com

 

[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)

 

[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.

 

[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.

 

[jmark@stayintouch.us]

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