Help with Spare Parts Database - I'm New! (1 Viewer)

sbrown914

Registered User.
Local time
Today, 05:00
Joined
Nov 13, 2019
Messages
14
Greetings!

I'm pretty new to all of this, and bumbling along...

I'm creating a database for our spare parts program. We sell complete systems, and once installed, spare parts to support the system.

I have created a Vendor Database as well as a Vendor Rep Database (there are often many sales folks for each company). I've started a Project Database (we do large jobs, so this is quite manageable), and am working on the Product Database.

My question is - Each Product may be used on several jobs. How do I create a relationship that provides the opportunity to pull up a product and see each project it was used on? I'd like this to be a query so that when I do a search for a product number it will pull up each project it was utilized on.

Thank you!

Sandi:D
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:00
Joined
Oct 29, 2018
Messages
21,357
Hi Sandi. Welcome to AWF!


We might need to see your table structure to understand your request better. Are you able to post a screenshot of your relationship window?
 

sbrown914

Registered User.
Local time
Today, 05:00
Joined
Nov 13, 2019
Messages
14
Here are my tiny little tables thus far:
Vendors:
VendorID
Address
City
State
Zip
Website
Phone
Fax
Notes

VendorRep
VendorRepID
Company (How do I put just a place holder?)
Last Name
first Name
Email
Mobile Phone

Products
VendorID
L&W Part Number
Description
Vendor Part Number
Cost
Sales Rep

I've connected the Sales Rep in the Product Database to the SalesRepID in VendorRep. My only other connection is the VendorID to Company

I need to add a project table, and connect each product to at least (and often multiple) projects - not sure what I make as a relationship.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:00
Joined
Oct 29, 2018
Messages
21,357
Hi. Can parts come from multiple vendors? For example PartA can come from Vendor1 or Vendor2? if so, you may need to split your product table into two (or three).
 

sbrown914

Registered User.
Local time
Today, 05:00
Joined
Nov 13, 2019
Messages
14
No - the part will only come from one vendor, but it could be used on multiple projects. I.E. Widgit purchased from CompanyWidgit could be used on 10 different jobs.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:00
Joined
Oct 29, 2018
Messages
21,357
Okay. So, which field is the primary key for the Products table?
 

plog

Banishment Pending
Local time
Today, 04:00
Joined
May 11, 2011
Messages
11,611
First and foremost: pick your terms, define them precisely and stick with them. Do not use synonyms. The nouns/objects of your description usually become tables in your database.

In your first post you used these key terms: System, parts, vendors, representatives, projects, products and jobs.

You should have a table for each of those--unless of course they are synonyms. We don't know enough about your organization to know if a project is the same as a job or product and parts are the same thing. This is why you need to use just one term per idea.

My advice is to open a spreadsheet, make a tab for each object and then list the attributes of that object (e.g. Product-> product name, product number, size; Vendor->vendor name, vendor address). Those attributes will then become the fields of that table.

Once you have that start making your database by building tables for each object including the correct fields each needs. Next, go to the Relationship Tool and complete that by setting up how all those tables relate. Finally, take a screenshot and post it back here and we can help you figure out if the structure and relationships are right.
 

sbrown914

Registered User.
Local time
Today, 05:00
Joined
Nov 13, 2019
Messages
14
Thank you! That sounds like a fabulous idea!!! I'll be back with the results!

I should've come here long before this! lol

(I'm also doing an online learning, so hopefully that will also help!)
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:00
Joined
Oct 29, 2018
Messages
21,357
Hi. Good luck. Let us know how it goes...
 

sbrown914

Registered User.
Local time
Today, 05:00
Joined
Nov 13, 2019
Messages
14
Here's my Excel Spreadsheet. Before I create the tables - does this look reasonable?
 

Attachments

  • SpareParts.xlsx
    10.4 KB · Views: 84

theDBguy

I’m here to help
Staff member
Local time
Today, 02:00
Joined
Oct 29, 2018
Messages
21,357
Here's my Excel Spreadsheet. Before I create the tables - does this look reasonable?
Hi. Here it is as an image for others' reference.





At first glance, the field CompanyName seems redundant. There should only be one of those, in the Vendors table (and maybe another in the Customers table, if they're not the same). Could Vendor companies be Customer companies as well? Also, why do you need a separate table for Reps (Vendors and Customers)? Are you expecting to have multiple Reps? If so, you should replace CompanyName with VendorID and CustomerID to represent their company affiliation. Even with that, I am wondering if two tables are required for Reps.
 

Attachments

  • tables.PNG
    tables.PNG
    20.4 KB · Views: 259

plog

Banishment Pending
Local time
Today, 04:00
Joined
May 11, 2011
Messages
11,611
Here's the issues I see:

CompanyName - I can't tell what it actually represents -is it the name of the vendor, or is it the name of the Customer? Remember my synonym comment? I would ditch CompanyName entirely and either user VendorName or CustomerName.

CompanyName - it shouldn't be in every table, it should be in just 1 actually. You are using it as a foriegn key (https://www.w3schools.com/sql/sql_foreignkey.asp) which links 2 tables together. The primery key of the table should be used as a foreign key in another table. You've identified a primary key in every table, those are the fields you should use to link to other tables.

VendorReps & CustomerRep - tables shouldn't have identical structures. Those 2 tables are essentially the same. In those cases, all the data gets put into just one table with a new field to determine if the Rep is a Vendor or a Customer.

L&WPartNo - don't use non-alphanumeric characters in table/field names. It just makes coding and querying more difficult in the future.

Overall, once CompanyName comes out of all those tables, I don't see how everything relates. Once you have all your pieces (tables) they have to fit together. I just don't see how they all fit together. Where do SpareParts go--what other table are they attached to? It seems like a Vendor since it has a VendorPartNo field, but you haven't included VendorID in SparePArts. Also, I just don't see how Vendors and Customers are going to tie together. Via Jobs perhaps?
 

sbrown914

Registered User.
Local time
Today, 05:00
Joined
Nov 13, 2019
Messages
14
Yep - I see what you mean.

So, maybe I'm trying to do too much. Basically what I need is a database that I can look up a part number, find the cost and who sells it. In thinking about your suggestions, perhaps I don't need a "Customer" table at all - just a Job table that can link: Job number to a part number to a vendor. Sounds easy... ;)

Back to the drawing board. I'm going to read more about fields so I can pare this down a bit, and see if I can make it make sense. Stay tuned...

You guys are SO helpful!

Sandi
 

sbrown914

Registered User.
Local time
Today, 05:00
Joined
Nov 13, 2019
Messages
14
Condensed it! The more I thought about what you said, the more I realized I was complicating things too much. (at least until I'm way smarter...).

So, in looking at these three tables, I'm wondering how to connect the spare part with the job it was used on? As I said, the same part could be appropriate for multiple jobs. I'd like to be able to query the part number and have it indicate which jobs it was utilized on.
 

Attachments

  • SpareParts.xlsx
    10.1 KB · Views: 89

sbrown914

Registered User.
Local time
Today, 05:00
Joined
Nov 13, 2019
Messages
14
Ok - Here's what I have for relationships! :eek:
 

Attachments

  • relationships.PNG
    relationships.PNG
    25.8 KB · Views: 91

theDBguy

I’m here to help
Staff member
Local time
Today, 02:00
Joined
Oct 29, 2018
Messages
21,357
Hi. I would probably do it more like this (incomplete).


 

Attachments

  • parts.png
    parts.png
    13.3 KB · Views: 236

sbrown914

Registered User.
Local time
Today, 05:00
Joined
Nov 13, 2019
Messages
14
Makes Sense! Let me see what I can do with it....

I'll get the hang of this.... maybe not this day... but soon!

Thank you.... I'll be back to let you know how it goes!
 

sbrown914

Registered User.
Local time
Today, 05:00
Joined
Nov 13, 2019
Messages
14
Next Steps for Database!

Good morning!

I've attached a copy of my relationships, and now I'm trying to figure out how to put it all together.

Do I now do a form? What I'd like to ultimately be able to do is the following:

Search for a particular part number to see who we purchase from.
Search for a vendor and see which parts they sell.
Search for a particular part number and see which jobs we use them on.

Note: The number of jobs is small so I could make that a lookup field and type in the jobs. The same part could be used on several jobs.

Help! lol

Thank you, everyone!

Sandi
 

Attachments

  • Relationships.PNG
    Relationships.PNG
    34.6 KB · Views: 94

theDBguy

I’m here to help
Staff member
Local time
Today, 02:00
Joined
Oct 29, 2018
Messages
21,357
Hi. I would recommend against the "lookup field" idea. In your relationship, I suggest enforcing referential integrity for JobID between jobs and job parts tables. In your field names, I would avoid using spaces and special characters like &, /, and #.
 

plog

Banishment Pending
Local time
Today, 04:00
Joined
May 11, 2011
Messages
11,611
I think your relationships look good. I too will caution you against non-alphanumeric characters in names. Only use letters, numbers and underscores; otherwise it just makes coding and querying more difficult down the line. So instead of [L&W Part #] I would use [LW_PartNum].

Do I now do a form? What I'd like to ultimately be able to do is the following:

Search for a particular part number to see who we purchase from.
Search for a vendor and see which parts they sell.
Search for a particular part number and see which jobs we use them on.

Forms are last. Next, populate your tables with test data. This will make sure you've included all the fields you need and they are they right data types. It will then let you move to the next portion of building a database--reports and the queries to support them.

Above, you've listed 3 things you need to do wtih your data. You should make queries and reports to achieve each of those. Don't worry about the search portion, just make queries/reports to list part numbers by vendor, vendor by parts and jobs by part number.

Doing that will help you test your tables to make sure you can get the data out like you need, understand what your relationships actually represent and the queries you build will be used in the forms you will make to actually do the searches you want to do.
 

Users who are viewing this thread

Top Bottom