Results 1 to 9 of 9

Designing a stage-wise production monitoring application

  1. #1
    RakB is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2019
    Posts
    4

    Designing a stage-wise production monitoring application

    Hello,



    I am trying to design a simple stage-wise production monitoring application using access (office 365). The idea is to track a product as it goes through different operations/processes. To make things simpler, lets consider four stages (Stage A, B, C and D) of production before the final part is ready.

    Lifecycle of product: raw-material -> A -> B -> C -> D -> Finished product

    I have created a table for stage A in access as follows:

    ID AutoNumber
    BarCode Short Text
    RecieveDate Date/Time
    Quantity Number
    Operator Name
    NextStep Dropdown created using lookup wizard - manual entry (Stage B, C and D as options)
    NextStepIssueDate Date/Time

    When stage A is complete, I want the product to be automatically populated in a table for stage B. The "NextStepIssueDate" field of stage A should become "RecieveDate" for stage B. Something like this:

    Stage A Column Maps To Stage B Column
    ID -> ID
    BarCode -> BarCode
    Quantity -> IncomingQuantity
    NextStepIssueDate -> RecieveDate

    Additionally, I want the table for stage B to contain additional fields such as "OKQuantity" and "Rejection" to store details specific to stage B. This again continues for stage C and stage D.

    How would one go about designing this? What is the best way to design a table for each stages? Using queries?

    I have experience with excel and VBA. I have some knowlwedge on relational databases, queries, primary/foriegn keys, but my usage of access is limited. Any pointers on how to get started will be appreciated!

    Thanks
    Rock

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,401
    can your steps (A, B, C, D) be used for a variety of products?
    can the steps occur in a different order (D, A, C, B) for different products?

    For instance let's say the step is 'Apply Glue'. Apply glue may be a appropriate to 50 different products, but if each step is very discrete i.e. 'Apply Glue to Part 0001234' this may limit the step to a single operation.

    If each step in each operation is discrete that's easier to handle than something where a step may be valid for multiple operations and in different sequencing.

  3. #3
    RakB is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2019
    Posts
    4
    Quote Originally Posted by rpeare View Post
    can your steps (A, B, C, D) be used for a variety of products?
    Yes. Different products can go through A,B,C and D stages.

    Quote Originally Posted by rpeare View Post
    can the steps occur in a different order (D, A, C, B) for different products?
    The order will always be A -> B -> C -> D. However some products may go from A to C or A to D. The backward movement will not be possible.

    Each step is going to be a discrete step.

    For my inital attempt at this, I came up with creating a table from query. For example, I can create a table for stage B using table for stage A (described in my post above). In the query wizard, I can set the criteria that NextStep = Stage B and NextStepIssueDate Is Not Null. However, I don'y know how to add information of stage B (such as OK quantity and rejection) to this table created from query. I am planning to use this table again for stage C as so on.

    Since each stage requires data from one or more previous stages (one or more since some products can skip a particular stage. For E.g. product 1: A -> B -> C Product 2: A -> C. The incoming table created for stage C should query tables for Stages A and B).

    Any suggestions for doing this? Do you recommend any other way of doing this?

  4. #4
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    297
    You may not want to store data AS tables - ie. Stage A table, Stage B table, etc.
    Use one table and store the data for each stage in it.

  5. #5
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,401
    I would try it this way:

    Code:
    tblStep
    S_ID  S_Name ---> other step specific information
    1     Step A
    2     Step B
    3     Step C
    4     Step D
    
    tblProduct
    P_ID  P_Name ----> other product specific information
    1     Product A
    2     Product B
    3     Product C
    
    tblProductStep
    PS_ID  P_ID  S_ID  PS_Qty ----> other product step specific information
    1      1     1     5
    2      1     3     10
    3      1     4     2
    4      2     1     4
    5      2     3     6 
    6      2     4     10
    7      3     1     2
    8      3     2     4
    9      3     3     6
    10    3     4     8
    This would be your basic product setup your 'generic' template for each product

    Code:
    tblJob
    J_ID  P_ID  J_StartTime  J_EndTime ----->  other job related fields  
    1     1     08:00         12:30
    
    
    tblJobStep
    JS_ID  J_ID  PS_ID  JS_StartTime  JS_EndTime  ---> other job step related information
    This would be your day to day recording of each job you complete.

    Then all you have to devise is a way to, when you create a new job, copy the relevant information from your 'setup' table to your 'job step' table, then your job is set up and your users just have to record the results each step.

  6. #6
    RakB is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2019
    Posts
    4
    Quote Originally Posted by rpeare View Post

    tblProductStep
    PS_ID P_ID S_ID PS_Qty ----> other product step specific information
    1 1 1 5
    2 1 3 10
    3 1 4 2
    4 2 1 4
    5 2 3 6
    6 2 4 10
    7 3 1 2
    8 3 2 4
    9 3 3 6
    10 3 4 8[/code]
    This wouldn't work because product has to go through stages sequentially. For example: Product 1 starts in stage A, 100 units produced, 100 units move to stage B and additional processing occurs. Out of this 95 good part come out and 5 are rejected. 95 good parts from stage B move to Stage C, again some processing is done and some rejections might occur. By the time product is finished in stage D, the yield may be 85. Note: Product 1 can move to stage B only after stage A is completed. I need to capture these details stage-wise and operator details. Also, like i mentioned earlier, products can skip certain stages. For example Product 2 goes from A -> B -> D and product 3: A -> D. The input quantity to a current stage depends on "good yield" from previous stage.

    Any suggestions?

  7. #7
    RakB is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2019
    Posts
    4
    Quote Originally Posted by moke123 View Post
    You may not want to store data AS tables - ie. Stage A table, Stage B table, etc.
    Use one table and store the data for each stage in it.
    Could you explain and give an example?

  8. #8
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,401
    Quote Originally Posted by RakB View Post
    This wouldn't work because product has to go through stages sequentially. For example: Product 1 starts in stage A, 100 units produced, 100 units move to stage B and additional processing occurs. Out of this 95 good part come out and 5 are rejected. 95 good parts from stage B move to Stage C, again some processing is done and some rejections might occur. By the time product is finished in stage D, the yield may be 85. Note: Product 1 can move to stage B only after stage A is completed. I need to capture these details stage-wise and operator details. Also, like i mentioned earlier, products can skip certain stages. For example Product 2 goes from A -> B -> D and product 3: A -> D. The input quantity to a current stage depends on "good yield" from previous stage.

    Any suggestions?
    I believe it would work. At each step all you'd have to do is record your scrap at each step, so if your initial goal for the product is to produce 100 and you have a materials list as well (someting not mentioned) you can calculate how much of each material you want. Then at each step along the way you subtract your scrap (and maybe categorize the scrap type as well) to get a final tally of how many pieces you ended up with. That's the whole point of having a record for each step in each product production you can record whatever you need in each step. and just because step A is not complete, does not mean you can't have steps B and D in your production table 'waiting' for stage A to be complete, nor does it mean you actually have to copy step B or D to your production table until stage A is complete.

  9. #9
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,003
    I think there may be more to your "business" than you have provided. It seems you are also dealing with "batches" or similar concept. And overall you are measuring "acceptable" and "scrap/rejected". It isn't clear to readers if there are details, relevant to your business and processes and requirements, missing in the description so far. Perhaps your analysis is not yet complete.
    Easier for development and testing if you model your processes and build a data model that supports your business. Vet the model to get a blueprint for the proposed database before jumping in to physical Access or other dbms software.

    How do you manage production and related info currently?

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

Similar Threads

  1. Putting Production Table Changes into Production
    By rjgriffin46 in forum Access
    Replies: 5
    Last Post: 11-13-2016, 08:49 AM
  2. Two-stage query?
    By AlanH in forum Queries
    Replies: 2
    Last Post: 02-15-2014, 10:06 PM
  3. Suggestion on Designing an Access Application
    By ravikiran in forum Access
    Replies: 0
    Last Post: 07-16-2012, 03:55 AM
  4. TempVars monitoring for debug
    By chris.williams in forum Programming
    Replies: 2
    Last Post: 11-18-2011, 03:27 PM
  5. Monitoring Form
    By chadk in forum Forms
    Replies: 0
    Last Post: 06-29-2010, 09:12 AM

Tags for this Thread

Posting Permissions

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