Trouble understanding the logic behind database structure

[connorwilliamsm]

I am new to Access, and I have been having trouble setting up my database. I am trying to make a database to track Job, order, and material information for a relatively small business. The general idea of what I want to be able to track is, "PM ordered (part #s) from (supplier) on (order date)." Then I want to be able to track when we received the parts and put them on our shelves, "(part #s) received on (date), checked in by (employee), placed in (part location)." And finally, to track when those parts are used, "(part#s) used/taken on (date) by (employee) for (job #)."

Here is a picture of my "relationships" diagram to show what I have so far:

I would have to make tables that include fields for a material check in and a material pull out right? I am just a little lost at this point. Thanks!

[June7]

IMHO, manufacturing (assembly) type of database is one of the more complicated to build. It has been discussed numerous times in forum.

PurchaseOrderItems is the material in data. Now to track parts used for a job work order. Do you really need to tie purchase orders to Jobs? Or just have a JobParts table that draws from parts inventory.

[connorwilliamsm]

IMHO, manufacturing (assembly) type of database is one of the more complicated to build. It has been discussed numerous times in forum.

PurchaseOrderItems is the material in data. Now to track parts used for a job work order. Do you really need to tie purchase orders to Jobs? Or just have a JobParts table that draws from parts inventory.

My original thought was to be able to run queries where I could request a list of jobs that used a certain part number. Also to be able to request a list of parts that have already been ordered for a specific job. I guess that is why I tied jobs to POs.. but like I said, I am new to Access and not really sure the correct way to go about it.

[Micron]

where I could request a list of jobs that used a certain part number

If tblParts is akin to inventory, it doesn't belong with tblJobs. Inventory is one entity, jobs are another. To query like this would require tblJobParts where JobID 456 shows up 10x if it requires 10 parts. You would index these fields as a pair so that the same JobId and part number cannot appear more than once - provided you are not trying to break out parts on a per task basis (e.g. qty 10 of 5874 required for one phase, but 20 more for another phase. In that case, the parts become related to the task, not the job. You can relate the PartID to the inventory table to see if the quantity in stock is sufficient, as well as query "backwards" through tblJobParts for a list of jobs related to a part id.

[orange]

As june said Inventory/stock control is not trivial.
Here is some info re database design and planning that may help with concepts.

You may get some ideas from this youtube video

Make a model and test it before getting too deep into physical database.
Good luck.