What table gets the barcode in my inventory management database?

[KarenR]

I am completely new to posting anything so please forgive me if I goof up. I'm educable.



First, I am creating an inventory management database (we have some special needs so a canned one won't work). We want to use barcodes. My database will have a master Product Table which will store a list of all the products we have and an Inventory Table which is where I will 'receive' the actual inventory we get. Other than that it will mostly be lookup tables. The Add Inventory Form will look to the Product Table to see if that item already exists in the Product Table with a link to add a new item to the Product Table.

I would normally think to put the barcode field in the Product Table except for this: while some products will all have the same barcode (latex gloves, large, McKesson) other things that have separate serial or VIN numbers will need separate barcodes like laptops or vehicles. This makes me think I need to put the barcode field in the Inventory table. So, first would that be correct?

Second, it would be nice if when I am putting a new product into the Product Table I can choose whether to add a barcode at that time or not. By that I mean if I'm adding latex gloves, large, McKesson I'd like to add the barcode there. But if it's something with a serial number, etc. I'd like to wait and enter the barcode when I add it to inventory as I would not capture the serial number for that item until that point. Any ideas how I do that??

This second question/idea would shorten data entry down the road as I'd only have to assign a barcode once for the more generic stuff (gloves) in the products table and have it show up in the Inventory table as I'm adding new inventory. BUT I would need the barcode field to be blank if there was no barcode added when it was added as a Product (e.g., because it had a specific serial number) AND I would need to be able to fill in that blank barcode field in my Inventory table and have it 'stick' to that inventory item.

If I can't do this I'm going to have to maintain a separate list of 'generic' items and their barcodes so I can find that barcode whenever I enter more of that item (e.g., gloves) into inventory which seems inefficient and open to items having another item's barcode by accident. And there is a lot of the 'generic' items.

I work for an emergency response program at a county health department and during an emergency or an exercise other people will be using the database. Unfortunately, because of the nature of my program, these people will have training every year or so and may use it during an exercise once every 2-3 years. Yes, we will provide them with some printed cheat sheets and during an exercise they'll be working on a copy of the database but if we have an actual emergency they will be working on the real thing. Some of these people may be good with databases but others will not. Meanwhile, I will be working in the database every day as I'm the one who maintains our inventory records. I can just see that separate barcode list becoming a problem.

Any ideas would be greatly appreciated.

[June7]

Your database is mixing consumable supplies with accountable assets.

I've never used barcodes. Are you creating your own labels and affixing to items?

Certainly records can be entered without barcode as long as it is not primary key field.

Several threads in forum have discussed this. Search keywords: asset management, consumables. Here's a couple:

https://www.accessforums.net/forms/s...orm-48883.html

https://www.accessforums.net/databas...ems-48590.html

[KarenR]

Yes, I'm trying to track both consumable and assets in the same db. I realize that has it's own issues. I'm using a mix of creating my own and using the barcode tag #'s my employer places on big ticket items.

I will check out the links you gave me and search on those keywords. Thanks for the help.

[June7]

The tags on assets are property numbers - unique ID numbers?

Is purpose of Product table just to provide a lookup source for consumable supply items?

If the asset items will not be entered into Product table, sounds like the barcode will have to be primary/foreign key and therefore in both tables.

[KarenR]

Yes, the asset tags the dept puts on are unique numbers. The consumables would share a barcode. I.e., all large gloves by XX manufacturer would have the same barcodes. Large gloves by a different manufacturer would share a different barcode. Also, some assets would have the same barcode. I have special coolers to transport medicines and they do not have serial numbers so I would store them as X number in this location, X number in that location. Saying that made me kind of re-think that as to maybe they should be tagged separately but then I remembered that I have 60 walky talkies. Now, they do have individual serial numbers but they are a minimal cost item and I'm not entering--or inventorying--60 separate walky talkies with separate SN's.

Product table stores both consumables and assets. For example, I have 28 laptops. Since laptops have individual serial numbers I have to enter them as a product so I can 'receive' them into inventory over and over again with individual serial numbers. There are other assets we buy and then buy more of.

I've been trying to work out in my head how I could enter some barcodes into the Product table (a not required field) and then enter a barcode into the Inventory table as I 'receive' the items. I'm wondering if the barcodes should be stored in a separate Barcode table with the keys for the Product and Inventory tables being the link. But then I'm not sure about pulling that info out later. When I do an inventory I'll want to print a list of every item with its barcode. If I set up a query for this I would add the Barcode table and somehow need to have the query results show all barcodes whether they come from the Product table or the Inventory table. I can't quite see it in my head and may just have to try it to see if it will work.

[orange]

I want to highlight that inventory management is not a trivial/simple subject. If you "google" or "bing" database inventory management
in any of the database forums, you'll see several discussions on the subject. I note that this is your first post, but have to ask if you
have designed, built or maintained any operational database.

There are several tutorials on database design.
There are a number of free inventory databases (I found via google).

[KarenR]

When I came to work at the health dept 14 yrs ago I was on my way to becoming a database expert. I had written and maintained 3 successful db's for other places I had worked. However, we are very compartmentalized here and I was told "oh, no, only so-and-so is allowed to work in Access". Hence, I wasn't able to keep up with changes in Access or increase my skills and you know the saying: use it or lose it. But I'd been very lucky to have gotten a good grounding in designing a db including the idea of doing it on paper first. Over the past 14 years I have worked in 2 db's here that someone else had created. I have added fields, edited or created forms, reports and mid-level queries.

I recently took a class and have done a couple of really good tutorials online (one was at Lynda.com) and was pleased to find that I already knew a lot of what they taught. The biggest thing was needing to learn what the changes in Access have been. I.e., things we couldn't do before that we can now. But I know I have more to learn.

Over the last 3 yrs I have worked with various canned databases, free and purchased, but each of them is missing something important that I need to track. Emergency planning and response has some different needs.

I feel pretty comfortable working on creating this db but know I need some help with parts of it. I also understand that trying to track assets and consumables in the same db is an issue.

[orange]

Good stuff. How does the company/department track "things" now? Do you have a clear statement of what exactly is/are the business facts that your database is going to support? Do you have a data model of the things involved and how they relate? Do you have some test data/scenarios? Is management asking and supporting the development of this database?

I have not worked with barcode readers, but others on various forums have.
Also, there are a number of free data models at Barry Williams site.


Added this link re barcode scanning with ms access

[June7]

Might just have to manage the supplies and assets separately. Supplies go out and don't come back but are replaced in stock. Assets are moved around - 'checked out and in' like library books. As Orange indicated, there are existing templates that manage consumable inventory and templates that track assets. And there is a library db template that does the 'check out and in' management.

Can build one db file but it could have 'modules' for supplies and assets.

[KarenR]

Thanks for your suggestions. Apologies for the slow response. I've been off since last Wednesday p.m. Now that the holidays are over I'm going to have to cut back on how much time I can spend on this so give me some time to look over what you've said and look at the various links.

Orange, thanks for the Barry Williams link for data modeling. I'll try to answer your questions soon.

June 7, I do need it all in one database file but modules could be an answer. I'll have to look that up online for some ideas. And I have checked out free inventory db's but they all miss tracking something I need badly.

[June7]

By 'modules', I just mean buttons on a main menu form that give user choices. One button opens Supplies menu form and one button opens Equipment menu form.