Setting Up New Comprehensive Database


Board Regular
Jun 18, 2016
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
Part Number
Vendor Name

First Name
Last Name

Regional Information
Region Number
Phone Number
First Name
Last Name

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

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

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

Vendor Info
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.


Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college


Well-known Member
Jun 3, 2015
Office Version
  1. 365
  1. Windows
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

Entity-Relationship Diagramming: Part I, II, III and IV

How do I Create an Application in Microsoft Access?

Important for success:
One source about how to name things -
What not to use in names -
About Auto Numbers

The evils of lookup fields -
Table and PK design tips -
About calculated table fields -

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.
Last edited:

Watch MrExcel Video

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back