Setting Up New Comprehensive Database

[bmpreston]

Ok, so its been 14 long years since I last tried to touch Access. I feel as confident now as I did then, lol

I'm trying to slowly build a master database. Here's where we as a company currently stand.

Currently on approximately 100 excel sheets we track part numbers, current on hands (formula based), total items received (per part number), total items billed to our customers (per customer)

On a master sheet, we have each part number, description, and price. Using a VLOOKUP option, we can bring over the appropriate values by using a VLOOKUP on the part number, etc.

Once per six months we inventory to get the initial on hand (all billing of parts, receiving of parts maintains the on hand values up and down, respectively with basic formulas) all tracked on the 100 individual tech inventory sheets.

Time for a proper database so we can run reports in one swoop to check total dollar figures, total PO numbers, re-order limit, etc

I'm determined to make this happen. Once I get the tables built I can query and forms for adding data, etc

With all these different technicians having inventory, each would require their own table, isn't this true?

So first question is the tables:

I have:

Master Parts List
Part SKU
Price
Part Number
Description
Vendor Name
Category


Technician
First Name
Last Name
Phone
TechID
Region
UserID


Regional Information
Region Number
Division
Phone Number
Email
First Name
Last Name


Tech Inventory
Part SKU
On Hand
TechID
Initial On Hand
Desired On Hand


Received
PO Number
Part SKU
Quantity
Inter-Tech Transfer (Yes/No field)


Billed
Job Number
Store Number
Quantity
Part SKU
Inter-Tech Transfer (Yes/No field)


Vendor Info
Name
Address
City
State
Zip
Rep First Name
Rep Last Name
Rep Email
Phone Number


Any help is tremendous on how to do this. I will be building this shortly and creating relationships. I already built a 'sharepoint' version, but I can't create relationships. So I'm confused.

Thanks in advance as I move down this road. Excel's time is up on 200+ spreadsheets and pulling data in from all over to create more and more sheets. Time to do it right.

Thanks!

 

[Micron]

each would require their own table, isn't this true?

If they're not based at a remote site and using their own db, then no way. You need to think of db relationships as entities & attributes. If a part is determined to be an entity with several attributes, then you need a parts table with all the part attributes applicable (PartNo, cost, weight, whatever). WRT your question, maybe PartLoc (part location) is a needed attribute. If you send out parts for repair/rebuild then you probably need a part transaction table to distinguish between sold, or being part of an assembly in process, or being out for repair. Stock count? No. That is the Inventory entity. Supplier? No. Not only would there be a Supplier entity, it is likely that there can be more than one supplier for a part, and no, the answer is not to add extra table fields for more suppliers. That would be 'spreadsheet thinking' and Excel layout needs to be forgotten when it comes to db architecture, which is row based, not column based. Here's a list of new/refresher stuff to get familiar with these and other concepts.

Normalization is paramount. Diagramming maybe not so much for some people.

Normalization Parts I, II, III, IV, and V
http://rogersaccessblog.blogspot.ca/2008/12/what-is-normalization-part-i.html
and/or
http://holowczak.com/database-normalization/

Entity-Relationship Diagramming: Part I, II, III and IV
http://rogersaccessblog.blogspot.ca/2009/01/entity-relationship-diagramming-part-i.html

How do I Create an Application in Microsoft Access?
http://rogersaccessblog.blogspot.ca/2009/05/how-do-i-create-application-in.html

Important for success:
One source about how to name things - http://access.mvps.org/access/general/gen0012.htm
What not to use in names - http://allenbrowne.com/AppIssueBadWord.html
About Auto Numbers
- http://www.utteraccess.com/wiki/Autonumbers
- http://access.mvps.org/access/general/gen0025.htm

The evils of lookup fields - http://access.mvps.org/access/lookupfields.htm
Table and PK design tips - http://www.fmsinc.com/free/newtips/primarykey.asp
About calculated table fields - http://allenbrowne.com/casu-14.html

Believe it or not, large paper (like flip chart) and pencil is still a good way to map out a db's schema. You make notes, jot down questions, draw lines between tables, id data types in foreign tables, flesh out basic form design, etc. As for forms/reports, I try to build suitable queries first. If I can't edit, delete, append where required, building a form first is kind of pointless. When you read up on naming conventions, don't settle for the default names Access gives to form/report controls when you use a wizard. There are tons of tutorials on the web for building databases. Also, you might find that someone has already developed a data model that you can use as a guide.
So many tips, so little space.