MS Access Tables Set Up 1

[susanm172]

My company has asked me to create a database with all of our items along with a lot of information about each item. The tricky part is that there are 18 different divisions in our company. Some of the item information will be the same no matter what division it's in (like description, size, itemm number, etc.) and some of the item information will be different depending on which division it's in (cost, case pack, etc.). Because there will be reports generated from this, I want to make sure that I start out the right way as far as setting up my tables and building relationships. What's the best way to set up these tables? A separate table for each division or a separate table for each "category" or is there a way to have all of this information in the same table?

 

[dhookom]

I suggest you read up on database normalization and design. One great book is Mike Hernandez "Database Design for Mere Mortals, Second Edition"

http://www.viescas.com/Info/books.htm#Database Design.

You haven't provide near enough information for anyone to provide accurate replies.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[fneily]

If you're new to Access, you have ALOT of studing and training ahead of you. You should take some courses first and find an actual Access programmer(very few out there) to help you. Here's a reommended article:
Fundamentals of Relational Database Design

http://r937.com/relational.html

I would suggest you create some tables you THINK are appropriate. Then go through the first three steps of NORMALIZATION on each and EVERY table. Is this necessary? YES!!! Then you'd create your relationships. Then figure out any defaults, required fields, ets. Then you actually type in the data.
If you can create some table structures, post them here in Tek-tips so we can see them, you would get more specific suggestions on what to do.
Also, be aware - you'll also probably be coding in SQL, ADO/DAO, VBA.

I see three tables. tblDivision, tblItem and tblDivItm
tblDivision has all the info on the division with a primary key of DivID.
tblItem has item info with primary key of ItemID.
tblDivItm is a "junction table" with its own primary key DivItmID and fields DivID, ItemID, Cost, CasePack, etc(any COMMON fields related to both Divsion and Item). Now, queries you can get reports on a divison's items, which item is in which division, costs/divison/item, etc.

 

[susanm172]

Thanks, fneily!
I'm not that new to Access, but this is by far the most complicated database I've attempted. My first round of table design does have that set up. Perhaps my reports and queries are my problem! I will keep working on it and post more questions as the arise.
Thanks Again,
Susan

 

[genomon]

Susan - open access and go to the templates tab. There are some good examples there to get you headed in the right direction as well.
Good luck!

The early bird may get the worm, but the second mouse gets the cheese in the trap.