Please help with inventory project... 2

[Thorns]

Hello!
I am currently working on a database to be used for inventory purposes. I have 4 different categories of stuff (computer equipment, furniture, books, supplies)
So far I have made a table for each of these with an item number as the primary key as well as a table for department/department number and a table for item number/department number. (so that I can look things up by department or by item)
The problem I have run into is that the autonumber is creating a new item number only in each field respectively and not out of all of them (ex. there is an item #2 in computers and an item #2 in furniture) I need every number to be different. Any help would be appreciated.
Thanks.

 

[SkipVought]

Thorns,

I'd agree with a separate table for department, but I STRONGLY disagree with separate tables for each category of inventory.

ALL your inventory ought to be in a SINGLE table. THis is a BASIC data normalization issue! Make a column for Category. This will solve your AutoNumber problem!



Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[Thorns]

I was worried you'd say that...the problem there is that my boss has specified several specific things she wants to see per category. (example: in books she wangts to have title and author and supplier...this can't also be in computers) Any ideas? Maybe put it all in one table like you said but also keep seperate tables for each as a reference that isn't related to the others? I have no clue!


Nobody can make you feel inferior withought your consent. Not even MS Access.

 

[SkipVought]

There is a term called a metafile: A file that contains other files or information that describes another file.

So depending on, for instance, the Category, the following fields in this table or in some other table will mean different things.

With a single Inventory Table you will be able to SUMARIZE items on a global level or select and summarize any Category level items.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[lespaul]

I would first recommend that you read "Fundamentals of Relational Database Design" in the developers section of:

http://www.alphabetcitydataworks.com

(thanks TTer JeremyNYC for this INVALUABLE document!)

I would have:

tblItemNumber
ItemID
ItemCategory
ItemLocation
etc

tblCategory
CategoryID
CategoryType

tblComputerDetails
ItemID
DetailID
DetailType
DetailInformation

tblBookDetails
ItemID
DetailID
DetailType
DetailInformation

tblItemDetailTypes
DetailTypeID
DetailTypeItem
DetailTypeDescription

tblCategory
CategoryID CategoryType
1 COMPUTERS
2 BOOKS
3 FURNITURE

tblItemNumber
ItemID ItemCategory ItemLocation
1 1 FrontDesk
2 1 President's Office
3 2 Library

tblComputerDetails
ItemID DetailID DetailType DetailInformation
1 1 1 120 GB
1 2 2 256 Meg

tblBookDetails
ItemID DetailID DetailType DetailInformation
3 1 3 Robert Jordan
3 2 4 Winter's Heart


tblItemDetailTypes:
DetailTypeID DetailTypeItem DetailTypeDescription
1 COMPUTER HARD DRIVE SIZE
2 COMPUTER RAM
3 BOOKS Author
4 BOOKS Title

Now, I'm not saying this is the only way, but it's A WAY! You should really take the time to read that article. If you take the time to design your table structures correctly, when it's time to write queries you'll be glad!! Much easier to get information from a normalized database than a non normalized.

HTH

leslie

 

[lespaul]

Actually I would modify it to:

combine these two tables:

tblComputerDetails
ItemID
DetailID
DetailType
DetailInformation

tblBookDetails
ItemID
DetailID
DetailType
DetailInformation


into a single table names tblItemDetails; don't need a separate table for each item type. Sorry still too early in the morning!

leslie

 

[SkipVought]

An EXCELLENT suggestion by leslie! ==> *

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[Thorns]

oh boy...
Thank you both very much for your advice. How would I create my metafile? Can I have more than one?Yes these are probably not great questions but I'm no programmer. It does seem easier than starting over, although I'm willing to do that too if I have to as long as I get this thing working.


Thorns

Nobody can make you feel inferior withought your consent. Not even MS Access.

 

[SkipVought]

Careful design ought to take a significant period of time -- probably 40% to 60% of the total effort. You are handicaped by not having training in database principles. You need to educate yourself. Depending on how important this system will be to your manager, you may need to attend training somewhere to jump-start this effort.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[Thorns]

SkipVought,
Well let's put it this way... They just want the information to be somewhere and be computerized. They initially gave me the choice to do it in Excel or Access. I picked Access because of all of the great things you can do with it. So I went and bought Access for Dummies (which by the way is very good for someone who hasn't studied database design...very clear) I've made it pretty far into it by reading everything I can find and am doing pretty well but I still get stuck which is why I came here for help.
Bottom line: It probably doesn't have to be all that complicated.(Obviously if they mentionned Excel in the first place) Maybe it doesn't even need to be relational. Apparently you can still look up info in Access if it's not, right?
Sorry that was so long winded! Ha ha

Thorns

Nobody can make you feel inferior withought your consent. Not even MS Access.

 

[SkipVought]

Excel can do database like things, but Excel is not the tool of choice for a medium to large database.

If you just need to keep a list of things and it's not critical data considered a corporate asset (data is an asset), used to extract and analyze information to make management decisions, then use Excel and don't worry about normalization etc.

But it seems to me that this IS important data, critical to the success of your organization. Designing an inventory system is not a trivial task.

Consider checking out the Access Inventory Control New Database Wizard.



Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[Thorns]

Thank you, I will.


Thorns

Nobody can make you feel inferior withought your consent. Not even MS Access.

 

[r937]

I would first recommend that you read "Fundamentals of Relational Database Design" in the developers section of AlphaBet City Dataworks

yep, that's a great article

F.Y.I. last fall the author gave me permission to convert the article from Word Doc to HTML and post it on my site:

Fundamentals of Relational Database Design

in case you want to read it online, eh

rudy

http://r937.com/

SQL Consulting

 

[Olaf4]

I suggest two ways to do that.

Way 1 – One Database in one file
Create a table for Categories
Create a table for Suppliers
Create a table for Authors
Create a table for Locations
Create a big table for Items
This last table has to have all fields for every detail of each item. Later in your Forms, you use the needed fields for each category.

Way 2 – Three or Four Databases in one file
Create a table for Categories
Create a table for Suppliers
Create a table for Authors
Create a table for Locations
Create a table for Books

Create a table for Categories
Create a table for Suppliers
Create a table for Brands
Create a table for Locations
Create a table for Computers

Create a table for Categories
Create a table for Suppliers
Create a table for Manufacturers
Create a table for Locations
Create a table for Furniture

Then you build Forms for each database and a Menu with buttons to open each one.