Results 1 to 7 of 7
  1. #1
    akeller is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Nov 2018
    Posts
    38

    Quality Inspection Tracking DB Help

    Hello,

    Like everyone else, I'm extremely new to Access and am seeking opinions, input, and advice.

    I work for a manufacturing company and have been tasked with creating an inspection tracking database for my department.
    We inspect the produced product on CMM's (Coordinate Measuring Machines) and either move the Accepted product to the next work center, or create an NCR(Non-Conformance Report) and hold the product for further review.

    Our products have a Part Number (Item ID), a Work Order Number (Can have multiple parts per order but is rare), a Serial Number(non-serialized parts are usually grouped on one Work Order), and a Lot Number.

    The goal is to add Incoming Parts that are ready for inspection to the database, assign them a priority, and have them displayed on a Incoming Inspection Form. Once an Inspector is ready for a new job, he\she can view the Incoming form and "claim" a Job.
    That job will then be displayed on a "In Work" form that shows the date started and what inspector is working the parts. Upon completion, the inspector will either accept or reject the parts. If accepted, the information will show in a "Completed" form. If rejected, the inspector will create an NCR, input the NCR number and storage location into the DB which will then be displayed on the "NCR" form. After further review, if the NCR is "accepted", the product should be moved to the "Completed" Form.

    We need to be able to somehow "group" multiple parts into one "job" that the inspector has "claimed". I have been making some progress but am not sure of the best way to join the Part Numbers, Work Orders, and Serial Numbers.



    Attached is a photo of my table relationships. Any thoughts on how I can accomplish my goal?

    Thanks in advance.
    Attached Thumbnails Attached Thumbnails Relationship.JPG  

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    You may get some insight from this generic data model (tracking inspection equipment) from Barry Williams' site.
    Here is the requirement overview for that model.

    Here is a link to several Database Planning and Design articles ---***work through 1 or 2 of the exercises from RogersAccessLibrary mentioned in the link. Also, the "stump the model" info may be helpful in clarifying details.

    A few questions for you:
    Your department wants you to build this, but do other departments have the same need?
    What process(es) are applied to unaccepted parts? Is this within the scope of your task? That is, what can make an NCR item into an "accepted" part?

    It seems you have some inherent grouping within your business. For example you have Item/Part that can be part of a WorkOrder and is also a component of a Job, something identifies the Inspector's "claimed group", and there is the assignment of serial numbers to Parts within a Job or is it WorkOrder???

    Good luck.

  3. #3
    akeller is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Nov 2018
    Posts
    38
    Thank you for the links, I will definitely review them.

    We have one "main" department that has 2 smaller "sub-departments". I'm building the database for one of the "sub-departments" and if it does what it's suppose to do, the other "sub" and "main" departments may utilize it as well. All the processes will be exactly the same, just different types of parts.

    As for the grouping, the Work Order essentially is the "Job". A Work Order has a Part ID, and might be serialized. All Work Orders have a Part Number and Lot Number. Work Orders may have one or more parts as a quantity.

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Good stuff. Work through one of the tutorials from RogersAccessLibrary to get familiar with the process. Then using your own description, use the process you just learned to make a data model. Get some test data and some scenarios and test your model; adjust and repeat. This will make a blueprint for your design and which you can build in Access.

    Show us a screen print (jpg/png) of your revised "table layout/model".
    Good luck.

  5. #5
    akeller is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Nov 2018
    Posts
    38
    Attached is an updated pic of the tables and relationships. Am I still missing something?
    Click image for larger version. 

Name:	UpdatedCapture.PNG 
Views:	28 
Size:	74.5 KB 
ID:	36135

  6. #6
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    PMFJI,
    Just a couple of comments about the relationship window.


    Click image for larger version. 

Name:	Design1.png 
Views:	26 
Size:	32.6 KB 
ID:	36139
    Don't think this is a good idea - 2 PK fields linked to one FK field.......







    Click image for larger version. 

Name:	PK_Field.png 
Views:	26 
Size:	93.5 KB 
ID:	36141
    Not a good naming convention. You have EVERY PK field with the name of "ID".

    My naming convention is
    PK fields have the suffix "_PK" or "PK".
    FK fields have the suffix "_FK" or "FK".

    So
    "tblPriorities" would have a PK field named "PriorityID_PK"
    "tblJobDescriptions" would have a PK field named "JobDescriptionID_PK"
    "tblNetWorkCenter" would have a PK field named "NetWorkCenterID_PK"

    "tblIncoming" would have a PK field named "IncomingID_PK"
    with foreign key fields named
    "ItemID_FK"
    "WorkOrderID_FK
    "PriorityID_FK"
    "JobDescriptionID_FK"
    "TypeID_FK"
    etc. (you get the idea)

  7. #7
    akeller is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Nov 2018
    Posts
    38
    Thank you for the feedback! I'm updating right now.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 12-16-2016, 12:55 AM
  2. Inspection Date Intervals
    By tonycl69 in forum Queries
    Replies: 26
    Last Post: 09-22-2016, 09:25 AM
  3. Replies: 8
    Last Post: 04-21-2015, 12:05 PM
  4. Inspection report
    By FJM in forum Access
    Replies: 3
    Last Post: 09-24-2014, 05:50 AM
  5. Hydrant Inspection Database Help
    By Plan B in forum Database Design
    Replies: 12
    Last Post: 01-29-2013, 01:51 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums