Advice on a simple inventory database in access 2

[mbaldridge]

Hi, As you can see I am new to the tek-tips community. I am currently working on a project at work where I must converge 6 spreadsheets with 4 columns of information (1 unique identifier) and about 20,000 rows a piece into one easy to navagate and search database.
I cannot simply paste these all into one Excel spreadsheet because it will overload any computer here.

Pardon me if my questions are rather elementary, but I just want to do the best job I can and build a database that can grow with our company. Where do I start when building a database? Does anyone have a simple solution?

Thank you in advance for your help. Please let me know if I have left out necessary information.

Mitchell D. Baldridge

 

[fneily]

If you open Access, click on File - New and then the Database Tab. They're some already designed databases there including an inventory one. If you know nothing about Normalization, relationships, joins, etc. then that's a good start.
If you have no prior Access experience, in my opinion, you can't learn it yourself. Too many non-intuitive concepts, nuances and protocols. Take some courses somewhere. It's not something you'll pick up in six months like the rest of Office.
Also, have you ever programmed in VBA?

 

[johnherman]

Also check the Library of Free Data Models. You might find a model which can give you a big head start.

http://www.databaseanswers.org/data_models/index.htm

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[lespaul]

read the fundamentals document linked below to learn more about how to decide what tables you need and the fields that belong to them....

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[MichaelRed]

there is no such thing as a simple inventory program

simple and inventory are mutually exclusive



MichaelRed

 

[lespaul]

I had that thought too Michael, but decided I didn't want to dash the OP's enthusiasm!

Leslie

 

[mbaldridge]

So I am beginning to learn..

There is just too much data for it to run on Excel, or I would be happy to keep it there and just use ctrl+f

Thank you for all your helpful comments.

Mitchell

 

[MichaelRed]

Excel is only in the bottom 10 applications for an inventory management process. I rank it only a bit below the back of used envelopes and a collection of shoe boxes.




MichaelRed

 

[SkipVought]

Qh, Michael, pa-lease!

Excel belongs in, at LEAST, the bottom eleven! Let's get REAL!!!! ;-)

Skip,

_{To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white]}

 

[lespaul]

we're not saying, don't do it....we just want you to be realistic...any inventory system is going to be a huge complex project.

Have you read the fundamentals document I suggested? You are going to want to read that, review your spreadsheets and decide on your tables and fields. Come back and post the table schema along with a description of the system you are trying to build and we will help tweak your table design and help you plan your design document. You are going to want to come up with a list of requirements of what the system needs to accomplish.

Leslie

 

[MichaelRed]

... bottom 11 ... ? ok, could you please post the one i missed?

more seriously, Mitchell, please take LesPaul's thoughts into account. There are many different approaches to inventory management as well as many goals for the process. Often the goals will change throughout the life cycle of the system (so called "Mission Creep"). Knowing as much as you can about the goals (immediate and potential) may ease some of the future transitions. I, personally, would recommend a fairly extensive study (both academically and practically) of at least a few of the more divergent approaches of existing systems as well as extensive dialog with ALL departments whic will interface with it to assure that the approach supports the needs. Finally, if the project does go forward, make sure the (computing) resources will easily accommodate both the data (storage and retrieval) and processing capability necessary. You are posting in the Ms. Access forum, and I can assure you that a robust inventory system can easily tax the resources in the native (i.e. Jet db engine) mode.




MichaelRed

 

[BobRodes]

>the back of used envelopes and a collection of shoe boxes.
I assume that the shoe boxes are parents and the envelopes are children, given that an envelope can't contain a shoe box.

<any inventory system is going to be a huge complex project.
Well, lots of them are, but some aren't, and maybe the OP's isn't. Our Maintain Services department asked me to put together an inventory system for them. Upon investigation, they needed to keep track of about 300 laptops that are available to our employees. They needed to keep the serial number of each one, and the name and employeeid of whoever has it checked out. They didn't care about historical data, as they required each employee to sign a sheet of paper indicating that they had the machine. A simple Excel spreadsheet sufficed.

Now, it's not clear to me that the OP has anything a great deal more complex than this, so let's not discourage him with the idea that he needs years and years of schooling to be able to be able to master the arcane complexities of relational database design.

Ok, Mr Baldridge. While the advice you've gotten is very good, I'd still encourage you to take a crack at designing a database intuitively. The main point of design is to break all your stuff into one to many relationships, and resolve any many to many relationships with some sort of table that's in one to many relationship with each of them.

So, inventory for example. You have products. You have recipients of products. You have orders, both to put stuff in and take stuff out. You have maybe people who stock the place. That sort of thing. Identify all of that stuff first. Then sort it into one to many relationships, and work out what characteristics of each thing you're going to keep track of. That will give you a good beginning.

Then, set up your database. For example, you'll have a table for products and a table for orders. An order will have many products on it, and a product will have many orders for it, so you have a many to many relationship. A single line item on an order will be for only one product, though. So, you create an orderitems table, and relate it to both products and orders. Once you've gotten your tables pretty well hammered out, then go into Access and start designing a database. Put in your tables, and there you are.

For some examples, get a look at nwind.mdb and biblio.mdb.

HTH

Bob

 

[SQLSister]

I'll add one thing that created a problem in the last place I worked ( form before I worked there)and that I had to fix.
If you are storing the current price of the item in a table, store that data at the time the item is added to inventory in the table that stores the specific items in inventory. Do not rely on the price in the product table for the price of the items in iventory as it changes, but that is not what you paid for the item and it will screw up your accounting reports if you don't store historical costs instead of current costs of products. Inventory done correctly will usually need to interface in some way with accounting. YOu need to thoroughly understand how if you are developng an inventory system. Alternatively, your accoutning software might have an inventory module. It might be best to use that instead of developing one.

"NOTHING is more important in a database than integrity." ESquared

 

[lespaul]

SQLSister,
I've seen that happen too...and it's great advice, but I think we've lost the OP!

Leslie

 

[BobRodes]

Yeah, looks like we've scared him off. In real life, that would potentially translate into a nice pile of dollars going into someone else's pocket, now wouldn't it?

My concern here is that we haven't asked the OP what he needs to do, and have basically convinced him that whatever it is, it's way beyond his capabilities. But we don't really know that to be the case, as we haven't really established the nature of his problem domain. For example, I don't see any place where the OP said he needed an "inventory control system"! We put those words in his mouth, and then started telling him about how difficult a thing it was to do. Maybe all he wants is a flat table to keep a list of 40,000 shoes! And the only clue we have as to what he really wants is that he has too many items to fit in an Excel spreadsheet.

Furthermore, I don't see where just importing 40,000 records into an Access table requires a bunch of higher education to become familiar with the counterintuitive nuances and protocols that are certainly a part of Access. The guy needs to import a few Excel spreadsheets into Access and merge the tables. Then he needs to read up on database theory before he tries to do anything more sophisticated.

Other thoughts:

The simplest approach to historical costs is to go to the order line items. The main reason that that doesn't always work is if you need historical costs on items not sold. In that case, there are several approaches, the simplest being keeping a table of individual inventory items. Since a lot of individual items can be the same and cost the same (hardware stores!), though, that can be less than optimal in terms of performance. So, another solution that often works better is to keep inventory items as lots, and decrement a counter each time an individual item is picked out of the lot.

I always do my best to avoid overengineering. I believe we all suffer from the overengineering bug, because we've all been burned in the past by underengineering. But overengineering doesn't work much better; since the stakeholders tend not to buy into the offered solution (costs too much, too hard to use, etc) it becomes difficult to implement it. Often what happens in that situation is that the solution is incompletely implemented, if at all.

Bob

 

[monkeysee]

BobRodes

I am interested in your comments regarding the costs of goods on the inventory scenario you guys have been talking about.

I don't have an inventory program, but I do have a program that we bill fees out on licensures, etc.
So far, we have not had an increase in fees, but I do see that coming. I have given this much tho't and really haven't come up with a real good solution. If we do have a fee increase, the fees in the existing table will become obsolete in a sense, but I do not want to 'overwrite' the old fees with the new charge as it would throw off the billing and payment history of existing data. I also don't want to add new description of the fee just to reflect the new fee. So...how would you approach this.
My table is: Fees: with FeeID (primary key); description; Charge; inactive. (I tho't that if I used an inactive y/n field, I could filter out the old fees when having to bill under the new fees) Am I on the right track? Or is there a better way?

 

[MichaelRed]

an approach is to separate the cost & price from the actual "inventory" as a product-price table. This only needs to include the product ID, the price and the effective date of the price (an extension / simplification can be made to also have an "end date" of the price effectiveness for the weak of heart ... )



MichaelRed

 

[BobRodes]

First, it's important to always think in terms of one to many relationships. You have one table, fees. Well, if the fees change, how are you going to handle history? Ok, so now you have two concepts: fees and the application of fees to actual services performed--if you hadn't charged those fees, you wouldn't have any history. These become the entities in your problem domain.

So, you have fees and services performed in which those fees were charged. I'm assuming you also have a client table. So, your structure is this:

Fees
Clients
ServiceTransactions

Fees and clients are both in one to many relationship with servicetransactions, which are each one charge of a fee to one client. One of the fields in servicetransactions is the actual amount charged for that service on that date to that client.

All that given exactly as stated, you can get your history of fees from the servicetransactions table.

Now, Michael's solution implies another level of sophisitication, in that he has a table that specifically addresses fee history. That's the next step, if you can't get your history for some reason from the existing basic structure. For example, you might find that you were charging way too much for a service and nobody wanted it, so you dropped your rates. You wouldn't be able to get that from the servicetransactions table, since that table would only contain fees that were actually charged at least once. In that case, you would indeed have to maintain a separate feehistory table. But I would first make sure that you can't use the basic structure to do what you need to do, before adding more levels of sophistication. I would also suggest that being rigorous about getting to the bottom of the problem domain will optimize your ability to get to the bottom of the database schema (structure) that you need.

Does this help?

Bob

 

[mbaldridge]

Haha,
You did lose me, in many ways, but now I am back from vacation. I am going to work on building fundamental goals and intuitive design for the table starting Monday and get back with you all for more suggestions. I am somewhat startled at the volume of help I have received, and very appreciative.
Like Mr Rhodes was saying, I feel like I might be getting directions on how to excecute a left turn properly when I have no idea how to steer, so I will attempt again to lay out what I need the table to do.

I work in a District Attorneys' office in records. We have many files here, but have nowhere the amount of space needed to maintain the files, so we pick them out a few times a year to send them to a warehouse. over time we have developed four huge spreadsheets (labeled by year) with 4 colums and 50k+ rows a piece. The colums represent

1. Box # - approxamitely 15 or so files a box. Each box has a unique number.
2. Last name of criminal.
3. First name of criminal (along with middle and suffix)
4. Unique number to identify the criminal's file

to pull up a file, we go to the year we assume the file is in and do ctrl+F on the unique criminal number usually.

I have cleaned up all these spreadsheets so the data matches up, but if you add them all together the file is over 15 megabites and just requires too much memory for our machines to manage that file (it takes 5-10 minutes to save info on the file).

I would happily answer any more questions you have, and do again appreciate all the help you guys (and ladies) have offered.

Mitchell D. Baldridge

 

[BobRodes]

Yup, you sparked a fine discussion.

As I suspected, you only want to use Access because you have too much data to fit well in Excel. This is an entirely valid reason to use Access, all of the above notwithstanding. So, for starters, do this:

1. Open one of your spreadsheets. Make the fourth column the first one. Save.
2. Go into Access. Create a blank database, call it what you like. Save.
3. In Access, select File/Get External Data/Import... Navigate to your Excel file and select it.
4. You'll get the "Import Spreadsheet Wizard". Select Next. On the next screen, check the "First Row Contains Column Headings" check box (assuming it does).
5. Hit Next. Hit Next again. Hit Next a third time. On the resulting screen, select "choose my own primary key," then select the column with your unique number from the dropdown box.
6. Hit Next. Come up with a nice name for your table, perhaps the same name as your spreadsheet, and hit Finish. If you get errors, it's probably because your spreadsheet's unique values aren't as unique as you thought. If that's the case, post back and we'll talk about some ways to get to those.

There you go; you've had your first Access lesson. Wander around in what you've created, then decide whether you want to have your other spreadsheets in other tables or in the same one. You DO want to keep them all in the same database. Don't create separate databases for each one.

You should indeed make yourself familiar with some of the documents and concepts suggested in here. In particular, have a look at the ones in lespaul's signature. Save VBA for later; once you get to the point where you have a well structured database and are doing repetetive tasks manually, you'll want to begin taking that up.

HTH

Bob